What if Analysis, Charting, and Working with Large Worksheets - PowerPoint PPT Presentation

What if Analysis, Charting, and Working with Large Worksheets Chapter 3

What we will cover  Rotating Text  Using the fill handle to create a series of month names  Copying and pasting

What we will cover  Inserting, and deleting cells ( not a recommended practice )  Formatting numbers using format symbols  Entering and formatting the system date  Using absolute and mixed cell references

What we will cover  Use the IF function  Create a spark line chart  Change spark line chart type  Use format painter to copy a format  Create a cluster chart on another sheet

What we will cover  Use chart filters to show a subset of data in a chart  Change the chart type and style  Reorder sheet tabs  Freeze and unfreeze rows and columns

What we will cover  Do sensitivity analysis (what if analysis)  Use goal seek to answer what if questions  Use Smart Lookup Insight  Find and fix accessibility issues

What we will Cover  Splitting the screen

Autofilling with the Fill Handle  Dragging to the right can be used to create a series of – Names of the days of the week – Numbers – Names of the months of the year

– Names of the days of the week as follows: • Monday, Tuesday, Wednesday, Thursday, and so on – Numbers as follows: • 2, 2, 2, 2, 2 … etc., OR 6, 7, 8, 9 and so on • Names of the months of the year as follows: • September, October, November, and so on

Other Series  1:00:2:00 , 3:00, 4:00, 5:00 …  1 st :2 nd , 3 rd , 4 th , 5 th …  2005:2006 , 2007, 2008, 2009…  Step 1, Step 2, Step 3, Step 4…

Starting Autofill with Two Cells Selected 1-Jan 1-Feb Will result in 1-Jan,1-Feb,1-Mar,1-Apr 2 4 Will result in 2, 4, 6, 8, 10 … -10 -12 Will result in -10, -12, -14, -16

Autofill Options Button  Fill Series with formatting (default) – Fill the destination area with a series using the format of the source area  Fill Formatting Only – Fill destination area using the format of source area. No content is copied unless the fill is a series.  Fill Without Formatting – Fill the destination area with contents, without the formatting of the source area.  Fill Months – Fill the destination area with a series of months using the format of the source area. This shows as an option only if source area contains a month.

Cut, Copy, Paste  Cutting removes the cell contents and places them on the clipboard Pasting involves moving the cell contents from the clipboard to a cell  Copying does just what it sounds like it would do. It copies the cell contents to the clipboard and leaves the original in the source cell(s). You can then paste them to another cell or cells.

Paste Options  Keep Source Formatting (default) - Copy the contents and the format of the source area.  Match Destination Formatting--Copy the contents of the source area, but not the format.  Values and Number Formatting – Copy contents and format of the source area for numbers or formulas , but use the format of the destination area for text .

More Paste Options  Keep Source Column Widths – Copy the contents and the format of the source area. Change the destination column widths so that they are the same as the source column widths.  Formatting Only - Copy format of source area, but not the contents .

Inserting Cells, Rows and Columns  Avoid adding or deleting individual cells because it will affect the rows and columns around them. Use the clear command instead to remove cell content, not the entire cell.  Adding rows is generally not a problem because cell references in the moved rows adjust  Adding columns is generally not a problem because cell references in the moved columns adjust  Be aware of any cells elsewhere in the worksheet that reference cells in a deleted column, row, or cell. They will not show error messages (because they are referring to something that is no longer there).

Review of Relative Cell Addressing Relationships Excel only knows that you want to add the three cells directly above the cell containing the sum function. The relative position of these cells is that they are the three cells directly above. A B C D E F G 1 2 2 2 2 3 1 6 3 3 4 5 4 8 =SUM(B1:B3) 5 6 7

Relative Cell Addresses  Cell addresses without dollar signs ($) will adjust when copied or moved. You have used these before.  Example: C33

Column Letter  To prevent a column letter from changing, type a dollar sign ($) before the letter.  Example: $C33

Row Number  To prevent a row number from changing, type a dollar sign ($) before the number.  Example: C$33

Column and Row  To prevent a column letter and a row number from changing (absolute cell addressing), type a dollar sign ($) before the column letter and the row number.  Example: $C$33

Absolute Cell Addressing Formula in B4 is =B3* $F$1 A B C D E F G Tax rate .07 1 2 Price 10.00 3 Tax 00.70 4 Total 10.70 5 6 7

Copied Formula in B6 is =B5*$F$1 A B C D E F G Tax rate 1 .07 2 Price 5.00 3 price 5.00 4 subtotal 10.00 5 Tax 00.70 6 7 total 10.70

Excel Functions  Functions that merely use a list as the arguments  Functions that have several individual arguments separated by commas (like a fill-in)

Functions that use Lists as arguments  They merely require a list of cells after the name of the function.  Tell Excel the first and last cell address of the cells in the list  Example: =sum(A1:A5)

Functions that use individual arguments  Arguments are pieces of information that Excel needs to perform an operation for you  =IF(ARGUMENT1, ARGUMENT2, ARGUMENT3)  Arguments go inside parentheses  Arguments are separated by commas  Arguments follow the name of the function

IF FUNCTION  Starts with an equal sign (=)  The name of the function is IF  Argument 1 is the condition to be met  Argument 2 is what to do if true (condition is met)  Argument 3 is what to do if false (condition not met)

Arguments 2 and 3  These can be: – Cell references – Text strings inside double quotes – Formulas using cell addresses – Formulas using numbers – Formulas using cell addresses and numbers

Location of If Function  Place the if function in the cell where you want the result to appear

 IF function using text strings =IF(A1>=3.00 , ” Buy a Big Mac ”, ” Eat at home ”)  IF function using cell references =IF(A1>=3.00,A2,A3) A B C 1 3.50 If statement here 2 Buy a Big Mac 3 Eat at home

Logical Operators used in an IF function Operator Meaning Example Means of Operator = Equal to A3=0 A3 is equal to 0 < Less than A3<10 A3 is less than 10 > Greater than A3>10 A3 is more than 10 >= Greater than or equal to A3>=10 A3 is 10 or more <= Less than or equal to A3<=10 A3 is 10 or less <> Not equal to A3<>10 A3 is not equal to 10

More on Spell checking  Excel checks selected worksheets  All cell values  Cell comments  Embedded charts  Text boxes  Headers and footers  To limit checking, you can select a range of cells first — Excel will only spell check those cells

Goal Seeking Personnel January Bonus Gustav Blau $7,000 $700 Bonus % 10% If you wish to increase the amount of the bonus to a specific amount, but the bonus is dependent on the bonus percent , you will have to change the bonus percent. To calculate the correct percent to use in order to make the bonus itself come out to the exact amount you want, you can use goal seek. Example: You want Blau’s bonus to be $1,000. You can use goal seek to tell Excel to figure out what the bonus percent should be to result in a $1,000 bonus for Blau . It will calculate that the percent should be 14.28.

Split Screen split

Reordering Sheet Tabs  You can change the order of the worksheets in your workbook by changing the order of their sheet tabs  Drag the tabs to where you want them

Rotating Text in a Cell Saves Space

Sparkline Chart (chart in a cell)

Do-It-Yourself Number Style Formatting  Number styles that you can either format yourself or format using the number group:  Percent 5.5%*  Accounting or Currency $1,000.00  Comma 1,000.00 * Excel treats this as the decimal equivalent, .055

Freezing Rows and Columns  Causes row(s) to remain in sight at the tops of columns when working farther down the worksheet  Also used to cause column(s) to remain in sight at the left when the user is working far to the right of the worksheet

Before using Freezing

Freezing Rows 1 and 2 at the top and Freezing Column A at the Left

Chart before Filtering

Chart After Filtering