Loading...

PRDV252: Intermediate Excel

Unit 2: Formatting Spreadsheets   This unit will discuss formatting in more depth for cells, text, numbers, including dates, and conditional formatting as well as the worksheet tabs themselves. Formatting is often needed on spreadsheets because it assists the reader to conceptualize numbers. It can be used to highlight certain information that needs to be emphasized or create a coherent organization for large pieces of data. This unit will deal with many of the features and commands on the Home Ribbon.
 
Conditional Formatting will also be introduced. Conditional Formatting is used to format text and numbers based on certain criteria or conditions. This feature was greatly improved in the newer versions of Excel®. If you have an older version or some other spreadsheet software, this would be a good topic to investigate in the Help files of your program.

Unit 2 Time Advisory
Completing this unit should take you approximately 3 hours.

☐    Subunit 2.1: 1 hour

☐    Subunit 2.2: 1 hour

☐    Subunit 2.3: 1 hour

Unit2 Learning Outcomes
Upon successful completion of this unit, you will be able to: - format a Title using the Merge and Center control, along with 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; - format dates; - format a list of numbers conditionally using databars and highlight rules; and - explain how to use Wrap Text to split text onto two lines within a cell.

2.1 Using Help   Note: The first thing we will look at in this unit is Excel®’s Help feature. Microsoft puts a lot of effort into creating useful Help files. No matter what version of Excel® you are using, the Help directory that comes with the program is quite large. With this much information available, it is useful to become familiar with this tool.

  • Web Media: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic B: Finding and Using Help” Link: TheAppTrainer’s MS Office 2010. Excel® Basic: Unit 1 Topic B: Finding and Using Help (YouTube)
     
    Instructions: Watch the short four-minute video and open your version of Excel® to explore your Help feature. Investigate the tools on the toolbar. Use the keyword CHARTS as was done in the video and see what shows up in your Help window. Use the back arrow or Home icon and then take a few minutes and investigate a topic of your choice. Click the various links to see how you can better use the Help feature.
     
    Completing this assignment should take approximately 10 minutes.
     
    Terms of Use: This resource is licensed under a Creative Commons Attribution 3.0 Unported License. It is attributed to TheAppTrainer, and the original version can be found here.

2.1.1 Advanced Formatting Cells and Numbers   Note: in this subunit you will be working with different aspects of Formatting using both the tools on the Home ribbon in the Font and Paragraph groupings and the Format Cells Dialog box. Both can be used to format but you may need the dialog box to do more advanced formatting. It is helpful to become familiar with this dialog box.

  • Web Media: GCFLearnFree.org’s “Excel® 2010: Formatting Cells” Link: GCFLearnFree.org’s Excel® 2010: Formatting Cells (HTML)
     
    Instructions: Click on the link above. Read through the six pages and watch the video on page 2. Note that this resource shows formatting features on the ribbon. If you have Excel® 2010, open the Excel® practice spreadsheet and follow the directions on page 6 to practice what was demonstrated in the video. This video shows many different ways to format text using the tools on the Home ribbon.
     
    Completing this assignment should take approximately 20 minutes.
     
    Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

  • Reading: The Saylor Foundation’s “Format Cells Dialog Box” Link: The Saylor Foundation’s “Format Cells Dialog Box” (PDF)
     
    Instructions: Read through the document. Open your version of Excel® and compare it to what you just read about. This dialog box is found in every version of Excel® with only small variations as new tools are added. Try using some of the features you think might be useful. Take note of which features you can find on the Home ribbon and which features you will have to use through the dialog box.
     
    Completing this assignment should take approximately 20 minutes.

2.1.2 Create Borders and Shading   - Web Media: The Saylor Foundation’s “Applying Borders and Shading to Cells” Link: The Saylor Foundation’s “Applying Borders and Shading to Cells” (YouTube)
 
Instructions: Please click on the link above and watch the short video. After you have finished, open your version of Excel® and try adding borders and shading to a spreadsheet.
 
Completing this assignment should take approximately 10 minutes.

2.2 Working with Worksheets   2.2.1 Navigating Worksheets   This subsection will highlight various ways to navigate through large worksheets and select data in Excel®. One spreadsheet can have thousands of columns and/or rows. Learning how to quickly navigate through large spreadsheets is a real timesaver. 

  • Web Media: TheAppTrainer’s “MS Office 2010. Excel® Basic: Unit 1 Topic C: Navigation and Selection Techniques” Link: The AppTrainer’s MS Office 2010. Excel® Basic: Unit 1 Topic C: Navigation and Selection Techniques (YouTube)
     
    Instructions: Watch the 15-minute video. Pay particular attention to the way the speaker selects noncontiguous ranges of text and numbers using the Ctrl key on the keyboard. This is an important technique to master. Then open up your version of Excel® and try these techniques on your own.
     
    Completing this assignment should take approximately 30 minutes.
     
    Terms of Use: This resource is licensed under a Creative Commons Attribution 3.0 Unported License. It is attributed to TheAppTrainer, and the original version can be found here.

2.2.2 Formatting Worksheet Tabs   A workbook can have over a thousand worksheets so learning to name and format worksheet tabs will help you quickly navigate through a large workbook visually. This makes dealing with the large workbook with many worksheets easier to manage. The videos within this link also discuss moving, grouping and ungrouping, and rearranging worksheets within a workbook as well as Freeze Panes for working within a large worksheet. There is a lot of information here so take your time and practice these skills in your own version of Excel®.

  • Web Media: GCFLearnFree.org’s “Excel® 2010: Worksheet Basics” Link: GCFLearnFree.org’s Excel® 2010: Worksheet Basics (HTML)
     
    Instructions: Read through the six pages and watch the videos on page 2, 4, and 5. If you have Excel® 2010, open the Excel® practice spreadsheet and follow the directions on page 6 to practice what was demonstrated in the videos.
     
    Completing this assignment should take approximately 30 minutes.
     
    Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

2.3 Conditional Formatting: Highlight Rules and Databars   Conditional formatting is an easy way to format large pieces of data or numbers to make important information stand out. This feature changed quite a bit with the newer versions of Excel®: Excel® 2007 and 2010. If you are using an older version of Excel®, you will not be able to use most of the features that this subunit talks about, although you can still conditionally format. You should explore the Help file in your version of Excel® to find out how to conditionally format in your version. It can be done, and you can probably find YouTube videos as well. This subunit will show the examples in the newer versions which have greatly expanded the availability of different types of conditional formatting tools.

  • Web Media: GCFLearnFree.org’s “Excel® 2010: Using Conditional Formatting” Link: GCFLearnFree.org’s Excel® 2010: Using Conditional Formatting(HTML)
     
    Instructions: Go through the five pages, watching the video on page

    1. When you have finished, there is a practice document at the end of this presentation for those with a newer version of Excel®. Open spreadsheet and follow the instructions to practice conditional formatting.
       
      Completing this assignment should take approximately 25 minutes.
       
      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.
  • Web Media: Sophia.org’s “Conditional Formatting in Excel®” Link: Sophia.org’s Conditional Formatting in Excel® (HTML)
     
    Instructions: Watch the video on conditional Formatting. After the video has concluded and if you have a newer version of Excel®, click on the link to download the Excel® document and practice the techniques you learned. Try each type of conditional formatting until you are comfortable with it use and how to adjust and adapt the formatting to fit your needs.
     
    Completing this assignment should take approximately 35 minutes.
     
    Terms of Use: The resource used in the link above is released under a Creative Commons Attribution-NonCommercial 3.0 Unported License. It is attributed to Pete Dulany, and the original version can be found here.