Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Numbers > Design > Summary
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Design: Summary

Documenting your sheet completely helps the user and later helps anyone who needs to modify the sheet. Your documentation can be shown directly on the sheet or in hidden comments and data validation input messages. Comments can be formatted - both the text and the shape.

A What If sheet allows the user to see the effects of changing the input values.

Logical tests compare two statements  with a logical operator, like =, <, and >. A logical test must be either TRUE or FALSE. The IF function uses a logical test as the first argument. The second and third arguments are the values to use when the argument is true and when it is false.

Conditional formatting makes use of a logical test to set some of the formatting for a cell. You can use one of the pre-designed schemes or create a custom format which will update when you change the theme.

Parts of documents can be used in documents for other programs by embedding or linking the source data or by making a picture of the data. The method you use will affect the size of the file. The file size of a document can be reduced by saving it under a new name.

What You Printed for Project 5: Design
 

  File and sheet What is it? What lesson? # of pages

 1. 

trips30-Lastname-Firstname.xlsx -
   sheet Specials

Analysis of the sheet (printed page 2 of sheet only) Analysis 1 page

2.

 trips31-Lastname-Firstname.xlsx -
   sheet Specials		 sheet with analysis in a comment (page 1 only) Comments 1 page

3.

trips31-Lastname-Firstname.xlsx -
   sheet Specials		 comments at end only Comments 1 page

4.

sketch of your layout Your ideas for the Bonus calculator sheet Create a What If sheet 1 page

5.

 trips34-Lastname-Firstname.xlsx -
   sheet Bonus		 Bonus calculator for Gardner and comments Test Your Sheet 2 pages

6.

 trips36-Lastname-Firstname.xlsx -
   sheet Bonus		 Bonus sheet for Gardner -with formulas, no comments, Landscape Conditional Formatting 1 page

7.

 trips36-Lastname-Firstname.xlsx-
   sheet Bonus		 Bonus sheet for Heinz - Portrait, no comments, no formulas showing Conditional Formatting 1 page

8.

 sharedata-Firstname-Lastname.docx Chart of Number of tickets sold - inserted with 5 methods Picture 1 page

9.

sharedata-Firstname-Lastname.docx Page 2 - table with file sizes Document Properties 1 page

Skills covered - by lesson

Analyze a Sheet

  • Analyze a sheet
  • Record analysis on the sheet
  • Create line break inside a cell

Comments

  • Create a comment
  • Format a comment - fill and font color
  • Edit an existing comment
  • Use keyboard shortcut in a comment
  • Add a command not on the ribbon to the Quick Access Toolbar 
  • Change the shape of a comment

Print Comments

  • Make comments always visible
  • Paste text into a comment
  • Adjust an AutoShape with adjustment handles
  • Zoom the view and see temporary formatting changes
  • Move a comment
  • Print comments as displayed
  • Print comments together at the end
  • Hide comments

Create a What-If sheet

  • Plan a What-If sheet:
    • Goals
    • Inputs
    • Outputs
    • Design Layout
    • Sheet features and Titles
  • Create a table of assumed values
  • Create a data entry table
  • Create a results table  
  • Use a function to insert today's date
  • Create a concatenated label

Test a What-If Sheet

  • Test fit to screen
  • Test with easy numbers
  • Test with special numbers
  • Document the sheet 
  • Create a data validation input message
  • Change the theme and see how it affects the sheet

Using IF function

  • Write an IF function
  • Name cells
  • Use the Function Arguments dialog
  • Use cell names in a function

Conditional Formatting

  • Create conditional formatting
  • Create conditional formatting by formula
  • Test conditional formatting
  • Change theme to see how it affects conditional formatting
  • Use the sheet
  • Find cells that have conditional formatting

Sharing Data: Embed

  • Embed a sheet in a Word document
  • Open an embedded object 
  • Edit an embedded object in place

Sharing Data: Link/Icon

  • Link a sheet in a Word document
  • Manage formatting issues with a linked sheet
  • Edit a linked object 
  • Link with an icon

Sharing Data: Picture/Graphic Object

  • Insert a sheet as a picture
  • Format a picture
  • Insert a graphic object
  • Edit a graphic object

Document Properties

  • Find file size in Windows Explorer/File Explorer
  • Find Document Properties
  • Compare file sizes of file variations

Skills in Exercises

Excel 5-1 Theater Tickets

  • Delete columns
  • Repair formatting
  • Apply conditional formatting
  • Create a comment
  • Display comment on sheet

 

Excel 5-2 Soccer Budget - IF

  • Insert an image
  • Apply conditional formatting
  • Copy conditional formatting
  • Create IF functions

Excel 5-3 Soccer Budget - What If

  • Create a What-If sheet based on the existing sheet
  • Balance the budget by changing income and expenses

Excel 5-4 Computers Today - Share Data

  • Insert Excel charts into a Word document

Excel 5-5 Theater Ticket - Subtotals

  • Use the Planning checklist
  • Sort
  • Subtotal
  • Create formulas

 

Important Terms

absolute reference

argument

assumed values

AutoCalculate

AutoSum

collapse group

comment

comment indicator

concatenation 

conditional formatting

convert

data validation

documentation

edit in place

embedded object

expand group

Goal Seek

group

icon link

IF function 

import filter

input message

link

linked object

logical comparison or test

named cell

picture object

pivot chart

pivot table

Print as displayed

Print at end

Sort

Split

Subtotal

Ungroup

update

value

What If sheet