• Unit 1: Formatting Spreadsheets

    When creating a spreadsheet, simple formatting techniques can help you improve its readability for your audience. For example, you can highlight information you want to emphasize and organize large amounts of data to make it more clear, consistent, and understandable. Try experimenting with the formatting tools we introduce in this unit to see what looks best – whether you are writing an online report for your boss or creating a spreadsheet to share with your work colleagues. In this unit, we discuss how to format cells, text, numbers, and dates. We also introduce conditional formatting tools, which allow us to format text and numbers based on specific criteria or conditions of the data.

    Completing this unit should take you approximately 2 hours.

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

      • format text in spreadsheets, 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 how to format the contents of cells and numerical values in spreadsheets. This is particularly important when sharing spreadsheets with other users. Everyone should be able to easily locate the data they need from your spreadsheet.

      • Formatting Cells in Microsoft Excel Page

        You can access most formatting commands in Excel from the Format Cells dialog box. To open this box, click the dialog launcher button – the small arrow in the lower right corner of the Number group of the Home tab. This document provides step-by-step instructions on using the Format Cells dialog box to perform standard formatting functions. Each tab in the dialog box controls a different formatting feature. Read this document, which 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. The commands are very similar to the formatting tools used in Excel.

      • Formatting Special Data Page

        Some data types require extra care when putting them into a spreadsheet. For example, when you insert dates, you must make sure they are formatted as dates, not as text or numbers. Watch this video to learn how to input dates properly and use them in formulas.

      • Borders and Shading Page

        Use borders and shading to highlight individual and groups of cells or data in your spreadsheet. For example, you may want to highlight a header row or column to organize the message you want to convey and make the information and the message you want to convey stand out. Watch this tutorial on how to use these formatting techniques.

    • 1.2: Formatting Worksheet Tabs

      Workbooks are often organized into 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 Page

        Grouping worksheets within a workbook can be helpful, especially if you want to use the same formula over multiple worksheets. This can save time and help you avoid mistakes when you must retype the same formula several times. It also can give your information a more organized and consistent look. Watch these two tutorials for tips on how to group worksheets and use the same formula across several groups of worksheets.

    • 1.3: Conditional Formatting

      Conditional formatting allows you to easily apply formatting, such as using a different font, color, or bolding, to cells in a spreadsheet based on their value. This can make it easier for your audience to see trends in your data.

      For example, let's say you are using a worksheet to record the daily temperature. You can use conditional formatting to change the font color so that every day above the freezing point is highlighted in orange while those below freezing are blue. This makes it easier to compare the data visually. You can probably think of lots of examples like this, such as when you are creating business reports.

      Conditional formatting also makes it easier to see and correct errors in large data sets. For example, it can be hard on the eyes and time-consuming to scroll through a large spreadsheet to identify typos and other mistakes. However, you can use conditional formatting to highlight cells with errors (such as numbers that fall outside a specific range or are not a desired quantity).

      For example, imagine you record 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 cells that are outside of your expected range of income. This lets you quickly and easily see if you accidentally mistyped one week's income entry.