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
Recommend
More recommend
