Microsoft Excel Basics Guide Ask Us

General Overview

Welcome to this Microsoft Excel Basics help guide*. This guide will provide information on:

• how to format and organize your data;
• how to calculate numbers and create basic formulas;

For information on how to create data visualizations, click here to access our Microsoft Excel Basic Visualizations Guide. If you would prefer to consume much of the material offered in these two guides via a video, you can click here to view my Excel Intro Video for Beginners.

Basic components of an Excel workbook

Excel is a spreadsheet program which means that it is effectively a grid composed of a series of columns and rows (see image below).

• Each column is named alphabetically (A, B, C ...).  An entire column (or columns) can be selected by pressing on the column header (where the column names are depicted - situated at the top of the spreadsheet)
• Each row is named numerically (1, 2, 3 ...). An entire row (or rows) can be selected by pressing on the row header (where the row names are depicted - situated at the left-hand side of the spreadsheet). 

At the intersection of each row and column lies a cell.

• Every cell has an alphanumeric name (example: cell A1 would be at the intersection of column A and row 1, so the top-left cell of the spreadsheet). 
• There is always one, and only one, active cell in a worksheet. In our image example, that cell is C5 which you can see as it is highlighted in green with a white background and appears in the active cell name area.

While there can only be one active cell, it is possible to highlight and modify a collection of cells (called a cell range).

• A cell range is defined by its upper value followed by a colon and then its lower value. In our example, we have a vertical cell range that begins at C5 (the active cell) and ends at C14. We can identify this range as C5:C14.
• Cell ranges can be vertical like the one we just went over, horizontal (example: C5:G5) or rectangular (example: C5:G14).

At the top of the worksheet, you will see a series of tabs (File, Home, Insert ...).

• Clicking on a tab will reveal its associated ribbon which includes the relevant command button grouped according to related function.
• For example, in the Home tab, the Paste command button is grouped together with the Cut command button, the Copy command button and the Format Painter command button. 

At the bottom of the worksheet, you will see a tab with the worksheet name. In the example above, the name of the worksheet is the default name Sheet1.

• You can add a worksheet by pressing the small "+" icon next to the worksheet tab.
• You can delete a worksheet by right clicking on it and choosing delete on the menu. 
• You can rename a worksheet by right clicking on it and choosing rename on the menu. 

For more information on modifying worksheets, please click here.

Viewing tips

Freeze pane

If you have a fair amount of data and want to lock a certain area of your screen while scrolling through the data, you can freeze your pane. To do so, place your active cell below and to the left of the area you want to freeze. For example, if you wanted to freeze the first two rows of the document, you would go to cell A3 and activate the freeze pane function by 

• Going to the View Tab
• Clicking on the Freeze Pane button in the Window grouping of the ribbon
• Press Freeze Pane option - you also have the preset options of freezing the top row or the first column
• You will now be able to scroll down your spreadsheet while keeping the first two rows at the top visible at all times.
• To unfreeze the pane, simply repeat the first two steps and then press the Unfreeze pane option.

For more information on freezing panes, feel free to click here. 

Zoom

There are many ways to zoom in and out in Excel. Perhaps the simplest for PC users would be to press the CTRL button while using the mouse scrolling wheel to scroll up (to zoom in) and down (to zoom out).

Alternatively, you can achieve a more precise zoom by going to the View tab and selecting the Zoom command button (magnifying glass icon).  From there, you can select between a variety of pre-selected zoom options, customize the zoom percentage or choose the "fit selection" option. This last option (which can be recreated by pressing the "Zoom to Selection" command button in the View tab), allows the user to select an area (a row, a column or a combination of both) and Excel will zoom in that exact area once the feature is activated. You can always go back to a standard view by clicking on the 100% command button in the View tab.

*Compatibility

It is important to know that Excel is not a static program and does come in many different flavours: Excel for PCs has various versions and while most of the functionality is retained from one version to the next, each iteration creates some changes to the platform. Excel also has a program for the Apple computer which has its own idiosyncrasies. Additionally, Excel has an online program which has has a truncated set of features compared to its PC counterpart. Finally, while not an Excel product, other spreadsheet programs (such as Google Sheets) do have many of the same features as the ones we will be discussing in this help guide. Please be aware of the program and version of Excel you are using. For your reference, we will be using Excel for Microsoft 365 (PC version) as our help guide version. 

Formatting Essentials

In this section, you will learn how to modify the look and feel of your Excel spreadsheets. As with many things in Excel, there are often many ways of accomplishing a task. For brevity, we will typically focus on one method we deem to be fairly obvious and easy to explain in text/visual form. 

Modifying cell appearance

Modifying cell appearance is relatively straightforward in Excel. Simply highlight the cells you wish to modify the go to the Home tab ribbon under the second grouping (Font).

Font type and size

• Change the font type (in our example, it is Calibri) by clicking the dropdown menu and selecting another font type.
• Change the font size (in our example, it is set to 9) by either clicking on the dropdown menu or by pressing the font size up or font size down command buttons situated right next to the font size dropdown menu.
• Add bold, italicize and/or underline to your font by clicking on the respective command buttons.
• For more information on changing font type and size, please click here. 

Borders

• Include cell borders (single line bottom border is the default) by clicking on the border command button.
• Click on the dropdown menu of the border command button to view more border options (top, sides, bottom as well as line weights).
• For more information on creating and deleting borders, please click here. 

Adding color

    Cells

• Click on the fill command button (paint can icon) to fill your cell(s) a certain color (the default is a bright yellow).
• Click on the dropdown arrow next to the fill command button to select a different color.
• For more information on background color, please click here. 

    Fonts

• Click on font color command button (bottom right in the font grouping) to change the text color (default is red)
• Click on the dropdown arrow next to the font color command button to select a different color.
• For more information on text color, please click here.

Conditional formatting

Conditional formatting allows the user to change the appearance of a predetermined cell (or cell range) based on a condition (or set of conditions).

If we look at the random dataset below, we can apply formatting to the data which is above 50.

• Highlight the desired data and click on the Conditional Formatting command button situated in the Home Tab in the Styles grouping.
• Click on the first item in the dropdown menu (Highlight Cells Rules)
• Click on the first item of that submenu (Greater Than ...). In this case, let's write 50 in the box so that any value above 50 will be highlighted.
• Let's keep the default formatting (Light Red Fill with Dark Red Text). The drop menu will allow you to choose from other preselected formats or to customize your own.
• After clicking OK, experiment 2 and 7 should be highlighted as they are both higher than 50 (as seen below).

There are many different variations of conditional formatting and we will explore a basic one here. For more information on conditional formatting, please click here.

Format painter

Format painter is a great tool when you want to use formats in one cell (or cell range) and carry them over to another cell (or cell range). To do so:

• Highlight the cell with the desired format
• Click on the Format Painter command button (paint brush icon) situated in the Home tab ribbon in the Clipboard grouping.
• Your mouse icon will now transform into a paintbrush icon. Use that paintbrush to highlight the cell (or cell range) that you would like to convert and that area will now have the same format as the source area.
• For more information on format painter, please click here.

Aligning data

Simple alignment

The centering function will likely be the most often used but you can align left, right as well as vertical alignment (top, bottom, center).  To do so:

• Highlight the cells you wish to modify
• Choose an alignment option in the Home tab ribbon under the third grouping (Alignment).
• For more information on this, please click here.

Merge and Center

A variation on the alignment theme is merging two or more cells together. To do so:

• Highlight the desired cells 
• Press the Merge & Center command button found in the Alignment grouping of the Home tab ribbon.
• For more information on aligning data, please click here. 

Modifying rows and columns

Row height and column width come in a predetermined size in Excel (15 points and 8.43 points respectively). That said, these sizes are adjustable and doing so is straightforward.

• Click on the row(s) or column(s) headers you wish to modify (this will highlight the entire row(s) or column(s)).
• Click on the Format command button in the Cells grouping of the Home tab ribbon.
• This will produce a dropdown menu where you can input the row or column size you wish or autofit (fits to the size of the data in your cells) the row(s) or column(s).
• For more information on row and column sizing, please click here.

Similarly, you can add or delete a row(s) or column(s) by:

• Clicking on their respective headers to highlight them.
• Click on the Insert or Delete command buttons in the Cells grouping of the Home tab ribbon
• Either pressing the Insert Sheet Column option or the Delete Sheet Column option.
• For more information on adding or deleting columns and rows, please click here.

Organizing Data

Modifying numbers

Modifying number formats is quite straightforward in Excel. Simply highlight the cells you wish to modify then go to the Home tab ribbon under the fourth grouping (Number). You can either:

• Click on the predetermined command buttons (Accounting, Percentage, Comma thousand separator) or
• Click on the dropdown menu which will provide a more expansive set of options.
• Note that when dealing with decimals, the command buttons at the bottom right allow to increase or decrease the number of visible decimal places simply by clicking on them. 
• For more information on modifying numbers, please click here.

Dates

Dates have been separated from the usual number formats as they can be a little less intuitive to work with. Below, you will see an example of a date (October 5th, 2021) in various formats within Excel.

• The first is the standard format.
• The next is a number - each date has a number associated with it. We will get back to this a little later.
• The next three are variations on the same theme: a short format (mm/dd/yyyy), a long format (ddd, mmm d, yyyy) and a custom format (mmm d, yyyy).
• You can learn more about date formats by clicking here.

Since each date is associated with a number, we can calculate the difference between two dates.

• As such, entering 10/5/2021 in cell F3, for example, and 10/7/2021 in cell F4 which would be the same as entering 44474 and 44476 respectively.
• It is therefore possible to go to an empty cell (say, cell F5) and calculate the difference between the two days by simply using the subtraction formula: =F4-F3 which would result in returning 2.

We will cover formulas more thoroughly in the following chapters but here is a formula associated with dates. by typing in =TODAY(), it will return today's date - or the date associated with your computer's current date. This formula will update every time you reopen or refresh your file. Since =TODAY() returns a date, and therefore a number, you can use it to calculate it with other dates. For example, if you wanted to know how many days were left for your class' deliverables, you could setup a spreadsheet like the one below. It would refresh (Days left column would decrease) every day.

For more information on how to use the TODAY() function, you can click here. Note that you can also use the function =NOW() which is similar to =TODAY() but also tracks the current time as well as the date. 

Reorganizing data

Sorting and filtering are two great ways to reorganize data. We will explore each briefly below.

Sorting data

Sorting data rearranges it in a predetermined way. After a sort, even though the data is reorganized, all of it is remains visible. To sort data, simply:

• Highlight the cell range you would like to sort.
• Click on the Sort & Filter command button in the Editing grouping of the Home tab ribbon. From there, you can chose to:
  • Sort the first column from lowest to highest (A to Z if alphabetical)
  • Sort the first column from highest to lowest (Z to A if alphabetical)
  • Custom sort which allows you to do various sorts including multi-level sorts including sorts that do not begin with the first column.
• For more information on sorting data, please click here.

Filtering data

Filtering data temporarily removes any data that does not meet the criteria provided. Even though some data is not visible, it can be restored to its original view by removing the filter option. To filter data, simply:

• Highlight the cell range or columns you would like to filter.
• Click on the Sort & Filter command button in the Editing grouping of the Home tab ribbon.
• Click on the Filter option in the dropdown menu. You will see a small dropdown menu at the top of each column you highlighted.
  • Uncheck any box whose data you wish to filter out.
  • Press the OK button
• Your data will now only include the rows associated with the filter constraints.
• To remove the filter, simply highlight the cells with the dropdown menu and click on the filter option of the Sort & Filter command button. 
• For more information on filtering data, please click here.

Calculation Concepts

Arithmetic Operators

Each cell has the capability of storing a formula or a value such as a static number or a string (typically static words). While we will go into formulas in greater depth in the following tabs, let's quickly take a look at how to perform basic mathematical operations in Excel. You will find below a partial Excel spreadsheet that we will use as an example.

If we wanted to add the scores of the first three experiments, we could do the following:

• Go to an empty cell (any unoccupied cell where you would like to see the result of your calculation).
• Enter the following =B3+B4+B5
• Press enter to the result of the operation (133). 
• You could also write = (and click on the cell B3) then click on the plus sign + and click on cell B4 and so on.
• By putting the active cell on the cell with the results, you will see the formula you wrote in the formula bar.

Similarly, you can use other arithmetic operators to perform mathematical calculations. Use the following operators to:

• the minus sign (-) to subtract cells or to create a negative number;
• the asterisk (*) to multiply cells;
• the forward slash (/) to divide cells;
• the caret sign (^) to use as an exponent. For example =B12^B5 in our example equate to 8 to the power of 7 (2,097,152).

Absolute and relative cell reference

So far, we have dealt exclusively with relative cell references. That is to say, if we were to copy and paste our formula to another cell, the formula references would change. For example, let's assume our addition function from earlier, =B3+B4+B5, was entered in cell F4 and results in 133. If we were to copy and paste that cell into cell F6 (2 positions down), the formula would move to 2 positions down (B5+B6+B7) and would result to 47. If we were to copy and paste the F4 cell again but this time into cell G5 (1 position down and one position to the right), the formula would move to 1 position down and one to the right (C4+C5+C6) and would result to 0. We can see these results by looking at the spreadsheet below:

If we wanted to lock in the cell references, we would need to put a dollar sign ($) in front of the cell name.

• Putting in a $ in front of the letter locks in the column information (example: $B3+$B4+$B5). Moving this 2 positions down would have the same effect as in the example above and would return $B5+$B6+$B7 for a value of 47. Moving the original formula 1 position down and 1 position to the right would return $B4+$B5+$B6 (as opposed to C4+C5+C6 in the example above) for a result of 119 (as opposed to 0 in the example above).

• Putting in a $ in front of the number locks in the row information (example: B$3+B$4+B$5). Moving this 2 positions down would have no effect on the formula and would continue to be B$3+B$4+B$5 for a value of 133 (as opposed to 47 in the original example). Moving the original formula 1 position down and 1 position to the right would return C$3+C$4+C$5  (as opposed to C4+C5+C6 in the example above) for a result of 0 like the example above but for different reasons.

• Putting in a $ in front of both locks in both column and row information (example: $B$3+$B$4+$B$5). Moving this 2 positions down would have no effect on the formula and would continue to be $B$3+$B$4+$B$5 for a value of 133 (as opposed to 47 in the original example). Moving the original formula 1 position down and 1 position to the right would have no effect and return $B$3+$B$4+$B$5 for a total of 133 (as opposed to C4+C5+C6 in the example above for a result of 0).

Instead of manually putting in the $ signs, you can also go into the formula bar, click on the cell references (example: B3) and then click the F4 button. The first F4 will do an absolute value (example: $B$3), pressing it again will do a row lock (example: B$3), pressing it again will do a column lock (example: $B$3) and pressing it again will revert back to a relative reference (example: B3). 

For more information on absolute and relative cell reference, feel free to click here. 

Copy and Paste Values

When you would like to transform your formulas into static values, there is a function that allows you to do this quickly. Simply highlight the cell (or cell range) you wish to transform, click the Copy command button in the Home tab ribbon and then click on the dropdown menu below to the Paste command button. Then click on the first item of the second grouping (Paste Values) to activate the function.

Your formulas will now be static data (numbers, strings, etc.) This is quite handy when you have quite a bit of formulas you want to transform into values quickly. For more information on a variety of pasting options, please click here. 

Basic Formulas

In this section, we will explore a few of Excel's basic formulas that will enable you to calculate numeric data very easily.

Formula structure

The formula in this section all follow the same structure:

1. As with all Excel formula, they all start with an equal sign "="
2. The equal sign is followed by the name of the formula. Example: SUM.  Please note that although the function name is in capital letters, formula text is case insensitive. Excel will automatically reformat the text in capital letters. 
3. The cells, range of cells or multiple cell ranges which are encapsulated by parentheses. Again, this entry is also case insensitive. 
   • Example of cells: (B3,B7,B10)
   • Example of a cell range (B3:B12)
   • Example of two cell ranges (B3:B12, D6:G8)

Formula examples

You will find below a partial Excel spreadsheet that we will use as an example. Notice that there is numeric data in cells B3, B4, B5, B6, B7, B8, B9, B10, B11 and B12. This is a vertical range that we can represent like this: (B3:B12). If we wanted to include the text in our range, it would look like this (A3:B12).

I will briefly explain the formula below as well as a quick example. For a more thorough explanation and accompanying examples of each formula, feel free to click on the formula name below. This will bring you directly to the Microsoft help page for that particular formula.

The SUM function calculates the addition of a range (or ranges) of cells. In our example, to calculate the sum (or total) of all the cells in the above range, we would find an empty cell and type in =SUM(B3:B12). That cell would then produce the answer (345). 

The COUNTA function calculates the number of non-blank values within a range (or ranges) of cells. In our example, to calculate the count of all the cells in the range below, we would find an empty cell and type in =COUNTA(B3:B12). That cell would then produce the answer (10). Note that typing =COUNTA(A3:A12) would return the same results (10) and that =COUNTA(A3:B12) would return 20.

The AVERAGE function calculates the average of a range (or ranges) of cells. In our example, to calculate the average of all the cells in the range below, we would find an empty cell and type in =AVERAGE(B3:B12). That cell would then produce the answer (34.5). 

The MAX function calculates the highest, or biggest, number in a range (or ranges) of cells. In our example, to calculate the maximal value of all the cells in the range below, we would find an empty cell and type in =MAX(B3:B12). That cell would then produce the answer (81). 

The MIN function calculates the lowest, or smallest, number in a range (or ranges) of cells. In our example, to calculate the minimal value of all the cells in the range below, we would find an empty cell and type in =MIN(B3:B12). That cell would then produce the answer (7). 

Practice

I have included a small practice spreadsheet for you to review these formulas. You can click here to download it.

The spreadsheet is composed of three tabs.

• The first tab has a table and a series of questions for you to solve;
• The second tab is a replica of the first tab but contains suggested formulas in case you get stuck on how to solve the questions;
• The third tab is a replica of the second tab with the answer key so that you can see one of the ways to answer the questions.