Return to Skills Assessment/Training

Major sections

bulletIntroduction and preliminary data entry
bulletFormulas
bulletText/value formatting
bulletCell borders and shading
bulletColumn width
bulletPrinting
bulletCharts
bulletColumn chart
bulletPie chart
bullet3D Column chart
bulletPrinting charts

Complete all instructions below using a PC or a Mac. Although Office 97 for Windows and Office 98 for Mac are very similar, you might consider completing the assignment on the with which system you are less familiar.

If you find any instructions below that do not appear to work correctly, please contact Robin or John David.

As you carry out the instructions for this evaluation, use the outline below for your own use to make notes about each step, in case you have questions or if you simply want to "check off" that you finished each step. You may write anything you like (or nothing) for each item in the outline. You will not have to turn this sheet in.

You will be modifying an Excel file, making it look like the spreadsheet and charts posted on the SLA bulletin board.

"Hints" below begin with a keyword you can type after selecting Microsoft Excel Help from the Help menu; following the keyword will be additional guides to follow within the options Excel presents as help for the original keyword.

If you need other help, consult the big spiral-bound Office 97 textbook, to be kept IN (not on) the HelpDesk when not in use.

bulletCopy \\Acad_Jax\\Read\Barham\SLA\ExcelEval1.xls to a floppy or to your personal network space
bulletOpen your copy of the file. You may complete the following steps in most any order. Save often!
bulletEnter your name in G1
bulletSince the Salary amount is the same every month, copy the value in C4 using the fill handle. Hint: autofill, Automatically fill in data based on adjacent cells, Copy data within adjacent rows or columns

bulletFormulas
  1. Enter a formula in C7 to total the September income values above it (there are at least 4 ways to do this); after entering the formula, see if it's working correctly by entering a different number for one or more values in the Sept column; if you want your numbers to look like what's printed, be sure to change them back to the original values
  2. Once your formula for September income total is correct, use the autofill feature to copy it across for Oct, Nov, and Dec; after copying, look at what formula Excel put into each cell to be sure they are correct
  3. H4: Enter a formula for Salary total for the semester
  4. Autofill it down for Interest, Stock
  5. H7: Total semester income formula
  6. C10: Formula to calculate Sept's Social Security and Withholding Tax as 21.5% of Sept salary; autofill across
  7. C11: Formula for tithe as 10% of Sept income total; autofill across
  8. C15..F15: Formulas for monthly expense totals
  9. C16..F16: Formulas for monthly net
  10. H10..H14: Formulas for semesterly expense item totals
  11. H15: Formula for semester expense total
  12. H16: Formula for semester net
  13. Saved lately?
  14. K4: Formula (actually, an Excel function) to find the largest salary amount for the entire semester- Hint: Max
  15. L4: Formula (actually, an Excel function) to find the smallest salary amount for the entire semester- Hint: Min
  16. M4: Formula (actually, an Excel function) to find the average salary amount for the entire semester- Hint: Average
  17. Select K4..M4; autofill down to Net row; "eyeball" the numbers to see if they look right; select K8..M9; Edit Clear Contents
  18. I4: Enter a formula that will tell what percent of the total income for the semester (in H7) came from Salary (H4); the value it displays should be between 0 and 100 (not 0 and 1) but should NOT have a % sign, so be sure to multiply by 100 in your formula.
  19. J4..K4: Autofill down to the Stock row; Hint: if you get #DIV/0!, look up absolute, Move or copy a formula; you'll want part of the formula to be relative and part to be absolute; repeat the previous step and this one until you figure it out
  20. I10..I14: Enter similar % formulas for the Expense info
  21. Jesus saves! Do you?!


bulletText/value formatting
  1. To make everthing in column A bold, click the A column heading to select the entire column, then click the Bold button on the toolbar; repeat for column B
  2. Make all of rows 1 and 3 bold
  3. Change the font of all cells click the rectangle above row 1 and to the left of column A to select all cells in the spreadsheet; use the font/size menus on the toolbar to go to Times New Roman 12 pt
  4. Select cells C3..M3 and click the Align Right button on the toolbar
  5. For cell I3 only, Align Center
  6. To get 2 decimal places on all numbers, select all cells (use the method above), go to the Format menu, select Cells..., select the Number tab, choose Number from the Category list, be sure Decimal places is set to 2, and be sure Use 1000 Separator is turned on
  7. Notice that the 2000 in D1 is formatted as 2,000.00; retype the 2000 with an apostrophe in front, so Excel will treat it as text
  8. To place dollar signs on some cells (here, the first and last row of a column of numbers), select the first range of cells to receive dollar signs (C7..H7), hold down the Contrl key (Command (Open Apple) for Mac), and click/select each other cell or group of cells to receive dollar signs (H4, H7, C10..H10, C15..H16); release the Control/Command key; Format Cells Number Currency 2; if some cells have ###### in them, it means the column is too narrow for the information in it - we'll fix that problem soon
  9. Save


bulletCell borders and shading
  1. To control where gridlines will print (rather than have them printing around every cell), first turn off the gridline option by going to Tool Options (Prefernces, for Mac) View and unchecking Gridlines; you may also need to turn off File PageSetup, Sheet, Print, Gridlines.
  2. Select C3..I7; from the Borders tool (on the toolbar, just to the left of the Fill color (paintbucket) and Font color (A) tools), select the full grid option (third row, second option on left)
  3. Repeat the previous step for ranges C10..I16, K3..M7, K10..M16
  4. To make several cells appear as one, outline them; select A3..B3; from the Borders tool, choose the outline option (third row, third from left)
  5. Repeat the previous step for ranges A4..B4, A5..B5, A6..B6, A7..B7, A9..B9, A10..B10, ..., A16..B16, C9..I9, and K9..M9; remember you can Control/Command click/drag on multiple ranges to select them before formatting
  6. To place a heavier line on the bottom of some cells, to visually indicate numbers being totaled, select C6..H6 and C14..H14; Border tool, second row, second option
  7. To place a double line on the bottom of some cells, to visually indicate numbers being grand-totaled, select C15..H15; Border tool, second row, first option
  8. Note: all bordering options can also be accessed under Format Cells Border
  9. To shade cells, in this case to show where formulas were entered, select C7..F7, C10..F11, C15..F16, H4..I6, H7, H10..I14, H15..H16, K4..M7, K10..M16; from the Fill color (paintbucket) tool menu, select a shade of gray
  10. Save


bullet Column width
  1. In general, to change the width of a column you position the cursor over the divider line between column headings; for example, to change the width of column A, place the normal heavy white crosshair over the line separating the column A header from the column B header; the cursor will change to a thin black cursor with arrows pointing left and right; drag the divider line left or right to narrow or widen column A, as needed; as you drag, a Width measurement will be displayed
  2. Change the width of column A to 2.00
  3. B: 7.00
  4. C..F: 8.00
  5. G, J: 2.00
  6. H, I: 8.00
  7. K..M: 10.00
  8. Save


bulletPrinting in landscape, and other miscellaneous printing options
bulletFormat, PageSetup, Page tab, Landscape is all that's required to get landscape printing for this assignment; however, there are some other options to be aware of on the PageSetup screen that control printing options for individual worksheets
bulletSave
bulletThe Options button allows you to adject paper size, such as for letter (8.5x11), legal (8.5x14), or 11x17
bulletOn the Page tab, under Scaling, you can manually set a scaling % or you can tell Excel to scale automatically to fit to a certain number of pages; be aware that if the Fit option is chosen, you have no control over the exact scaling % and that page breaks you may have inserted with Insert PageBreak will not have any effect
bulletThe Margins tab allows you to control margin settings, as well as to automatically center pages horizontally and/or vertically
bulletThe Header/Footer tab has nothing beyond header/footer settings
bulletThe Sheet tab allows you to select a specific range of adjacent cells to print, lets you repeat certain rows and/or columns somewhat like headings on each page, lets you turn full gridline printing on/off, lets you repeat the row numbers (1, 2, 3, ...) and column headings (A, B, C, ...) on each page


bulletCharts - Excel supports a number of different kinds of charts allowing you to visually analyze the numbers in your spreadsheet; if your numbers change, your charts change with them
bulletAlthough you can put charts in the same sheet as your numbers and formulas, for this assignment you will place them on a separate sheet.
bulletFirst, assign names to two sheets; at the bottom of the Excel window you will see tabs labeled Sheet1, Sheet2, etc.
bulletDouble-click on the label Sheet1, the label of the sheet containing your work so far; when it is highlighted, type Budget
bulletChange the Sheet2 label to Charts
bulletClick back on the Budget tab
bulletOne of the many wizards included in Excel is the Chart Wizard, which will guide you through the creation of a chart
bulletTo create any chart, first select the cells of numbers/text to be included in the chart, then click the Chart Wizard (on the toolbar, the button that looks like a small bar chart with red, blue, and yellow bars)
bulletColumn chart
  1. For a comparison, month by month, of the semester's income from each major category, you will create a column chart
  2. Select cells A3..E6, which includes not only the numbers to be charted but also the headings you entered to identify what the numbers represent
  3. Click the Chart Wizard button
  4. For Chart Type, be sure Column is selected (You might want to scroll through the list to see all the different kinds); click Next
  5. For Source Data, click Next (since we preselected the data before starting the Chart Wizard)
  6. For Chart Options, on the Titles tab, type Fall Income for (your name)
  7. On the Gridlines tab, for Value (Y) axis, turn on the option for Minor gridlines; click Next
  8. For Chart Location, choose As an object in and change Budget to Charts
  9. Click Finish
  10. With the Fall Income column chart selected in the Charts sheet, drag it up to the very top left corner of the sheet
  11. Use the bottom right black rezising handle to drag the size/shape of the chart so that it covers A1..E18
  12. Save
  13. You need to change the plot area background color from the default gray to white; with the entire chart still selected, press the right arrow key, changing the portion of the chart selected; if the gray plot area is not selected, keep pressing the right arrow key until it is; Format SelectedPlotArea; change Area to None; click OK
  14. You need to change the formatting of the Y-axis labels, so they will be formatted different from the way you formatted them in the spreadsheet; either click on (or press the right arrow key enough times) to select the Y axis; Format SelectedAxis Number; from the Number tab, set Decimal places to 0 and turn off the 1000 separator; click OK
  15. To reduce the size of the chart title (Fall Income), click on it and change the font size to 14 using the font size menu on the toolbar
  16. Save


bulletPie Chart
  1. For a rough visual comparison of your income sources for the entire semester, create a pie chart on your Charts sheet using the following guidelines
  2. Data in G4..G6, labels in A4..A6; remember you can select both using the Control key (Command key for Mac)
  3. Chart Options, Legend tab, turn off Show Legend
  4. Chart title: Semester's Income for (your name); 14 pt type
  5. Location for pie chart in Charts sheet: F1..I18
  6. Click on the pie; Format, Selected DataSeries, DataLabels, Show label and percent; OK
  7. Save


bullet3-D Column Chart
  1. To show all Expense values for each month, create a 3-D column chart on your Charts sheet using the following guidlelines
  2. Data in C10..F14, labels in A10..A14 and C3..F3
  3. No legend
  4. Chart title: Fall Expenses for (your name); 14 pt type
  5. Category (X) axis title: Expense Category
  6. Series (Y) axis title: Month
  7. Vertical (Z) axis Scale info: Maximum should be 300, Major unit should be 100
  8. X, Y, and Z axes should have major gridlines
  9. No decimal points on Z axis labels
  10. If all expense category labels or all month names are not displayed, reduce the font size for each until they are
  11. With any part of the chart selected, form the Chart menu, select 3DView and adjust as needed to make your chart look close to the printed version
  12. Click on the walls (gridlines) behind the columns and change the gray coloring to white
  13. Location for column chart in Charts sheet: A19..I38
  14. Save


bulletChart printing issues
bulletTo print all charts on the sheet, be sure none of the charts is selected; if one is, click on any cell outsie all the charts
bulletAny PageSetup options you set will apply to the sheet, unless you have a chart selected, in which case the PageSetup options will apply to that chart only
bulletPrint all 3 charts on a single sheet of paper in landscape orientation, changing margins and/or scaling/fitting as needed
bulletSave

Turn in your printouts and your floppy disk with your work to Robin.