Conclusion

To end my 3rd Quarter blog, I've learned many lessons that can be beneficial in my use of Microsoft Excel. This proves that everything that I learned can and will be useful for future purposes. These may also make working in Microsoft Excel  more productive and more efficient.

Sources;

• Google Images
• www.excel-2010.com
• www.excel-easy.com
• moodle.pshs-brc.edu.ph

Third Quarter Lessons

1. Spreadsheets for Data Analysis
2. Functions and Formula
3. Cell Reference
4. Logical Operation (AND, OR and NOT)
5. IF
6. Conditional Formatting
7. Charts
8. Macros

Macros

Imagine you are to present your lesson to 10 different sections simultaneously. Hard, right? In these kinds of situations, you can make duplicates of your lesson and present it to the classes in order for you not to do the same thing over and over again if you want the shortcut. But if you want the long run, suit yourself. We can do these kinds of shortcuts in Microsoft Excel with the help of Macros.

Macros ( in real life )

One of the more powerful, but seldom used function in Microsoft Excel, Macros are shortcuts that can be used, are efficient and provide an ideal way of making repetitive tasks many times without writing a single line of code.

 

In order to do this, you must:

1. Open Developer options in the Ribbon
2. Click Record Macros
3. Name the macro and the shortcut key
4. Once the Macro is recording, do the things that you want the macro to record.
5. Press the Blue box located in the lower left corner of your workbook once you are done.
6. Press the assigned key for that macro to activate.
7. i

Data Representation using Charts

In MS Excel, you have a lot of tools for you to improve your work at your discretion. One of these tools is Graphs/Charts.

Column Chart

We have certain types of graphs to use. These are:

• Column Charts
• useful for showing data changes over a period of time

Pie Chart

• Pie Charts
• show the size of items on one data series, proportional to the sum of items

Line Chart

• Line Charts
• display continuous data overtime, set against a common scale, and are therefore, ideal for showing trends in data at equal intervals.

Bar Chart

• Bar Charts
• illustrate comparisons among individual items
• When to Use:
• the axis labels are long
• the values that are shown are durations.

And there are plenty more charts such as:

•  Stock Chart
• Area chart
• Doughnut Chart
• Bubble Chart
• Radar Chart
• Surface Chart and
• Scatter Chart

Conditional Formatting

From the lesson about IF, we can know more about making conditions in a more advanced manner through Conditional Formatting.

Conditional Formatting

Conditional Formatting is applying unique cell formatting based on a certain condition. In Conditional Formatting you can apply the following

• Highlight Rules
• Top-Bottom Rules
• Data Bars XD
• Color Scales
• Icon Sets, and of course
• Customizing your own rule.

PS: When adding a new rule, add a formula that contains a condition and add new formatting.
Ex: =A1="PASSED"

IF

In the previous blog, we talked about the AND, OR, and NOT Functions. Now we will move on to IF.

The IF Function returns one value of a specified condition evaluates to TRUE, or another value if it evaluates to FALSE

An example is =IF(A1>10,"Larger","Smaller")
The example basically means that if the value of the cell A1 is greater than 10, then it will give the answer "larger". Otherwise, it will give "Smaller"

Always remember that the use of IF pertains to Making Conditions.

AND ,OR and NOT

In Microsoft Excel, there are certain things that can improve the sense of formulas. Such are AND, OR and NOT. All these functions follow the Boolean

Table

The AND Function, ( pronounced as And not as ) returns True if all conditions are true and will return False if any of the conditions is false.

An example is =AND(A1>A10,A1<A40).

The OR Function, returns True if all conditions are true. Unlike the AND Function, the OR Function will return true if any of the conditions are True.

The NOT function, I don't know.

Cell Reference

In the next topic, we will learn some cool tips in Cell Referencing.

Basically, Cell Referencing is getting information from other sources which include using range, columns and rows. Here are somethings that can help

Cell Reference

• Autofill
• Paste Special, and
• Ctrl + `

There are different types of reference. 

• Relative
• Automatically changes when the formula is copied down down a column or across a row.
• Absolute
• cell reference is fixed; doesn't change when copied to other cells.
• Uses $ like: $A$1
• Mixed
• A combination of Relative and Absolute.
• Example:
• A$1
• $A1

Cell reference can also be from other sheets, and even from other workbooks.

These are the formulas.:

• Same File
• Manual Selection
• Use Formula
• Sheet2!A1
• Different File
• Manual selection
• use formula
• [File2.xlsx]Sheet1!$A$1

Functions and Formulas

As said in the previous blog, data can be entered in text, numbers, and even formulas. Now, we are going to tackle on Functions and Formulas

Pythagorean Theorem

A formula is an expression  used to perform calculations,begins with an equal sign, composed of operands
and operators, and may contain constants, cell references, ranges and grouping symbols.

An example of a Formula is:

• 150*0.5

A function, on the other hand, is a predefined formula, may require arguments as inputs, returns a value, and may be used together with other functions and other operations.

An example is:

• AVERAGE(A1:A10)

Data Types

There are many kinds of data that can be inputted in  a cell. They can be numbers, text, and many more data representations.

A Data Type is a standardized representation for any particular kind of data. It also indicates the potential range of values we can store and the operations we can form with them.

http://i.msdn.microsoft.com/

Data types include

• Currency
• DateTime
• Logical/Boolean
• Number, and of course
• Text

Spreadsheets for Data Analysis

To begin the 3rd Quarter, we tackle first Spreadsheets for Data Analysis.

Basically, an excel file is called a Workbook while an excel sheet is called Worksheet. A worksheet is the page where you work on which is made up of grid Cells. Cells are where you input data or formulae into. They are arranged by row and column.

http://www.etektraining.com/

In Microsoft Excel, the top part of your workbook is called the Ribbon. The Ribbon holds different task bars for you to use in working in your excel file such as the name bar, formula bar and other options.







There are some keys that may hell in making your working in excel easier. Here are some.

• Alt + Enter
• check "Advanced Options"           
• Ctrl + ;  or Ctrl + Shift + ;
• Ctrl + Enter