Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
An introduction to using Microsoft Excel, focusing on inserting and deleting rows, columns and cells, formatting cells, and performing calculations using formulas. It covers relative and absolute cell references, copying formulas, and using functions such as SUM, MIN, COUNT, ROUNDUP, and VLOOKUP.
Typology: Lecture notes
1 / 27
This page cannot be seen from the preview
Don't miss anything!
CPA Info# 388 September 2020
Designed for use in Advanced Got Farm Records… Now What? Workshop This workshop is made possible, in part, through a Southern Risk Management Grant supported by USDA/NIFA under Award Number 2018- 70027-28585.
Lamar Smith Consultant Hal Pepper
Active Cell: The active cell has a box around it. There are several ways to move between cells.
Mouse: Click on the cell desired.
Arrow Keys: Scroll to the cell desired.
Scroll lock on – window pans Scroll lock off – active cell changes
The box will move as the arrow keys, the return key or a mouse click activates another cell.
Some important terms:
Worksheet or Spreadsheet: A single grid of cells. The name of the worksheet or spreadsheet is the name on the tab at the bottom.
Workbook: A series of worksheets or spreadsheets in one computer file. The name of the workbook is the name of the file.
Formula Bar: Located across the top of the spreadsheet. It is a place to enter the formula to be calculated.
Exercise 1 - Formulas
Open the file: Excel Class Tenn.xlsx. Click on the Formulas worksheet tab. Starting in cell A1, enter a number followed by the Enter key. Depending on how Excel is configured, the active cell will now be A2, B1, or still on A1. Just enter some numbers into Excel to get a feel for how numbers are accepted. You can use the Enter key, Tab key or use a Cursor (Arrow) key to input the value.
Deleting: The contents of a cell can be deleted by selecting the cell and touching the delete key. This action empties the contents of the cell. It does not remove the cell from the spreadsheet or change the appearance in terms of font, color, etc.
Exercise 2 - Formulas
Make sure you are working in the Formulas worksheet tab. Highlight a cell with a value. Press the delete key. The value stored is removed.
Undo: Any action can be undone by using the “Undo” feature or Control (CTRL)+Z.
Exercise 3 - Formulas
Make sure you are working in the Formulas worksheet tab. Click the Undo icon at the top or use (CTRL)+Z. The information deleted in exercise 2 is restored.
Adjusting Column and Row Size: At the top of each column is a column label. It is gray with the letter designator of the column in it. At the left of each row is a row label. It is gray with the number designator in it.
The size of the column or row can be adjusted to provide the necessary space for the value by hovering the mouse pointer over the line which divides the column or row in the label area. The pointer will change to a bar with arrows pointing out from the bar. The bar is vertical for the columns and horizontal for the rows.
Vertical bar shown above for a column.
Once the pointer changes, left click and hold to drag the row or column to the size desired.
Clearing Whole Rows or Columns
A whole row or column can be highlighted by clicking on the label area of the row or column desired. Once highlighted, press the delete key to remove the information. This process only clears the values. It does not remove the row or column from the spreadsheet.
In cell C2, enter “=A2-B2” followed by the Enter key. In cell C3, enter “=A3*B3” followed by the Enter key. (Select cells using arrows.) In cell C4, enter “=A4/B4” followed by the Enter key. (Select cells using mouse.) Notice the results in column C. Change the values in columns A and B and notice how the results update.
Order of Operation: You should also be aware that the order of operation holds true in Excel. Remember the rule we learned in school that to evaluate a mathematical expression, first multiply, then divide, next add and finally subtract.
Use parentheses to group math operations that should be performed first.
Exercise 7 – Operation Order
Make sure you are working in the Operation Order worksheet tab. The following information has been entered in the spreadsheet under the worksheet tab “Operation Order.” A2 = 3 ; B2 = 7 ; C2 = 20 A2 represents the number of people in one group. B2 represents the number of people in a second group. C2 represents the fee to be collected. The mathematical expression =A2+B2C2, or 3+720 will produce a result of 143 because multiply and divide occur first in the order of operation. First 7 x 20 = 140, then add 3 = 143. But, if we want to first add the number of people in the two groups and then multiply the result by the fee, we can place parentheses around the expression A2+B2. The formula would be expressed in cell D2 like this: =(A2+B2)*C This says first add 3+7 for a sum of 10, then multiply by 20 for a total of
Relative Cell Reference: Instead of having to enter each individual formula into each individual cell, Excel copies formulas into cells semi-intelligently. When the formula is copied down the spreadsheet, the formula is changed by Excel to reference the correct set of cells relative to the position of the new formula location.
Exercise 8 – Formulas
Make sure you are working in the Formulas worksheet tab. In the Formulas worksheet tab, highlight column C only and press delete. For each row (2, 3, 4, etc.), I want each cell in column C to be the result of adding the value in column A to column B. I could manually enter the formula over and over in each cell of column C, changing the row reference for each row, but there is an easier way. Make cell C2 the active cell. In cell C2 enter the formula =A2+B2. Press Enter and move the active cell back to cell C2. (You’ll notice the active cell has a box around it as we discussed earlier.) In the lower, right hand corner of the box is a green square. This is a handle. Notice how your cursor will change from a large white cross to a small black cross when you touch the handle. So, hover the cursor over the handle so that the cursor is a small black cross, click and hold the left mouse button, then drag the box down to cover cells C2 through C4. Release to see the new answers in column C.
Relative Cell Reference gives us the ability to copy formulas down or across the spreadsheet and have the formula automatically updated for the cell reference.
If you copy down, the row numbers are indexed. If you copy across, the columns are indexed. But you can’t do both at the same time. That would just be too confusing for Excel.
A word of caution. You can make a mistake right here if you’re not careful. So, if you attempt to drag the formula in our example from C1 to D1, your formula will not mean much because it would become a formula that takes the answer and adds to it again one of the numbers used to produce the answer. There are times when relative cell reference will not work for you and there is another way to handle those situations. We’ll come to that in the next exercise. First we need to talk about functions.
Sum Function: The makers of Excel knew that we would be doing a lot of totaling long lists of numbers. If we had a list of 6000 values and wanted to total the whole list, that would be quite a long formula (=A1+A2+A3+A4… all the way to A6000). We have a way to short cut that process. One way of making this statement would be to add up all the numbers from A1 to A6000. The “SUM” function does exactly that.
Look for an icon on the tool bar that looks like the Greek symbol “Sigma”. It looks like a letter M laying on it’s left side. Like this: “ Σ “ This is the Sum button.
Make sure you are working in the Functions worksheet tab. I want to find out the annual percentage of total acres in vegetables produced over a four-year time period by year. In other words, over the time period, what was the percentage of total acres for each year? In cell B17 enter =B15/F15. This divides the annual total by the grand total for a percentage. Make cell B17 active and drag copy across to cell E17 by using the handle of the active box. The error “#DIV/0!” means you are trying to divide by 0. You’ll remember from our school math that no number can be divided by 0. It’s impossible. To correct this problem, we need to change part of our formula to use “absolute cell reference”. In a formula, when a row or column is preceded by a dollar sign ($), then the row or column value will not change as you copy it from cell to cell. So go back to cell B17. Press the F2 key to allow you to edit the formula or double click on the cell. Now move the cursor to just before the F and place a dollar sign ($) there. Then, move to just after the F and put another dollar sign ($) there. This locks the cell reference. Copy the formula by dragging across to cell E17 and this time the proper answers appear.
Copy and Paste: Now that you understand about “relative cell reference” and “absolute cell reference”, you can now see how cut and paste can work for you when copying information. Copy and paste (just like in Word) will copy values or formulas according to the rules of relative and absolute cell reference.
Exercise 11 --Formulas
Make sure you are working in the Formulas worksheet tab. On the Formulas worksheet, highlight cells A2 and B2. Once highlighted, the cells can be copied as follows: Use Edit menu, then Copy, (or) (CTRL)+C, (or) Right clicking on the highlighted cells and selecting “Copy” with a left click. Move the active cell to A9 and paste the cells as follows: Use Edit menu, then Paste (or)
(CTRL)+V (or) Right click on cell A9, then left click on Paste. Move the active cell to C2 and copy as described above. Move the active cell to C9 and paste as describe above. The formula was copied and pasted, and the formula cell address was updated to continue to add the columns as you structured them.
Paste Special: There are occasions that you do not want a formula to be pasted. For example, you may want the result pasted instead of the formula. In this case, you may want to use “Paste Special.”
Exercise 12 --Formulas
Make sure you are working in the Formulas worksheet tab. On the Formulas worksheet, make cell C3 the active cell and copy the cell (CTRL)+C. Move the active cell to cell F15 (any unrelated cell will do). To use Paste Special, use 1 of the 2 following methods:
Paste Special is handy when you don’t want the user of the spreadsheet to be able to adjust the displayed values in a cell. For example, you may not want a particular cell to be linked to other cells with formulas. With Paste Special the results of a formula are pasted to a cell instead of the formula itself.
To save the workbook select File, then Save. If the workbook has never been saved before, Excel will require a file name. If it has been saved previously, the old copy will be over written.
The icon on the toolbar (the one that looks like a disk) serves the same purpose.
There are occasions when you might like to take an existing workbook and save a new copy with a different name. In this case, select File, then Save As. You will be prompted for a new file name before the saving will be executed. There is not a tool bar icon for this feature.
This is a destructive delete. It is removing the cell from the spreadsheet and shifting the cells around it to take its place. The question Excel wants answered is “Do you want to shift the cells up or over from the side?” Click on cell C2, then right click while pointing to cell C2. Select “Insert”. This process can be reversed and cells inserted. Excel will again ask which way to shift the cells. Select a row or a column for insertion or deletion by selecting the whole row or column, right click for the short cut menu, then choose “Insert” or “Delete.”
Be careful how you delete information. You could be deleting a cell and not just a value. You are able to use “Undo” to restore the cell.
Just like cells can hold numbers, cells can also hold text, dates or time values. The numbers, text, dates and times can be formatted to suit the appearance you desire.
Exercise 2 – Auto Fill
In Cell A10 of the Insert and Delete worksheet tab, enter “Jan”. Then grab the handle of the cell and drag down to copy the names of the months into the adjacent cells. The same can be done with the days of the week. You can create your own number series as well by entering the first two values, highlighting both cells, and dragging the handle in the direction desired.
Just like cells can hold numbers, cells can also hold text, dates, or time values. The numbers, text, or dates and times can be formatted to suit the appearance you desire.
Many of the format changes can be done right from the toolbar at the top of your screen. The following exercise illustrates the use of these toolbar features to change the appearance of the cell.
Exercise 3 – Formatting from toolbar
Click on the Formats worksheet tab. Cell A1 does not have any special formatting applied. It bears the default format. Make the following formatting changes using the toolbar. We’ll leave A2 for comparison.
In cell A3 click on Bold B In cell A4 click on Italics I In cell A5 click on Underline U In cell A6 click on Left Justification In cell A7 click on Center In cell A8 click on Right Justification In cell A9 change the Font size In cell A10 change the Font style In cell A11 change the Background Color In cell A12 change the Font Color
These buttons did nothing to change the value or the way the value is stored in Excel. There are a few other buttons along this toolbar that pertain to the format of the value. These buttons are demonstrated in the following exercise.
Exercise 4 – Formatting Numbers from Toolbar
Click on the Formats worksheet tab. Cell C2 does not have any special formatting applied. Enter the number 5000 and select enter. Make cell C2 the active cell. Then select each of the following buttons to see the change on the value.
Currency
Table 1. Settings Available for Formatting the Value of a Cell Cell Format B2 General – Let Excel try to figure it out. (default setting) B3 Number – Always treat the value as a number. No letters are expected. Number of decimal places to show. (This rounds the display, not the value.) Choose whether to use a comma to separate at each thousands break. Choose how to display negative numbers. B4 Currency – Money. Same as Number but adds a symbol for currency. B5 Accounting – Money but aligned with “$” sign to the far left. Accountants use parenthesis to indicate negative values. B6 Date – Choose how to express the date, such as MM/DD/YY or DD/MM/YY or as a 4 digit year. Choose whether to spell the month out. B7 Time – Choose how to express the time in hours and minutes, as a 12-hour clock or 24-hour clock B8 Percentage – Automatically format the value into a percentage. B9 Fractions – Display and calculate fractional values B10 Scientific Notation – A format used by mathematicians to work with extremely large numbers. B11 Text – Information such as words or alpha characters. B12 Special – Zip codes and SSN formats.
Alignment: The next tab of the Format Cells Control Box is “Alignment.” This does just what it sounds like. It deals with the positioning of the information in the cell and on the spreadsheet. The upper left area of the panel controls how the value will be positioned in the cell horizontally and vertically. If left on “General” the we’re leaving Excel to try to guess which way is best.
Indent: The indent control allows information to be indented in the cell to form a sub list of main points.
Horizontal: This controls the positioning of the information horizontally. Information can be:
Exercise 6 – Formatting
Click on the Formats worksheet tab. In cell A1 type the following: “Formatting Styles” and press enter. Now highlight cells A1 and B1. On the “Alignment” tab select “Center Across Selection” in the Horizontal drop down list and click on “Okay”. Now the information which is really in cell A1 is displayed across the area of cells A1 and B1. If any modifications are to be done to the text, they must be done in cell A1. If new information is typed in cell B1, then the text will not display as desired. An example of this type of formatting is found on the Functions worksheet tab in row A. Notice that the information is located in B1, but displayed and centered from B1 to H1.
Vertical: The vertical control works similar to the horizontal control, but will display differently due to the vertical orientation.
Text Control: This area has 3 check boxes to turn features on and off for the cell. Wrap Text just allows the text to wrap automatically from one line to the next.
Note: A carriage return cannot be used in a cell. So if Wrap Text is on, don’t expect to be able to display separate paragraphs of information.
Shrink to Fit is handy for allowing Excel to automatically choose a font that will allow the text to fit in the cell when otherwise it would run off into the next cell.
Exercise 7 – Shrink to Fit Formatting
Click on the Formats worksheet tab. Look at column E. Due to the length of the text fields, the names are overflowing into column F. Highlight from E2 to E31. Select “Format Cells..” from the short cut menu. On the “Alignment” tab select “Shrink to Fit”. Click on “Okay.” Notice how the various names do not over flow into column F now.
Merge Cells allows the user to highlight a range of cells and force the range to be considered as one cell by selecting “merge cells. I would suggest using extreme caution when merging cells. There are times when there is simply no other way to accomplish what is intended but to merge the cells. But, when
Print Preview is a very helpful tool if you’re going to be printing the results of your work. This tool allows you to view how the printed results will look when sent to the printer. With the sheet highlighted, click on the File menu, then Print to view a preview of the print version of the worksheet.
Under Settings you have control of how the document prints including Portrait or Landscape Orientation, Paper size, margins, and scaling.
At the bottom of these setting is a link to Page Setup.
The Page Setup link opens many options to adjust how the page will print.
Each tab provides many options which control the printed appearance.
The margins of the printed page can be adjusted on the Margins tab. A Header or Footer can be set for the page on the Header/Footer tab. You can set gridlines to print on the Sheet tab.
