• Unit 1: Formatting Spreadsheets

    When making a spreadsheet, it is important to use formatting techniques to improve readability for the user. We use formatting tools to highlight information we want to emphasize and clearly organize large amounts of data. In this unit, we discuss how to format cells, text, numbers, and dates in depth. We also introduce conditional formatting tools to format text and numbers based on specific criteria or conditions.

    Completing this unit should take you approximately 1 hour.

    • Upon successful completion of this unit, you will be able to:

      • format text, including font and color selection;
      • format numbers from the toolbar and format cells dialog box;
      • format decimal places and dollar signs from the toolbar and format cells dialog box;
      • apply borders and shading in a spreadsheet;
      • format multiple worksheets within a workbook and group worksheets within a workbook; and
      • format a list of numbers conditionally using data bars and highlight rules.

    • 1.1: Formatting Cells and Numbers

      In this section, we discuss methods of formatting the contents of cells and numerical values in spreadsheets. This is particularly important when sharing a spreadsheet with other users. All users must be able to locate data within a spreadsheet easily.

      • Formatting Cells in Microsoft Excel File

        Let's begin by reviewing the tools available in the Microsoft Excel toolbar. Read this document, which provides step-by-step instructions on using the Microsoft Excel dialog box to perform common formatting functions. Each tab in the dialog box controls a different formatting feature. This document focuses on using the Number, Alignment, Font, Border, and Fill formatting features.

      • Formatting Cells in Google Sheets Page

        Watch this video for an overview of formatting methods in Google Sheets. You will see it is similar to Excel.

      • Formatting Special Data Page

        Some special types of data require extra care when inputting them into a spreadsheet. For example, when you input dates, you need to make sure they are actually formatted as dates. Watch this video to learn how to properly input dates and how to use them in formulas.

      • Borders and Shading Page

        When you create a spreadsheet, adding borders or shading can highlight certain cells for the people who use the spreadsheet. For example, you may want to highlight a specific row or column to make your information easier to read. Watch this tutorial on how to use these formatting techniques.

    • 1.2: Formatting Worksheet Tabs

      Often, one workbook will have multiple worksheets, each with different data and calculations. It is important to keep track of what each worksheet represents in an organized and manageable way. You can accomplish this by formatting the tabs at the bottom of the workbook representing the different worksheets.

      • Adding Tabs to a Workbook Page

        Watch this tutorial on formatting tabs in a workbook.

      • Grouping Worksheets in Microsoft Excel Page

        Grouping worksheets within a workbook can be helpful, especially if you want to use the same formula over multiple worksheets. This can save you from having to retype a formula several times. Watch these tutorials on how to group worksheets and use a formula in multiple grouped worksheets.

    • 1.3: Conditional Formatting

      Conditional formatting lets you apply formatting, such as using a different font, color, or bolding, to cells in a spreadsheet based on their value. This can be useful for observing trends in data.

      For example, you may be recording the daily temperature in a worksheet. You can use conditional formatting to change the font color for all the days that were above the freezing point. This way, you can easily visualize dates when the temperature was above a certain limit.

      You can also use conditional formatting to find errors in large data sets. It is hard to scroll through a large set of data to find an error. But, you can use conditional formatting to highlight cells that have mistakes.

      For example, imagine you are recording your weekly income in a spreadsheet. At the end of the year, you notice the total does not match what you expect. You can use conditional formatting to highlight any cells that are outside of your expected range of income. This allows you to quickly and easily see if you accidentally mistyped one week's income entry.