1. EXCEL INTERMEDIATE

2. WORKSHEETS • Worksheet Tabs • Rename by double clicking • Can be moved by click and drag • Change colour by right click and choose Tab Color • Grouping worksheets by clicking ctrl and tab • Allows formatting and formula insertion in multiple sheets simultaneously • Refer to formulas on another sheet by beginning to type formula on current sheet, then click on sheet tab, and select cell on new sheet • =B5-Sheet2!C3 • =J27*’Quarter1’F17

3. CELL FORMATTING • Clear Cells • Home tab, Edit group, Clear button • Text Wrapping • Right click, Format Cells,Alignment tab, Wrap text box • Text Rotation • Right click, Format Cells,Alignment tab, drag “Text”

4. LINKING FORMULAS • If on a “Summary Sheet”, you would like to include a cell from a different sheet: • Click in the cell on the “Summary Sheet”, where you want the answerto appear • Key in the equals sign= • Then navigate to the sheet tab, you want to reference, and click on the specific cell you want • Press “ENTER” • In the myitlab Grader assignment —this is what question # 10, is referring to

5. FUNCTIONS • Autosum • =today() • Can also enter CTRL + ; • =now() • Try format as # • =min() • =max() • =median() • Dragging formulas

6. FIND TOTAL “WORKING DAYS” BETWEEN ANY 2DATES, INCLUDING “HOLIDAYS” • Helpful, with project plans, ganttcharts • =networkdays(start date, end date, list of holidays) to get the number of working days. • In the above sample you can see the number of working days between NEW YEARS DAY and SEPTEMBER 01

7. FUNCTIONS cont. • =Round() • =Average() • =Countif() • The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify • range  Required. One or more cells to count • criteria  Required. A number, expression, cell ref, or text that defines which cells will be counted. eg, 32, ">32", B4, "apples", or "32". • =PMT() • =IF()

8. IF FUNCTION • =IF(logical_test,value_if_true,value_if_false) • Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

9. Cell References • Relative cell references • Default • Automatically changecell references relative to which column/row you copy it to • Absolute cell references • F4key • Absolutely will notchange when you copy formula • Mixed cell references • Either ROW or COLUMN will not change depending which one is preceded by a $dollar sign

10. MIXED CELL REFERENCE • A mixed cell reference contains only one dollar sign: =$A1 • the columnpart of the reference (A) is absolute and the row part (1) is relative. =A$1 • the column part of the reference is relative and the row part is absolute.

11. “PROJECTED 1st QUARTER”:Absolute Cell Reference • A formula to calculate: “PROJECTED 1st Quarter Sales” • Because sales are PROJECTED to increase, the projected value should be greater than the current data =B7+(B7*$B$17) • B7 would be the current value • *$B$17 would be the % increase

12. PV • =PV(rate,nper,pmt,pmt,fv) • Returns the PRESENT value of an investment. The present value is the total amount that a series of future payments is worth NOW. • For example, when you borrow money, the loan amount is the present value to the lender. • Unless otherwise stated-the pmt is “0” • Do NOT have to divide INTEREST RATE by 12, or multiply NPER by 12 (yrs.)

13. FV • =FV • (rate,nper,pmt,pv) • Returns the FUTURE value of an investment • Unless otherwise stated-the pmt is “0” • Do NOT have to divide INTEREST RATE by 12, or multiply NPER by 12 (yrs.)

14. ROWS and COLUMNS • Insert • Right click on a cell and choose Insert form pop up menu • Note that functions and formulas update automatically • Freeze Panes • VIEWtab > WINDOWgroup > FREEZE PANES

15. ROWS and COLUMNS • select the rowbelow where you want the row to be frozen • To freeze columns, select the column to the right of where you want the column to be frozen

16. ERROR MESSAGES • #NAME? • i.e. =DIV(C2,B5) • (no such FUNCTION name as “DIV”) • #VALUE! • i.e. =SUM(B5,”H3”) • Cell reference should not be in “quotation marks” • #DIV/0! • If the value in a cell is “0” (no division by zero)

17. VIEW/PRINT FORMULAS • CTRLkey + (to the left of the #1key) • This key combination will toggle to viewing formulas--ONorOFF

18. CHARTS • 3-D pie chart • Resize • CHART TOOLS DESIGNtab > CHART STYLESgroup > CHART STYLESgallery • CHART TOOLS DESIGNtab > CHART LAYOUTgroup > CHART LAYOUTgallery

19. Charts cont. • Change the rotation of a 3D chart: • Select the chart • FORMATtab >CURRENT SELECTION group, click on: FORMAT SELECTION • Choose: 3-D ROTATION -change the “X” & “Y” rotations boxes accordingly

20. Charts cont. • Change font of Category X (horizontal) axis • And Y (vertical) axis • Change other options of X & Y axes: • Select specific axis • Right-mouse click • Select: FORMAT AXIS

21. Charts cont. • If you change the “MAJOR” axis to “fixed”, and then set a specific value: • the value will be the “bottom” value, and other values will be Incremented by that same amt. i.e. 75,000: Values increase by 75,000

22. Adding a title to an axis: • Select the axis • Go to the LAYOUTtab > click on AXIS TITLES drop-down arrow • Select either Horizontal or Vertical & then the location of The title

23. Format data series • Right-mouse click on: data series • Select: FORMAT DATA SERIES • Change desires options i.e. FILL

24. LINE CHART • SWITCH LEGEND INFO TO HORIZONTAL AXIS • Select chart > DESIGNtab > DATAgroup • Select:SWITCH ROW/COLUMN • CHANGE A CHART TYPE FOR A SERIES: • Right-mouse click on a specific series > Change Series Type Chart

25. INSERTING AND FORMATTING A GRAPHIC SHAPE • LAYOUTtab > INSERTgroup > select: SHAPES

26. PASTE LINK • Insert data saved to the clipboardso that the inserted data will changeif the ORIGINAL data changes. • Warning - for this to work the original and destination files must be kept together • HOMEtab> • CLIPBOARDgroup> • PASTEdrop-down arrow> • PASTE LINK

27. FREE “TIP OF THE WEEK” • Free anti-virus software from Microsoft – Microsoft Security Essentials • http://www.microsoft.com/Security_essentials/