1 / 22

EXCEL INTERMEDIATE

Learn advanced Excel functions such as cell formatting, functions like IF and PMT, managing cell references, and error messages. Discover how to view/print formulas, create charts, insert graphics, and utilize features like Freeze Panes and Paste Link.

sanjiv
Download Presentation

EXCEL INTERMEDIATE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. FUNCTIONS • Autosum • =today() • =now() • =min() • =max() • =median() • =Average() • =Round() • =Countif() • =pmt() • =if() • Dragging formulas

  5. 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

  6. Cell References • Relative cell references • Default • Automatically changecell references relative to which column/row you copy it to • Absolute cell references • 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

  7. 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.

  8. “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) • FV

  9. 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)

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

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

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

  13. 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

  14. 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

  15. 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

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

  17. 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

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

  19. 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

  20. 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

  21. PASTE LINK • Insert data saved to the clipboard so that the inserted data will change if 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

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

More Related