Class plans

1 Basics. Open a new spreadsheet. Navigation and data entry. Autocompletion. F2 shortcut for entering a cell in edit mode. Cell formats.

1. Excel is an application for working with tables of numbers called spreadsheets, named after the wide paper documents that were once used for accounting.
2. Excel calls its spreadsheet documents “Workbooks”.
3. To create a new spreadsheet in Excel, click “Blank workbook” on the backstage screen.
4. A spreadsheet is displayed as an infinite array of cells into which text, numbers, and mathematical formulae can be entered.
5. Select a cell and start typing to enter data.
6. To move to another cell, press one of the arrow keys.
7. You can also use TAB to move right one cell and SHIFT-TAB to move left one cell.
8. Press RETURN to move down a row.

   • If you are entering data into several adjacent columns, the selection will conveniently move back to the first column.
9. After you begin typing Excel will offer to autocomplete the cell contents for you based on data already present in other cells.
10. When you start to type on a cell any previous contents are cleared automatically.

    • This is great if you actually wanted to overwrite the cell with new data, however
    • it is really annoying if you wanted to add to existing data a cell, especially if the cell contained a lot of text.
    • If you accidentally start typing in a cell, you can press ESCAPE to get out of editing more and restore the cell to its previous contents.
11. To add to the existing text in a cell you can either double click on it, or (better still) avoid the mouse entirely and press F2.
12. Excel will guess what kind of data is in a cell by looking at its value.

    • If it looks like a number, it is a number; if it looks like text, it is text; if it looks like a date, it is a date.
13. Excel sometimes gets this wrong and makes annoying changes to the data you type in.
14. To tell Exccel what kind of data in in a cell, right click on the cell and choose “Format cells…” and specify what kind of data is stored there.
15. The default is “General” which means Excel will try to guess what is in the cell based on its contents.

2 Working with rows and columns.

Inserting and deleting rows and columns. Cut and paste. Autofill. Changing row/col formats. Auto-sizing cells to their content.

1. The labels above and to the left of the cells can be clicked to select an entire row or column.
2. Any operation you perform will then affect the entire row or column.
3. For example, to set the format of an entire row or column, first select it by clicking on its label and then right-click and choose “Format cells…”.
4. You can also copy a row or column and paste it elsewhere, replacing the original content of the row or column.
5. Insert will insert a blank row above, or column to the left of, the one you click on.
6. If you first “Copy” a row or column, then click on Insert, the copied data will be inserted into the table.
7. To move a row or column, first “Cut” it and then click on Insert, and the cut data will be removed from the table and then re-inserted at the position you chose.
8. To clear the contents of row or column click on “Clear Contents”; to completely remove it, click on “Delete”.
9. To select more than one row or column, hold down the SHIFT key to extend the selection or click on the first label and then drag the selection out.

   • “Clear” and “Delete” work as you would expect.
   • “Insert” also works, but will insert the same number of blank rows or columns as you have selected.
   • If you first cut or copied them, “Insert” will also transfer the data.
10. Clicking in the top-left corner, between the row and column labels, selects the entire sheet.
11. With everything selected the Home > Format… menu has several useful options for consistently setting the row or column size, or for auto-sizing them to fit the contents of the spreadsheet.

3 Autofill. Interpolate or extrapolate missing data.

1. Sometimes you want to fill in missing data, interpolating or extrapolating from partial data that is already available.
2. One example is extending a data series by providing the first few values and then having Excel extrapolate the rest.
3. Insert the first couple of numbers in a series into a row or column.
4. Then select the cells containing those numbers by dragging from the top-left cell to the bottom-right cell.
5. A small green square will appear in the bottom-right corner of the selection.
6. Click on it and then drag the outline to cover the entire range of cells you want to fill with extrapolated data.
7. Using the contents of the cells that you first selected, Excel will guess how to fill in the missing data.
8. A very useful feature is that this works with data other than numbers, for example, with dates.
9. First set the format of a column to “date”, then type in a couple of consecutive dates, or maybe even two dates separated by a week as I am doing here
10. Select the two dates, then drag the small green handle over a larger range of empty cells and the missing dates will be inserted into them, all separated by a week.

4 Working with tables of data.

1. Dragging over a square array of cells will select them all.
2. You can then peform operations on all the selected cells, such as changing their type.
3. You can also cut or copy the data and then paste it somewhere else in the worksheet.
4. When pasting you have several options for manipulating the data.
5. One useful option is to transpose it, which converts rows into columns and columns into rows.
6. To easily rearrange or sort data in an array of cells, you can turn a selection of cells into a table.
7. Select the cells of interest, then click “Insert > Table”.
8. Check the selected cells are correct, set or clear “My table has headers” as appropriate, then click “OK”.
9. The cable can now be sorted by any column.
10. To remove the table while leaving the data,

    • first use the “Design” tab “Table Styles” group to change the shading on the table to None,
    • then the “Tools” group to “Convert to Range”.

5 Formulas. Using autosum and friends. How cells are named. Naming arrays of cells. Absolute and relative references. When to use absolute or relative references. Some easy functions: sum, average, max, min, count.

1. The value stored in a cell can be calculated from one or more values stored elsewhere in the workbook.
2. Let's square a number stored in a cell, for example, 10.
3. To place the square in the adjacent cell, click on the empty cell and instead of a value enter a formula.
4. Formulas begin with an equals sign.
5. To refer to another cell, click on it, for example on the one containing the number 10.
6. Now we can enter an arithmetic operator, such as multiply.
7. To square the number, click on it again which will multiply it by itself.
8. Press RETURN to confirm the formula, and the cell now contains the square of 10.
9. Confirm that it is working by changing the 10 to another number such as 5, or 1.
10. To create a table of squares, extend the series starting at 1.
11. Then copy the cell with the formula, extend the selection over the empty cells below it, and paste the formula into all of them.
12. Let's look at the first formula.
13. Inside the two numbers being multiplied together are read from another cell, whose coordinates are specified as the values to multiply.
14. The coordinates work just like map coordinates, where there is a letter for the horizontal column and a number for the vertical row.
15. Both of our inputs come from A1, in other words, column A and row 1.
16. The cell below it refers to a different cell.
17. The row number has changed, but the column letter has stayed the same.
18. When you paste a formula, Excel automatically updates any cell references that are inside it so that they follow the formula as it moves around in the workbook.
19. These are called relative cell references, since they try to remain at a fixed relative distance from anywhere the formula might be pasted.
20. This is often convenient, but sometimes the exact opposite of what you want.
21. For example, instead of squaring each number, let's multiply each number by the same factor.
22. The factor will be stored in another cell.
23. The formula is entered as before, by clicking on the cell in the data column and the cell containing the factor.
24. Copy it, select the entire column, and paste the formula in.
25. This does not work, because Excel automatically updated the factor cell's address, pointing successive formulae at successive cells under the factor, which are all empty and assumed to be zero.
26. To fix this we can make the reference to the factor cell be absolute.
27. An absolute reference will not change when its formula is moved.
28. To make any part of a refernce absolte, just put a dollar sign in front of it.
29. In this case we really only need the row to be absolute, but there is no harm in making both the row and column absolute.
30. So we put a dollar sign in front of both the column letter and the row number.
31. Of course, we leave the reference to the data dell relative so that it goes down our column of numbers.
32. Copying and pasting the formula into the cells below it now creates the desired result.
33. Modifying the value in the factor cell updates all of the products simultaneously.

6 Formulas on data ranges. Averages, sums, counts.

1. Formula can contain functions that are invoked with a mathematical style notation.
2. Excel has many useful built-in functions, for example, we can sum the numbers in a column using the SUM function.
3. Select the cell that will receive the result and type and equals sign to begin a formula.
4. Type the name of the function, which is SUM, and then a left parenthesis to start the function parameters.
5. Drag over the cells that you want to sum, and then close the parenthesis in the formula.
6. Press RETURN to enter it into the cell.
7. Immediately the value in the cell is updated to reflect the sum of the other cells.
8. Changing any of the values in the other cells also causes the sum cell to be updated.
9. Next to the sum we can put a count of the data cells, using the COUNT function.
10. Just like sum, select the cell, enter the COUNT function, and for its parameter drag over the range of cells to count.
11. Press RETURN and the cell updates with the count of how many cells in the selection contain data, which is all of them.
12. If we clear any of the cells, the count reduces by one and the sum goes down by the value that we removed.
13. To find the average of the numbers in the cells, just divide the sum by the count.
14. Of course, this is such a useful operation that Excel has a built-in AVERAGE function that sums the cells and divides by their count, all in one.
15. If we look at the parameters in the formulae we see that a range of cells is represented by two cell references separated by a colon.
16. The first reference indicates the top-left of the range, and the second indicates the bottom-right of the range.
17. These references can be edited and parts of them can be made absolute, just like references to individual cells, whenever required.
18. There are many built-in functions in Excel.
19. To find the one you need, click on the little function symbol next to the formula field and a dialogue will pop open asking you what you want to do.
20. If you are lucky, typing a relevant keyword in the search field will show you some functions related to the mathematical function you need.

7 Conditional functions. Conditional formatting. Filtering data.

1. Many functions can be applied conditionally to their input data.
2. These functions usually end with the word “IF”.
3. For example, to count the numbers higher than 7 in our data you would use the function COUNTIF.
4. This function takes two parameters.
5. The first is the range of cells that are to be counted and the second is a string that specifies a condition that must be satisfied for the cell to be counted.
6. To count just the cells higher than 7, make the condition be “>7”.
7. Similar versions of SUM and AVERAGE exist, to filter just the cells of interest to us.
8. When the value of the cell is used in the calculation, as in the functions SUMIF and AVERAGEIF, an optional third parameter can be given.
9. If it is present then it must be a range of cells the same size as the first parameter.
10. The values used in the computation of the function will be taken from those in the third range whenever the corresponding cell in the first range satisfies the criterion given in the second parameter.
11. Functions also provide a way conditionally format a cell.
12. First select the range of cells that should be conditionally formatted.
13. Click on “Home” tab, “Styles” group, “Conditional Formatting” tool.
14. Select “New Rule…” then “Use a formula to detmerine which cells to format”.
15. In the box underneath, type a formula that applies to the first cell in the range of interest, for example, to select all cells whose value is odd.
16. Then click “Format…” to specify how to format the cell, for example to set the background to orange.
17. Then click “OK” and the formatting rule will be applied to the selected cells.
18. Updating the value in one of these cells will immediately apply the formatting rule based on the new value.
19. To clear the formatting rule, select the cells and click the “Conditional Formatting” tool, then “Clear Rules” and “Clear rules from selected cells”.

8 Data filtering. Advanced filtering.

1. Filtering means selecting certain data from a range of cells based on a criterion.
2. One way to do this is to conditionally copy cells from one place to another, remove any that are not of interest.
3. To do this, first add headings to your data columns if necessary.
4. Also, make sure there are no empty rows as this may confuse the filtering formula.
5. In an empty array of cells, create a table using the same column headings.
6. Under each heading type the criterion, such as a value or string that much match the cell's value, an arithmetic relationship that it must match.
7. In the “Home” tab, click on the Filter > Advanced tool and a dialogue will appear.
8. Choose Filter in-place or Copy to a new location as appropriate.
9. Select List range and drag a selection box over the data to be filtered, including the column headings.
10. Select Criteria range and drag a selection box over the criteria table, including the column headings.
11. If Copy to is available, select it and click on a cell where the top-left of the resulting data should be inserted.
12. Press “OK” and a new table will appear at the destination containing only those cells that match the criteria.
13. If you have more than one row of criteria then they will be combined with an “OR” operation.

9 Visualising data.

1. When a range of cells is selected a small button appears next to the selection.
2. It offers ways to visualise the data by adding conditional formatting, generating charts, inserting computed values including sums and averages, or applying tables to the data.
3. The first few options are kinds of conditional formatting.
4. One of the most useful formats adds a small bar behind each cell indicating the relative size of the value compared to the others.
5. Another is a “heat map” where lower valued cells are coloured green, intermediate ones yellow, and higher ones red.
6. The chart options let you plot the data in the cells on various different kinds of chart.
7. One of the simplest and most useful is a scatter chart, which plots X-Y points taking X values from one column and the Y values from the column next to it.
8. Despite its name, which implies a cloud of dots scattered around the chart, this is the one to use if you have data that needs to be connected with lines.
9. Let's make a column that contains the integers from 1 to 10.
10. Next to it we will make a column that contains the squares of those numbers.
11. To plot the resulting quadratic curve, select the entire table and then make a scatter plot from it.
12. The three icons that appear to the right of the chart let you control the visibility of optional items such as axes and labels,

    • or the style of the table,
    • which particular columns will be included, and even
    • to change the range of cells that are used to draw the chart.

10 Freezing parts of the display.

1. When working with large tables you might want to freeze row or column headings while working on parts of the table far away from them.
2. In the “View” tab, “Window” group, “Freeze Panes” tool, you can instantly freeze the top row or first column, where your headings usually live.
3. To unfreeze them, use the same tool which will have an “Unfreeze Panes” whenever any of them are frozen.
4. To freeze other parts of the worksheet, choose the first row that should scroll and click “Freeze Panes” and all the rows above the selection will now be frozen.
5. Or, choose the first column that should scroll and click “Freeze Panes” and all the columns to the left of the selection will now be frozen.
6. To freeze rows and columns, select the top-left cell in the region that you want to allow to scroll.

11 Importing and exporting data.

1. There are many ways to share data direcly between Excel and external programs such as databases; far too many to cover in this short tutorial.
2. There is one easy and universal way to do it indirectly, and that is using CSV or comma-seperated-values files.
3. A CSV file is a normal text file containing table data where each line of the file is a row of data.
4. Within each line of data, the columns are separated by commas – hence the name “CSV”.
5. To import data from a CSV file, in the “Data” tab, “Get & Transform Data” group, use the “From Text/CSV” tool.
6. Open the CSV file and a dialogue will appear previewing the data.
7. If necessary you can change the delimiter (which is the character used to separate columns).
8. Press “Load” and the data will appear as a table in a new worksheet.
9. To export data, do the exact reverse process.
10. Select the data you want to export, then create a new worksheet by pressing the “+” button at the bottom of the screen.
11. Paste the data into the new worksheet at the top-left, then from the “File” tab choose “Export”, “Change file type”, and select “Comma-separated values”.
12. Once saved, you can delete the empty workbook.
13. A slightly more convenient, but less standard, way to export and import data is using tab-separated data.
14. To export, select the cells of interest and copy them.
15. In a text editor program (such as Notepad) paste the copied data.
16. To import tab-separated data directly, open it in a text editor, copy it, and then paste it anywhere in your worksheet.
17. Another useful kind of export is to copy a chart from Excel into PowerPoint or Word.
18. Copying a chart as an image creates a pixelated result that does not look very good at all.
19. To copy a chart witout loss of quality, select the chart, press copy, and then in PowerPoint or Word use “paste special” (CONTROL-ALT-V).
20. A dialogue will appear.
21. Choose “Microsoft Graphics Object” to paste a copy that you can edit in the same way that you could in Excel to change the axes, labels, and so on.
22. If “Microsoft Graphics Object” is not available, choose “Picture (Enghanced Metafile)” instead; you won't be able to change the content, but the quality will be much better than a normal picture.

12 There is a lot more to discover.

1. Excel is a large and complex application that offers hundreds of other ways to manipulate and analyse data.
2. These short tutorials are meant to show you enough of the basics so that you will be able to find and understand help online when you need it.
3. Some important topics that we did not have time for include pivot tables, making drop-down lists for constrained data entry, locking cells and worksheets, and working with data across multiple worksheets.
4. Some of the most powerful formulas make use of indirect cell references, which means using the value stored in a cell as a reference to another cell.
5. When you need these features, you will know it.
6. And after these tutorials you will also know enough about Excel to understand how to use them based on reference material you find in a book or online.
7. Finally, never be afraid to explore: generate some interesting data, using formulae, and then manipute it either to understand the data or just to understand what Excel is capable of doing.

13 Extended example. Using Excel to simulate an electronic circuit.

1. Combining a resistor and a capacitor creates a filter.
2. Depending on the order of the components, the filter might be low-pass (rejecting high frequencies) or high-pass (rejecting low frequencies).
3. A low-pass filter puts the resistor in series with the input, and the capacitor in parallel with the output.
4. To understand the behaviour of the filter, we should look at its frequency response.
5. The frequency response relates the gain of the filter to the frequency of the signal passing through it.
6. Let's simulate a low-pass filter and plot its frequency response.
7. The frequency response is the ratio of output voltage to input voltage measured at a number of different frequencies.
8. The ratio of output to input voltage at a particular frequency is given by the gain function of the filter.
9. Two critical constants in the gain function are the time constant and cuto-off frequency of the circuit.
10. The time constant is easy to calculate: it is the product of the resistor value and the capacitor value.
11. In a blank worksheet, create two cells that will accept the values of the resistor (in Ohms) and the capacitor (in Farads).
12. In another cell, enter a formula that calculates the time constant of the filter.
13. Check that it is working by entering some values for the resistor and capacitor.
14. Let's use 1000 Ohms and 1 nano Farad, which is 0.000001 Farads.
15. The time constant should be 0.001.
16. The cut-off frequency is the reciprocal of two-pi times the time constant.
17. In another cell, enter a formula that calculates the cut-off frequency.
18. The value should be approximately 159.
19. To plot the frequency response, calculate the value of the gain function for a range of different frequencies and turn it into a chart.
20. Start with one cell containing a frequency of 1 Hz.
21. In the cell to the right, enter the formula for the gain function of the filter.
22. Be careful to use an absolute reference for the cut-off frequency, or it will change as you copy the formula to other cells.
23. The gain function of a low-pass filter is: the reciprocal of the square root of one plus the frequency squared divided by the time constant squared.
24. If the formula is entered correctly, the gain value should be 0.99998.
25. Note that for this kind of passive filter the gain will always be between 0 and 1, in other words, the filter always attenuates the signal.
26. Underneath add a cell with frequency 2, and copy the gain formula next to it.
27. The gain value should be .99992.
28. Do one more just to make sure.
29. Create a cell with frequency 5, and copy the gain formula next to it which should give a result of 0.99951.
30. Clearly the gain is not changing much, so… how high should the frequencies go?
31. A range of 100,000 to 1, or a maximum frequency of about 100 kHz, will give a good overview of the circuit's response.
32. We therefore have a lot of ground to cover on the frequency scale, so let's do it exponentially.
33. Instead of increasing the frequency linearly, approximately double it at each step – which, if you are a musician, is increasing it by approximately an octave at each step.
34. The frequency column begins 1, 2, 5, which is close enough to doubling at each step.
35. The next frequencies will logically be 10, 20, 50, and then 100, 200, 500.
36. Add them now, and continue adding frequencies to the column until you reach 100,000.
37. (It would be nice if Excel could figure out this series for us, but unfortunately I have not found a simple way to make that happen.)
38. Copy the gain formula, select the empty cells next to the frequencies, and paste it in.
39. The last couple of values should be 0.00318 and 0.00159.
40. If you cannot see enough decimal places, select the entire column and then “Format cells…” and change the format to “Number” with 5 decimal places.
41. Now plot the frequency response using a scatter plot.
42. It won't look like much at first, because our frequency scale should be logarithmic to compensate for the exponential series we used on the horizontal frequency axis.
43. To fix this, select the chart then right-click on one of the labels on the horizontal axis.
44. Chose “Format Axis…” and a new pane called “Format Axis” will appear at the right edge of the Excel window.
45. In the “Format Axis” pane, select the rightmost tab called “Axis Options”.
46. Near the bottom of the pane click the little button that enables “Logarithmic scale”.
47. You should now see a very nice curve showing a 50% gain at approximately 300 Hz, asymptotically approaching 1 below that frequency and 0 above it.
48. If you know your electronics (or your technical theory of music and audio) you will be familiar with the idea of a “first-order” filter which “rolls off” “linearly” at 3 decibels per octave.
49. Our plot certainly doesn't show that kind of behaviour, but only because decibels are an exponential quantity too and so the vertical axis should be made logarithmic.
50. Select the chart again, right-click one of the labels next to the vertical axis, and change it to “Logarithmic scale”.
51. You should now see the classic low-pass filter curve with a “knee” at about 100 Hz, and a beautiful linear roll-off of 3 decibels per octave above about 400 Hz.
52. To test your understanding of what just happened, repeat the simulation for a high-pass filter.
53. The formula for the gain of a high-pass filter is: frequency divided by cut-off frequency divided by the square root of one plus frequency squared divided by cut-off frequency squared.
54. Use the same values for R and C.
55. The correct technical term for the curves we plotted is the transfer function of the filters.
56. Finally, what do you think happens if you place the low-pass filter in series with the high-pass filter?
57. When two linear circuits like these are placed in series, the result is the product of their transfer functions.
58. Create another plot showing the frequency against the product of the gains of the two filters.
59. What do you think happens if you place them in parallel, which will add their transfer functions?
60. Try it and see; the result is maybe rather interesting, if you think about it carefully, although doing the division algebraically using the two gain functions will explain the result.

1. Can you enter data and copy/move data (including arrays of cells) in an Excel spreadsheet?
2. Can you insert and delete multiple rows/columns?
3. Can you move several rows or columns of data from one place to another in one operation?
4. Can you use autofill to generate a sequence of numbers?
5. Can you use autofill to generate a daily or weekly list of dates?
6. Can you convert cells to a table to perform sorting on each column?
7. Can you convert a table back into a range of normal cells?
8. Can you calculate the value in a cell using a formula based on one or more values in other cells?
9. Can you calculate the value in a cell using a formula based on combining many values from many cells?
10. Can you explain the difference between relative and absolute cell references?
11. Can you explain when to use an absolute reference and when to use a relative one?
12. Can you use conditional functions to exclude missing or irrelevant data from a calculation?
13. Can you use conditional formatting to change the visual appearance of a cell based on its value?
14. Can you filter data from a range of cells based on a complex set of criteria?
15. Can you very quickly add visualisation to a range of cells, such as colouring them by value or showing relative size bars behind their values?
16. Can you create a chart of data from a single series of values?
17. Can you create a chart of data from a sequence of X-Y value pairs?
18. Can you freeze parts of the display that contain headings or other permanent information?
19. Can you import and export data using CSV files?
20. Can you copy a chart from Excel to Word or PowerPoint without loss of visual quality?