Basic Excel Concepts

© Mike Splane 2012

Also see:

Cell Names

An Excel spreadsheet is similar to a tic-tac-toe grid, only much bigger. The squares in the worksheet are called cells. Every cell has a name. Select a cell with the mouse and you will see a letter highlighted at the top and a number highlighted on the left. The name of the cell is the combination of that letter and number. The name of the cell also appears in the Name Box, above column A.

Moving Around the Worksheet

Pressing Tab moves the cursor to the next cell on the right, Enter moves it to the next cell down, Shift + Tab moves it to the next cell on the left. You can also shift the cursor with the arrow keys and the Home, End, Page Up, and Page Down keys. Use the Ctrl key with the arrow keys to move the cursor the maximum distance. You can also move the cursor into a new cell by typing the cell address in the Name Box. Double-clicking on a cell wall moves you to the edge of a row or column of data.

Entering Characters, Dates, and Numeric Data 

Select a cell. Enter text or numbers by typing in the cell, or in the formula bar that is centered directly above the worksheet. Enter dates as mm/dd or mm-dd for the current year. Enter the date as either mm/dd/yyyy or mm-dd-yyyy for other years.

Editing Cell Contents

·        Select the cell and then click in the formula bar to edit its contents, or select the cell and type in a new entry, or select the cell; then press F2 to edit directly in the cell, or select the cell and double click to edit directly in the cell.

·        Click and drag to move the contents of a cell.

·        Use shortcut keys to undo or redo a step: Ctrl + Z for undo, Ctrl + Y for redo.


There are several ways to copy and paste. Use the mouse to highlight the contents in the cells that you want to copy from. Do not highlight the formula bar area!  That will delete the cell contents!

·        Press Ctrl + C. Highlight the area to copy into. Press Ctrl + V. Whatever you pasted is still on the clipboard, so you can paste multiple times with this method.

·        Or, click on the Copy icon. Highlight the area you want to copy into. Click on the Paste icon.

·        Or, click the Copy icon. Highlight the area you want to copy into and press Enter, or highlight the upper left corner of the area you want to copy into and press Enter. This will clear the clipboard

·        Move the cursor around until you get a small black cross in the lower right hand corner of the highlighted area.  Click and drag (down or across) to highlight the area you want the formula copied into. Release the mouse. 

Formatting Cells From the Home tab

·        The Font group contains icons for text options (Font, Size, Bold, Italics, and Underline) you’re probably familiar with from Word. It also contains an icon to add borders to cells. The “A” icons grow/shrink the font size.

·        The Alignment group contains text alignment options (Left, Center, Right, Indent, and Decrease Indent). It also has an icon to merge/unmerge cells. Other icons let you position text vertically in the cell, wrap text, or rotate text.

·        The Numbers group contains icons for formatting numbers as accounting style, percents, or comma style. Two icons increase or decrease the number of decimal places. A pull-down menu gives you other formatting options.

·        The Styles group provides options to apply special formatting.

Working With Columns and Rows

·        The Home tab, Cells group has two icons, insert and delete, used to manage formatting for columns and rows. Options include deleting, inserting, changing column width or cell height, and hiding/un-hiding columns.

·        You can find these same options by right-clicking on a column or row heading.

·        You can also use the mouse to change row heights and column widths. Just click and drag between the row or column indicators.

·        To resize a column’s width to match its widest content, double-click to the right of its column-letter indicator.

The Format Dialog Box

·        To quickly select large areas of the worksheet

·        mouse over large groups of cells,

·        or click on one or more column letters to select columns,

·        or click on one or more row numbers to select rows,

·        or click on the gray cell in the upper left hand corner, above the 1, to select all of the cells in the worksheet.

·        After you have selected cells to format, right-click and select format cells to open a dialog box. You can also access this dialog box by pressing Ctrl + 1

·        The numbers tab in the dialog box is used to change the numbering style.

·        The alignment tab lets you choose how letters are displayed within a cell.

·        Other tabs give you more options.

Copying Formatting

·        Select a cell with the desired format. This is called the source cell.

·        Click the Paintbrush icon on the Home tab. 

·        Drag the mouse across cells, row numbers (to select a row), or column letters (to select a column) to apply the format. The format will be copied into the selected cells. Try using the arrow keys to quickly apply the format to any cell next to your source cell.

Formatting Text and Numbers Practice Examples

Formatting Cells Practice Examples


The AutoFill Handle

·        You can use a built-in feature of Excel, the AutoFill handle, to enter a series of dates; to extend a series of numbers; to copy formulas; and to copy text.

·        Position the cursor in the lower right hand corner of a range. A small + appears. Drag in any direction.

Working with Formulas

A worksheet consists of a set of cells (boxes) aligned in rows and columns. Each cell can contain a string (letters or numbers mixed with letters), a number, or a date. Each cell is referred to by the letter of the column it appears in, followed by its row number. 


Column A

Column B

Column C

Column D

Row 1





Row 2

Text Message

March 18, 2002



·        In Column A Row 1 the content of cell A1 is a number with the value 3.

·                    In Column B Row 1 the content of cell B1 is a number with the value 5.

·                    In Column A Row 2 the content of cell A2 is a label “Text Message.”

·        In Column B Row 2 the content of cell B2 is a number, 37333. The number has been formatted to appear as a date. Why is it a number? So you can add and subtract dates or easily put them in calendar order. Excel assigns a number to each date by counting the number of days since January 1, 1900.

·        A cell can also contain a formula. A formula is a mathematical equation. Formulas always start with an = sign. The result of calculating the equation will appear in the cell. The actual formula appears in the formula bar area, above the worksheet, when the cell containing the formula is selected.

·        If you use a cell name (called a cell reference) in a formula, Excel uses the value found in that cell to calculate the result of the formula. For example, if you enter =A1 + 5 as a formula in cell C1 Excel will look into cell A1. It sees a 3 in that cell, so it converts the formula into the form = 3 + 5. The answer, 8, will be displayed in cell C1. The formula bar will display the formula, =A1+ 5.

Remember: formulas always start with = signs! When you enter these statements or formulas in cell C1, here is what will be displayed:

 These strings

in cell C1

Display this

 in cell C1


These formulas

in cell C1

Display this

in cell C1






B1 + 3

B1 + 3


=B1 + 3












Order of Operations (PEMDAS)

If a formula contains multiple calculations, Excel calculates the parts of the formula in this order: everything in parentheses, then exponents, then multiplication and division, and finally addition and subtraction, always moving from left to right. For example = 3+4*2 will display 11 because Excel would do the multiplication first, while = (3+4)*2 will display 14because Excel would do the addition inside the parentheses first.

Formulas Combining Strings of Text

To combine text strings use the ampersand (&) symbol in the formula. Strings must be enclosed in quotes. For example =”Splane”&”,”&”Mike” would display Splane,Mike. You don’t have to type a string, instead you can use a cell reference to a cell with text in it.  If cell A4 contained the name Splane, and cell B4 contained the name Mike, you could combine the names using the formula =A4&”,”&B4. Note that cell references are NOT in quotes.

Displaying Formulas

Select a cell and its formula will be displayed in the formula box located above the worksheet area. To view all of the cells as formulas, press Ctrl + ~ (tilde). The tilde key is in the upper left corner of the keyboard. This same keystroke combination returns you to the normal view. There is also a checkbox on the View tab where you can reset the worksheet to display formulas.

More Tools & Tips Practice Examples

Working with large groups of cells:

·        Rather than listing all of the cells in a large block of the worksheet you can use a shortcut method to describe any group of cells that form a rectangle.

·        The term range is used to refer to a rectangular block of cells.

·        Enter the cell address from the upper left-hand corner of the range, followed by a colon and the cell address from the lower right-hand corner. Excel will recognize this as a range and will include all of the cells inside its borders when it performs calculations.

·        You can also assign a word to describe a range. Start by mousing-over the cells in the range. Then type the word you want to use to describe the range into the name box and press enter key. The name box is right above column A in the worksheet.

·        If you use a range name in a formula, do not include quotation marks.


  • Example of ranges: Range 1 is: B3:C4, Range 2 is: E2:E4, Range 3 is: C6:E7




















Range 2




Range 1

Range 1 


Range 2




Range 1

Range 1


Range 2












Range 3

Range 3

Range 3





Range 3

Range 3

Range 3










Relative Cell References







= A1+B2+C3


= C1+D2+E3

Letters change when you copy a formula into a different column.






= A3+B4+C5


= C3+D4+E5

Numbers change when you copy a formula into a different row.

The formula in cell C4 was copied into cells E4, C6, and E6. Note how the letters and numbers change.

Cell references, when written like those in the table above, are called relative cell references. Cell addresses written in this format will change when the formula is copied into other cells. The row-indicator numbers change when the formula is copied into a different row. The column-indicator letters change when the formula is copied into a different column.


Absolute and mixed cell references

·        You can write cell references in a different form. Adding dollar signs to cell references tells Excel that you don’t want those cell references to change when the formula is copied. The part of the cell reference following each $ will not change.

·                    The dollar sign does not change the result of the formula. = A1+B2 and = $A$1 + $B$2 will display exactly the same thing.

·                    Cell references can have dollar signs in front of just the number, = C$3, or in front of just the letter, = $C3. These are called “mixed cell references.”  Cell references with dollar signs in front of the letter and number, = $C$3, are called “absolute cell references.”

·                    If you want to create a set of formulas that will always refer to either one specific row: A$1, or one specific column: $B2, use a mixed cell reference.

·                    For a formula with a fixed reference to a specific cell, use an absolute cell reference.

·        You can combine absolute, mixed, and relative cell references in a single formula. In the following table, the formula in cell C4 was copied into other cells. Observe which row and column numbers changed and note the effect of the dollar signs.







= A$1+$B2


= C$1+$B2

Notice that only the A and the 2 (in cell C4) change. Because of the $ symbol, the others don’t change.






= A$1+$B4


= C$1+$B4

·        One use for an absolute reference is to divide numbers in several cells by the same divisor, to find percentages. The formula can be written once, using an absolute cell reference to refer to the cell containing the divisor. Then it can be copied into other cells. For example = A4 / $B$7.


Working with Multiple Worksheets

Look at the bottom of your worksheet. You are given three worksheets to work in when you open a new Excel file. Click on a worksheet tab to move into a different worksheet.

By right-clicking on a worksheet tab, you can insert and delete worksheets, move and copy worksheets, rename a worksheet, select multiple worksheets to work on simultaneously, and add colors to the tabs.


Formulas Using Cells in Other Worksheets

To write formulas that refer to cells in other worksheets, add the worksheet name, followed by en exclamation mark, before the cell address. For example, =Sheet2!C4 will show the contents of cell C4 in Sheet 2. If a worksheet is renamed, Excel automatically corrects any cell references that referred to cells in the renamed worksheet.

Formulas Involving Arrays

You can write formulas using range notation instead of cell references. For example you might want to multiply two columns, multiplying the number in each row by its counterpart in the same row. Start by selecting the output area. Then type in the formula, using range notation. For example highlight cells D1 to D4 then type in this formula = A1:A4 * B1:B4. Press the key combination Ctrl + Shift + Enter to simultaneously enter the formula into all the cells in the selected range.

Formulas Practice Examples

Boolean Formulas

You can write formulas in Excel to test the value in a cell. If the value matches the Boolean formula’s terms, Excel displays “True.” If they don’t match, “False” is displayed.

Examples:     = A1 < 4,        = A1 = “Red”              = A1 < 10                   = A1 <> 5

These formulas will all evaluate as either True or False, depending on the contents of cell A1. Note that when you compare text strings, the string must be in quotes. Boolean formulas are used as a test condition in IF Functions, as described below.

Some common error messages: 

Error messages start with a pound sign (#).

·                    #####   If you see railroad tracks, your column is too narrow. Solution - widen the column.

·                    #DIV/O!  You are dividing by an empty cell or zero. Solution - fix the formula’s denominator to refer to a cell that contains a non-zero number.

·                    #REF! Your formula refers to a range or cell that no longer exists, due to a change in the worksheet.

·                    #NAME?  Your formula contains text that Excel doesn’t recognize. This could have many causes. Usually it’s a typo or missing punctuation in a formula.

·                    CIRCULAR  If a cell reference in a formula refers to the cell where the formula is entered, Excel will not be use the formula. For example, if =C4+5 was entered into cell C4. The “circular “error message will appear in the status bar.

Troubleshooting Practice Examples


Examples of commonly used formulas: There is no one right way to write a formula; getting the correct result is what matters. Spaces are not necessary in formulas, but were included in the examples to make them easier to read.


This table is used with the sample formulas below:






















Type of Equation:

Entered in Cell D3:

Result Displayed in D3:


Addition of Two Cells

= A2 + B3



Addition of a Constant

= B1 + 25



Addition of a Row of Cells

= SUM (A1:C1)



Addition of a Column of Cells

= SUM (B1:B3)



Addition of a Range of Cells

= SUM (B1:C3)



Addition of Scattered Cells

= SUM (A2,B1,C3)



Subtraction of a Constant

= C1 – 10



Subtraction of a Cell

= B2 – B1



Multiplication by a Constant

= A3 * 20



Multiplication of Two Cells

= B3 * C3



Multiplication by a %

= A1 * .40



Multiplication by a %

= B1 * 25%



Division by a Constant

= C1 / 5



Division by a Cell

= A2 / C2



Exponentiation (Squaring)

= B3 ^ 2



Exponentiation (Cubing)

= A3 ^ 3



Square Roots




Square Roots

= A1 ^ 0.5



Cube Roots

= B1 ^ (1/3)



Increasing by a Percentage (4%)

= A1 + (A1 * .04)



Increasing by a Percentage (4%)

= A1 * 1.04



Increasing by a Percentage (4%)

= A1 + (A1 * 4%)



Decreasing by a Percentage (8%)

= A1 - (A1 *.08)



Decreasing by a Percentage (8%)

= A1 *.92



Decreasing by a Percentage (8%)

= A1 – (A1 * 8%)



Calculate a Percentage (Part/Sum)

=A3 / $D$3

25% (Format as a %)


Average of a Column

= AVG (B1:B3)



Average of a Row

= AVG (A3:C3)



Average of a Range

= AVG (B1:C2)



Formula referring to a cell in another worksheet

= Sheet2!C4

The contents of cell C4 in Sheet2. 








Excel has a built-in set of advanced formulas, called functions. Usually a function needs to be given one or more pieces of data (each piece is called an argument) before it will work. The arguments are listed inside parentheses following the name of the function. Each argument is separated by a comma.

Here are some commonly used functions:

SUM                           Example = SUM(C3:G9)

A range is the required argument.

Result: Total of all numbers in the range.


AVERAGE                Example = AVERAGE(C3:G9)

                                    A range is the required argument.

Result: Average of all numbers in the range.


ROUND                     Example = ROUND(A4/3,2)

                                    A formula is the first argument.

The second argument is the number of decimal places to keep .

Result: if A4 = 5 then 5/3 = 1.6666 which is rounded to 1.6700.


TODAY()                   Example = TODAY()

                                    This function does not need an argument.                                

Result: The current date is displayed.


SUMPRODUCT       Example = SUMPRODUCT(A1:B3,C1:D3)

This function multiplies the contents of each individual cell in one range by its counterpart in another equally sized range and displays the total.

The result is a single number. 

SUMIF                        Example = SUMIF(B1:B3,”>5”,C1:C3) This function searches the range given in the first argument to find cells which match the criteria given in the second argument. The values from the corresponding cells in the range in the third argument are summed. In our example the result  = 104 (91 plus14)

·        The Sum Function

You can create Sum functions with the AutoSum icon (S) found on the Home tab. There are several different ways to create functions with the icon.

1.     Position the cursor in a cell where you want a total and click on the icon. Use the mouse to select the range of cells whose contents will be included in the total.

2.     Highlight a column of data and a blank cell underneath). Click the icon.

3.     Highlight a row of data (include a blank cell at the right). Click on the icon.

4.     Highlight a range, including a column of blank cells to the right and a row of blank cells below the range. Click on the icon to quickly total all the rows and columns.

5.     Position the cursor in a cell where you want a total. Click on the icon. Hold down Ctrl while selecting cells from different areas.

Functions Practice Examples


IF Functions

The IF function works with Boolean formulas. The words TRUE and FALSE that a Boolean displays are replaced with numbers, formulas, functions, or text strings. 

In Excel, an IF Function has three arguments.

The first argument is the Boolean formula that compares the contents of a cell to a value.

The second and third arguments give two options of what to display. If the Boolean formula evaluates as “True” the second argument is displayed. If the equation evaluates as “False” the third argument is shown.


C3 is > 80



If True




If False



Show “FAIL”

We would write the function like this: =IF(C3>80,”PASS”,”FAIL”) Commas separate each argument.

If the value in cell C3 is greater than 80, then the condition is met. The word PASS will appear. If the value in cell C3 is NOT greater than 80 then the condition is not met.  The word FAIL will appear.

The second and third arguments in IF Functions can be:

·        Text (called strings), always written between double quotes, or

  • Or Numbers, or
  • Or Formulas, or
  • Or Functions

You can even use an IF Function as an argument. This is called a nested IF.

Example: =IF(B9 = “Yes”, 21, IF(B10 = “Yes”, 22,23))

If Functions Practice Examples

VLOOKUP Functions

VLOOKUP functions are used to find information from a table. For example, you could have a table showing the sales tax rates for different counties. You can give Excel the name of a county and it will show the tax rate for that county.

A VLOOKUP Function has three arguments and looks like this:  =VLOOKUP(C4,G5:H7,4)

The first argument is always a cell reference to the input cell where the user types in what to look up. Excel will look in the table for a match to the contents of the input cell and extract data from that row.

The second argument tells Excel the location of the table. It can be expressed as range notation or as a range name. The range includes the data and the row labels but not the column labels. The labels must be in the first column of the table and must be in alphabetical order.

The third argument, which is always a number, is the location of the column in the table which will contain the requested information. The first column in the table is designated as 1, the second is designated as 2, etc. 

LOOKUP Functions Practice Examples

A common problem with Vlookup functions occurs when the entry in the input cell does not match anything in the table. Excel shows the closest match. To make sure the output data is always accurate you should prevent invalid entries into the input cell. Select the cell(s) where data entry should be restricted. On the Data tab, in the Data Tools group, click the Data Validation icon. On the Settings tab, under Validation Criteria, choose List in the Allow box. Then either type a list directly into the source box, using commas to separate each item in the list, or click on the icon in the Source Box. Then use the mouse to highlight the cells that contain your list. Press Enter and click OK.

Data Restriction Practice Examples


Installing the Analysis DataPak in Office 2007

The Analysis ToolPak contains some useful statistical analysis tools. It does not come pre-installed; you have to install it yourself. Here’s how:

Open Excel. Click on the Office button. At the bottom of the task pane click on Excel Options. A new task pane will open. On the left hand side, find Add-Ins and click on it. A dialog box will open.

Find the “manage” box at the bottom and select “Excel Add-ins.” In the Add-Ins Available list, select Analysis ToolPak and also Analysis ToolPak – VBA. Click OK.

Follow any instructions that appear on the screen. After the ToolPak installs, you will see a new tab on the ribbon, labeled Add-Ins.


Building Charts Using Excel

Building a chart is a process with several steps.

Select the cells containing information needed to build the chart. Include cells containing data and cells with text for making labels.

Select a chart type from the Insert tab.

Create the chart.

Reposition the chart in the datasheet or move it to a separate worksheet.

Use the three Charts Toolbars to customize the chart.

To select data before building the chart: Mouse over the cells that contain the information you need for the chart. If the cells are in separate areas of the worksheet, mouse over one area, then hold down the Ctrl key while you mouse over additional areas. If only one of the cells appears to be selected, that’s ok; Microsoft didn’t make the shading color dark enough to show up on a bright monitor. Release Ctrl.

To change the chart after it has been created.  Right-click in the chart background. Choose from the options.

To change the appearance of any object in the chart: Right-click on it. Choose the format option at the bottom of the menu. To add colorful background effects click on “Fill Effects.”

To work with individual data points: Click on the data series. All points are selected. Click again on the individual data point. The single point is selected. 

To edit the title:  You can replace text in titles by clicking on the text box, then entering new text, then pressing Enter. To add additional lines to a title, click twice in the text. Move the cursor to the end of the line and press Enter. Then type another line of text. When you finish click anywhere on the chart. 

To combine graphics with a chart: Use icons on the Insert tab to add graphic elements. Text boxes, arrows and lines, clip art, WordArt, and pictures can make a plain chart look spectacular.

Graphing an equation: Create a table in the worksheet. Enter a label at the top of the X values column. In the next column add a label to describe the equation. Enter numbers into the X Values column. In the top cell of the Equations column, next to the first X value, enter your equation as a formula or function. Copy the formula down so the formula is alongside each X value. Mouse over the equation column. On the Insert tab, click on the Line icon and select the first chart.

Now add labels to the X Axis of the chart. On the Chart Tools Design tab click on Select Data. In the Horizontal Category Axis Labels box click on Edit. Click on the cell selector icon and then mouse over the X values. Press Enter and OK twice to add the labels to the chart.

Chart Creation Practice Examples

Chart Formatting Practice Examples

Working with Large Sets of Data

Excel has database capabilities that are suitable for small and simple tasks. Microsoft Office includes a full database application called Access. Access can be used for tasks that require more complex data management capabilities.

A data table consists of one row of labels, called field names or headers, and several rows of data. The headers row is always the first row, above the rows of data. The rows of data should not contain any blank cells.

Tables should be separated from other areas in the worksheet by a blank row and a blank column. This helps Excel determine which cells are included in the database table. You can select any cell in the data table to work with the entire set of data; you do not have to select the entire table.

Sorting Data

You can use the A-Z or Z-A icons on the Data tab to sort data in a table. First click on any cell in a column that contains data. Then click on the A-Z or Z-A icon. Data in the selected column will be sorted. The other columns in the table will be rearranged so each row of data stays intact.

If your column contains numbers, the A-Z icon will put the numbers in order, starting with the smallest. The Z-A icon will put the numbers in order, starting with the largest.

You can use the Sort icon to simultaneously sort by more than one field. Click in the data. Click on the icon. Select a column to sort by and then select the order Click on Add Level to sort the data into subsets of the first selection. For example, you might want to sort by last name and then by first name.

Creating a Filtered List

Click on any cell in the data table. On the Data tab, select Filter. Pull-down arrows will appear next to the column headings. To hide some of the records, click on the pull-down arrow in a field. Every entry that has a checkmark next to it will be displayed. Remove the checkmarks for any records that you wish to hide.


Displaying Subtotals

You can do mathematical calculations on any columns that contain numbers. These calculations can be broken down to include subtotals for the results of similar records. Sort one of the columns that contain text, to group similar records. Click on any cell in the data table. On the Data tab select Subtotals. In the first dialog box select the column you just used to sort the data. Then select the type of calculation and the fields to work with.


Database Tools Practice Examples

Pivot Tables

Pivot tables give you tremendous flexibility in studying and manipulating large amounts of data. You can sort multiple fields of data using multiple criteria, and manipulate the data in several different ways. Pivot tables are particularly useful for analyzing marketing and manufacturing data involving multiple factors.

Create a Pivot Table Report

Click anywhere in the data table.

From the Insert tab, Tables group, click the Pivot Table icon to open the Create Pivot Table dialog box. Click OK.

The Pivot Table Field List should be open on the right side of the worksheet. To create the pivot table you choose fields and drag them from the top of the task pane into the boxes in the lower right corner. Fields containing text can be are used as filters, as row names, and as column names.  Fields containing numbers are used in the values area.

You can add more than one field to any section.

You can add multiple instances of a single value field, and then customize them to show different mathematical operations: count, average, sum, etc.

If the task pane disappears, click on the pivot table to reopen it.

Pivot tables Practice Examples



A macro is a recorded set of keystrokes and mouse clicks. When you create a macro, Excel records every mouse-click and keystroke you take as you perform the task. The steps are converted into a series of Visual Basic commands. After the macro is created you can run it, as often as you like, to repeat the steps.

To create a macro, go to the View tab and click on the pull-down arrow below the Macro icon. Select record macro. Fill in the name and description of your macro, and then start executing your planned series of keystrokes. If you make a mistake when you record the macro, corrections you make are also recorded. When you have completed the macro, go back to the macro icon pull-down box and select stop recording, or click on the stop macro icon in the status bar.

After you record a macro, you can review the Visual Basic code and make changes.

To play a macro, go to the View tab and click on the pull-down arrow below the Macro icon. Select play macro. An alternative method is to first insert a shape into your worksheet and then right-click and select assign macro. Pick a macro from the list. Add a label to the shape to you know which macro it runs. Click on the shape whenever you want to run that macro.