Formula in MS Excel
What is a Function?
Formulas in Excel are useful in performing various mathematical, statistical, and logical operations. You can type in a formula (though you have to be sure it’s exactly right) or you can use Excel’s preset formulas called functions.
If you type in the formula, you must start with an equal sign, so Excel knows that the data in the cell is a formula. After the =, what comes next depends on what you’re trying to do. If you are multiplying numbers, just type in the appropriate numbers and mathematical symbol (* for multiply).
Excel is also capable of performing more complicated calculations, including calculations for the cell itself, no matter what the content (for example, if the content of the cell changes from a 5 to a 8), and more complex formulas (such as averages, sums, etc., beyond basic math). Just like a basic formula, you need to start with the equal sign. After that, you would put the function name, then the range of cells inside parentheses, separated with a colon. For example: =SUM(B2:B5). Luckily, Excel has many preset functions, so you don’t have to remember exactly what to type in – just remember the name of the function.
Commonly Used Functions: AutoSum
Since adding is used so frequently in Excel, there is an icon on the toolbar that will attempt to do it automatically. When you use the AutoSum command, Excel guesses what data you want to sum together (usually a block of cells next to the cell where the formula is).
To use AutoSum, go to the cell where you want the summation result to appear, click on the AutoSum icon, and verify that Excel’s guess is correct. If it is not correct, select the cells you wish to add to the formula by clicking on the first cell and dragging to the last one. Then press Enter/Return on the keyboard.
The sum of the data of the selected range of cells will appear in the cell with the formula. If any of the values in this range of cells are changed, the cell with the summation formula will automatically update to reflect the new sum. The AutoSum icon is located both in the Home and Formulas ribbons.
Filtering Data
In addition to sorting, you may find that adding a filter allows you to better analyze your data. When data is filtered, only rows that meet the filter criteria will display and other rows will be hidden. With filtered data, you can then copy, format, print, etc., your data, without having to sort or move it first. To use a filter,
- Go to the Home ribbon, click the arrow below the Sort & Filtering icon in the Editing group and choose Filter.
OR
- Go to the Data ribbon, and then click Filter in the Sort & Filter group.
You will notice that all of your column headings now have an arrow next to the heading name. Click on the arrow next to the heading with which you want to filter, and you will see a list of all the unique values in that column. Check the box next to the criteria you wish to match and click OK. Click on the arrow next to another heading to further filter the data.
To clear the filter, choose one of these options:
- Click on the Filter icon next to the heading and choose Clear Filter from “Name of Heading”.
- Go to the Data ribbon and click the Clear icon in the Sort & Filter group.
- Go to the Home ribbon, click the arrow below the Sort & Filter icon in the Editing group and choose Clear.
Advanced Filter
In the Sort & Filter group of the Data ribbon, there is an Advanced icon, which evokes the Advanced Filter dialog box. This dialog box allows you to set a particular criteria, copy results to another location (other location must be in the same sheet), and capture unique values.
Sorting Data
For a quick sort, click the arrow below the Sort & Filtering icon in the Editing group of the Home ribbon and choose the Sort A to Z / Z to A icons in the Sort & Filter group of the Data ribbon. In Excel 2013, these are labeled Sort Smallest to Largest and vice versa.
For a more complex sort, go to the Home ribbon, click the arrow below the Sort & Filter icon in the Editing group and choose Custom Sort. This takes you to the same Sort dialog box you get with the Sort icon in the Sort & Filter group of the Data ribbon.
1. Under Column, choose the first column that you would like to sort. If you want to sort multiple columns, click the Add Level button.
2. Under Sort On, choose how you would like to sort. Note that Excel can sort by cell or font color in addition to values.
3. Under Order, choose A to Z (ascending), Z to A (descending), or Custom List.
4. Click OK to perform the sort.
View Tab
This bar provides different options for viewing the sheets in an Excel workbook. It also provides options for viewing different windows in Excel, for enlarging or reducing the size of the worksheet while it is being viewed, and what elements in the Excel window should be displayed. The commands for hiding and showing worksheets are on the Home Tab under (Format > Unhide).
The commands for hiding and showing workbooks (and windows) can be found on this tab.
Sheet View
Lets you create different views of the same worksheet when you are collaborating with others.
Only enabled in Excel online or on the Desktop when your workbook has been saved in OneDrive.
Options – Displays sheet view options.
Workbook Views
Normal – Displays the worksheet in the normal view. There is also a shortcut to this view in the bottom right corner of the status bar.
Page Break Preview – (Moved in 2013). Used for adjusting page breaks. There is also a shortcut to this view in the bottom right corner of the status bar.
Page Layout – Displays the worksheet as it will appear on a printed page.Displays a very useful horizontal and vertical ruler plus you can add/change headers and footers, check margins, row/column headings and scaling options. Not to be confused with Print Preview. There is also a shortcut to this view in the bottom right corner of the status bar.
Custom Views – Displays the “Custom Views” dialog box.
Full Screen – (Removed in 2013). Displays the workbook in full screen mode. Press Esc to return to the normal screen.
Show
Ruler – Toggles the display of the horizontal and vertical rulers. This is only available when you are in the Page Layout view. You can change the units displayed from the (Excel Options, Advanced tab)(Display, Ruler Units). By default the ruler displays the default units that are specified in the control panel, regional settings. These units can be either inches, centimeters or millimeters.
Gridlines – Toggles the display of gridlines on the active worksheet. Provides a shortcut to (Excel Options, Advanced tab)(Display options for this worksheet, show gridlines).
Formula Bar – Toggles the display of the formula bar. Provides a shortcut to (Excel Options)(Advanced tab, Show formula bar).
Headings – Toggles the display of the heading rows. Provides a shortcut to (Excel Options)(Advanced tab, Show row and column headers).
Zoom
Zoom – Displays the “Zoom” dialog box.
100% – Zoom the workbook to 100% of its normal size.
Zoom to Selection – Zoom the worksheet to display just the currently selected cells.
Window
New Window – Creates a new window of your current workbook.
Arrange All – Tile all open windows side by side on the screen.
Freeze Panes – Drop-Down. The drop-down contains the commands: Freeze Panes, Freeze Top Row and Freeze First Column.
Split – Splits the window into multiple resizable panes which allow you to have multiple views of the same workbook.
Hide – Hides the current workbook or window.
Unhide – Displays the “Unhide” dialog box allowing you to unhide a workbook or window.
View Side by Side – View two workbooks side by side to allow you to compare their contents.
Synchronous Scrolling – Synchronize the scrolling of two windows so they scroll together. The View Side by Side option must be switched on for this command to be enabled.
Reset Window Position – Reset the window position of the two windows being compared so that they share the screen equally. The View Side by Side option must be switched on for this command to be enabled.
Save Workspace – (Removed in 2013). Displays the “Save Workspace” dialog box. This allows you to save the layout of all the workbooks that are currently open so this layout can be reopened at a later date.
Switch Windows – Drop-Down. The drop-down contains a list of all the workbooks/windows that are currently open.
Macros
Macros – Button with Drop-Down. The button is a shortcut to View Macros which displays the “Macro” dialog box displaying all the available macros. The drop-down contains the commands: View Macros, Record Macro and Use Relative References.