Since Microsoft Excel is widely used in industry, and we are using Microsoft Windows, we will focus on Excel going forward. There are many similarities across spreadsheet software, so the skills we are learning can be translated to other software and apps. The following ‘Practice It’ assignments are designed to be completed using Microsoft Excel in Office 365 on a PC with Windows 10 or higher.

We will use Excel to perform complex calculations, analyze data so that we can make intelligent decisions, and create visually interesting charts and graphs that help us understand the data. Since Excel is used for Data Analysis, it is best to use a keyboard and mouse or touchpad rather than the touchscreen.

In Excel, data is stored in a cell. Cell content is anything that is stored in the cell and can be either a constant value or a formula. The most commonly used values are text values and number values. Values can also be a date or time. A text value is also referred to as a label.

Prefer to watch and learn? Check out this video tutorial. NOTE: The assigned activity does not completely match this video tutorial.

Complete the following Practice Activity and submit your completed project.

For our first assignment in Excel, we will create a spreadsheet with monthly expenses. This spreadsheet will provide us with an overall picture of our financial health by helping us understand where we are spending our hard-earned money. We will start with a new blank Excel Spreadsheet.

1. Start Excel. Click Blank Workbook.
2. Select File, Save As, Browse, and then navigate to your Excel folder on your NbCC OneDrive or other location where you save your files.
   • Name the workbook as FirstnameL_Excel1 (example AynslieC_Excel1).
3. Take a moment to locate the following components of the Excel workbook window.
   • Notice how Columns are lettered and Rows are numbered.
   • The intersection of a row and column is a cell.
   • The active cell in the image is A1.
   • Notice the vertical and horizontal scroll bars. Use the arrows to practice scrolling on the page.
4. In cell A1, type “My Budget By Month” and press Enter.
5. In cell A2 Type “For the First Quarter” and press Enter.
6. Starting in cell A4, Type each of the following (excluding the circle bullets), pressing Enter after each:
   • Housing
   • Groceries
   • Utilities
   • Misc Expenses
   • Monthly Total
7. In cell B3, type January and press Enter.
8. Select cell B3 and use the fill handle (small square in lower right corner of cell) to drag to cell D3.
   • Notice how the names of the months automatically generate.
   • The fill handle enables auto-fill, which generates and extends a series of values into adjacent cells based on the value of other cells.

11. Adjust the column width for column A to Width 18.71 (136 pixels) by dragging the right boundary (between columns A and B) to the right.
    • Can also select the column, right-click, select Column width,
12. Select the range B3:D3 and center the text.

13. In cell B4, type 1200 and enter the remaining numbers as shown:

14. In cell B8, type =b4 + b5 + b6 + b7 and press Tab.
15. In cell C8, type =c4 + c5 + c6 + c7 and press Tab.

16. A quicker way to enter a formula is with a function. We will use the SUM function next.
    • In cell D8, click AutoSum on the Home Tab, Editing Group and press Enter.
17. In cell E3, type Total and then press Enter.
18. Click in cell E4, Press Alt and + = key.
    • This is a keyboard shortcut that enters the Sum function. If the keyboard shortcut does not work (this is common due to variations in keyboards), use the AutoSum technique from step 16.
19. Click the Enter button on the Formula Bar, which is the green or blue check mark.
20. With Cell E4 selected, drag the fill handle in cell E4 down through cell E8.
21. Click in cell F3, type Trend and press Enter.
22. Click in cell A1, and drag your cursor to the right to select the range A1:F1.
    • On the Home tab, in the Alignment Group, choose Merge and Center.
    • The title should be Merged and centered in the range A1:F1.
23. Using the same technique, Merge and Center the title in the range A2:F2.
24. Home Tab, Styles Group, then choose the arrow next to cell styles.
    • Apply the Title style to cell A1
    • Apply the Heading 1 style to cell A2.
    • Apply the Heading 4 style to the ranges B3:F3 and
    • Select A4:A8 and click F4 to repeat the last command (Apply the Heading 4 style). 
25. Select ranges B4:E4 and B8:E8
    • Home Tab, Number Group. Select the arrow to view a drop down list of all number format
    • Apply the Accounting number format.
26. Apply the Comma number style to the range B5:E7.
    • This is located on the Home Tab, Number Group, and select the comma icon.
27. Apply the Total number style to the range B8:E8.
    • Cell styles are on the Home Tab, Styles Group, then choose the arrow next to cell styles.
28. AutoFit column D.
    • Select column D by clicking on the D Column Header.
    • Then, double click the line between the D and E.
    • Or, with Column D selected, on the Home Tab, Cells Group, click the arrow next to Format and choose auto fit for the Column.
29. Apply the Slice theme to the Workbook.
    • On the Page Layout Tab, in the Themes Group, click the arrow under Themes
    • choose Slice (third row, first column).
    • If necessary, adjust the width of the columns to ensure you can see all of the cell content.
30. Select the range A3:D7.
    • On the Insert tab, in the Charts group, click Recommended Charts,
    • click All Charts, select Clustered Column chart and then click OK.
31. With the chart selected, under the Chart Design Tab, in the Chart Layouts Group,
    • Choose the Add Chart Element and ensure the Chart Title is ‘Above Chart’.
    • Change the Chart Title to My Budget.
32. Drag the chart by clicking and holding any of the chart outer lines using the four-sided arrow mouse pointer.
    • Move the chart so that the upper left corner is inside cell A10.
33. Ensure the chart is still selected, and apply Chart styles, Style 6.
    16. Chart styles are located on the Chart Design Tab, under Chart Styles.
    17. Click the down arrow (“more” button, which is the upside-down triangle with the line above it) to see all of the Chart Styles.
34. Using Change Colors select Colorful Palette 4.
    • The Change Colors button is located on the Chart Tools, Design Tab, under Chart Styles
35. Insert a Sparkline
    • Select the range B4:D4. Be sure to not include the totals in the sparkline range.
    • Sparklines are located on the Insert Tab, Sparklines group, then choose Line.
    • Ensure the Data Range is B4:D4
    • Type F4 in the Location Range textbox and press enter.
    • The sparkline will be displayed in cell F4. 
36. Show markers on Sparkline
    • With cell F4 selected, on the Sparklines Tab, in the Show group choose the checkbox next to Markers.
37. Apply the Dark Green, Sparkline Style Colorful #4 style (or similar).
    • Styles are located on the Sparkline Tab in the Style group.
    • Choose the down arrow to view more styles.
    • Select style in the sixth row, fourth column
38. With cell F4 selected, use the fill handle to fill the sparkline to cells F5:F7.
39. On the Page Layout Tab, Sheet Options Group, click the arrow to launch the Page Setup Dialog Box.
    • Notice how it opens to the Sheet tab.
    • Go to the Margins tab
    • Click the checkbox to center the data and chart horizontally on the page.
40. With the Page Setup Dialog Box still open, go to the Header/Footer tab.
    • Choose Custom Footer and insert the File Name in the left section of the footer.
    • The file name will show in the Print Preview and also when the spreadsheet is printed.
    • This is a field, so if the file name is changed, it will automatically update the footer with the new file name.
41. Close the Page Setup Dialog Box, and click File to go to Backstage View.
    • Under Info, choose Properties, and then Advanced Properties. Add the following Properties:
      • Title: Excel Budget
      • Subject: SAAL1869 Office Fundamentals
      • Author: Your First and Last Name
      • Tags: Sums, Charts, Budget, Excel
42. Click the back arrow to exit backstage view.
43. Click the Save shortcut button and ensure your file is saved in a safe location.
44. Select the range A2:F5 and then press Ctrl + F2.
    • This is the keyboard shortcut that displays Print Preview.
    • If you do not have the shortcut key, click File to enter Backstage View, Print and view the Print Preview.
45. Change the print settings option to Print Selection and notice how the Print Preview changes.
    • Printing of this assignment is not required, but if you needed to print a copy, you would click Print.
46. Exit Backstage view and Save your file.
47. On the Formulas tab, in the Formulas Auditing group, Show the Formulas.
    • This is a toggle button, so press it once to show the formulas.
    • Press it again to remove show formulas.
    • Notice how row 8 and column D display the formulas rather than the result when the show formulas is turned on.
    • Turn show formulas off.
48. On the Page Layout tab, in the Page Setup group,
    • Change to Landscape orientation and
    • Scale the data to fit on one page. 
      • To scale the data, choose the arrow in the page set up grouping to open the dialog box,
      • you will find the scaling option on the page tab.
49. Run spelling and grammar check from the Review tab using the Spelling button in the Proofing group, making any spelling corrections as necessary.
50. Compare your file to the image below and make all necessary corrections.
51. Submit as instructed by your instructor.

Media Attributions