Microsoft Excel is a Spreadsheet to manage data. It is a main part of Microsoft Office. It is the primary data system for small databases. Larger databases often use Microsoft Access or SQLServer. This document has the following parts:
- Introduction
- Cell Grid and References
- Ribbon
- File (Backstage View)
- Home
- Insert
- Tables (including Pivot Tables)
- Spark Lines
- Page Layout
- Formulas
- Data
- Review
- View
Introduction
This document is intended to help students with preparing for the Word certification exams. As I find specific areas where students struggle in order to pass the Microsoft Office Specialist Word Certification exam, or I see something that should be better explained, I will add it here. This document is divided into sections related to the Word Ribbon.
The Excel tool creates one Workbook which consists of one or more Worksheets. Each Worksheet is accessed by a tab at the bottom of the tool. A tool with multiple Worksheets will have multiple tabs, one for each Worksheet. Right clicking on each tab allows you to modify the appearance of each tab.
Cell Grid and References
The main part of each Excel Worksheet is the cell. Each Worksheet has multiple cells, each identified by their Column (letter) and Row (number). For example, Column C Row 6 would be identified as C6. Referencing a cell from another cell (even on a different Worksheet) is easily accomplished. In the desired cell, start a formula by either typing = or going to the Formula tab on the Ribbon and choosing the desired formula. Then in the cell or in the formula dialog box in the desired input area, click on that area and then click on the referenced cell. This will cause the column/row refence to be placed in the formula.
Cell references can be floating, fixed, or partially fixed. The dollar sign ($) indicates that a column and/or row is a fixed reference. Usually, clicking the cell refence and then clicking the F4 key at the top of the keyboard fixes the reference. Pressing F4 will toggle through whether the row or column or both are fixed or not fixed. The click sequence repeats.
| Click | Example | Meaning |
| 1 | $C$6 | Reference always points to the same cell. |
| 2 | C$6 | Reference points to cells in a constant row (row 6 here), the column is set to the same relative column as the referencing cell. For example, if C$6 was referenced from cell J11 then using the fill handle to drag the content of J11 into J12 the content of J12 would be C$6. Since I am not changing out of the J column, then the reference column C would also not be changed. If you drug J11 into K11, the content of K11 would be D$6. This is because we are moving the location one column to the right and thus the referenced cell would change from C$6 to D$6. |
| 3 | $C6 | Reference points to cells in a constant column (column C here), the row is set to the same relative row as the referencing cell. |
| 4 | C6 | Back to the original, the refence tracks the referencing cell location. |
Ribbon
Just like all other Microsoft Office tools, Excel has a Ribbon across its top. The ribbon is used to divide up the commands into tabs and command groups. The Tabs are listed in the Table of Contents listed above. The Ribbon appearance is:
Excel uses a Green theme. Across the top green bar is information and the Quick Access Tool Bar. It starts with a way to turn off or on AutoSave, a useful feature if you sometimes forget to save the file. Next is the Quick Access Toolbar. by default it has a quick save, undo, and redo. On the right is a down arrow that allows you to add additional items to the QAT. I usually add Print Preview and Mail. Next is the name of the File and its status (here Saved). You can Search the worksheets for particular items. Then comes the owner of the license for the tool and a flag for hiding the Ribbon. The standard three windows icons follow.
The main part of the Ribbon is under the title bar. I will describe each of the tabs on the ribbon in the appropriate section.
Insert
Besides the Home tab, the Insert tab is the most frequently used tab on Excel. Using formulas may be more difficult, but there are multiple enhancements for an Excel Spreadsheet that are accessed through the Insert tab. The two most commonly used are Tables and Charts/Graphs. The most useful, beyond tables and charts, are pivot tables and sparklines. I will cover these in the order that they appear on the Insert tab.
Tables (including Pivot Tables)
To be covered soon.
Spark Lines
Sparklines are a nice addition to Excel. They make data trends easy to summarize in a single cell. They take the selected data and express it in a one cell bar or line graph. They are very useful and easy to implement.
Formulas
Formulas are the key part of Excel. To be a good user of Excel, you need to know how to locate and use Formulas. There are two methods of using Formulas, either typing them into a cell or selecting the Formula from the Formula tab and filling in the entries in a dialog box for that Formula. I show both in the two following videos.
Basic Formulas
This is an introduction to some basic Excel formulas, especially the LOGIC and VLOOKUP formulas. This include “IF” and “AND” for logic.
Financial Formulas
Everyone has to buy something more expensive than our current funds will allow us to purchase. Excel has a way to start exploring the cost of purchases. It allows the user to specify items like Cost, Down Payment, Purchase Price, Years of Loan, and Interest Rate and then calculate the Monthly Payments. It also helps with create a Table to determine the best rate and length for a loan for your budget. Data Tables are quite helpful in helping to determine best options. To help make decisions, a brief introduction to Conditional Formatting is given.
String Manipulation
Excel allows extensive string manipulation, from combining contents of cells to taking portions of the content of cells and creating new information in new cells. Like all formulas, you can create a string manipulation formula in one cell and then copy it to other appropriate cells.
The Conditional Functions (The <command>if functions)
Excel can do simple functions with some condition attached. For example, Excel can go through the entries in one column and find matches to specific entry and do something with the cells in a second column in the same row. A few examples are in the following video.
VLOOKYUP and HLOOKUP
When you have a table and there are indexes either in the first column or top row, the best way to find information in the table related to an index is through the two lookup commands. Information can be extracted from any column in the table with the VLOOKUP (vertical lookup) command and from any row with the HLOOKUP (horizontal lookup) command. The lookup can either look for an index within a range of values or looking for a specific index, the command writer’s choice. The fields of the command are the same for both. I am using the VLOOKUP as the example. The fields of the VLOOKUP command are:
- VLOOKUP or HLOOKUP – name of the function
- loopup_value – value used for looking through indexes.
- table_array – table containing information, with first column or row containing indexes.
- xdol_index_num or row_index_num – slumn or row containing desired information.
- [range_lookup] – optional parameter, defaults to TRUE.
- TRUE – find the best fit answer, it is determined by any value of index above or equal to each line and less than the index value on the previous line.
- FALSE – find the exact match for the particular index in the table.