Excel Intermediate 2013

Overview

This course contains the following 11 modules on the following:

3D Workbooks: 

  • Double-clicking on a sheet tab allows you to give it a descriptive name. You can change the order of the worksheets by dragging their tabs.
  • To copy worksheets press the Ctrl key as you drag the tab. To select the same cells in consecutive worksheets, select the cells on the first worksheet, then press the Shift key and click on the tab of the last worksheet you require.
  • To select the same cells in worksheets that are not consecutive, press the Ctrl key as you select the tabs. When using formulas from different worksheets you need to specify which worksheet the data is in, as well as the cell address.
  • When the sheet name has a space, the whole name is enclosed in single quotes.

Additional Formatting: 

  • Hash characters are displayed in a cell when there is insufficient room to display its data.
  • To use the best fit method for columns, double-click on its right column intersection inside the column heading.
  • Applying built-in or custom Styles enables you to keep a consistent look to your worksheets.
  • Formatting and formulas can be extended as you add to the data in your worksheet. Formatting is removable.

Creating a Web Page: 

  • The Internet is a global network of interconnected computer systems and smaller networks.
  • A Web browser allows you to exchange Excel workbooks with others over the Web, without using Excel. To check a workbook's appearance in a browser you use the Web Page Preview command.
  • Web pages are created in a language called HTML (Hypertext Markup language).
  • Excel workbook components can be saved as a Web page using the Save as Web Page command.
  • A Hyperlink enables you to jump from the current worksheet to another location such as a file or Web page.

Creating Charts: 

  • You create charts by selecting the data and clicking on the command for the type of chart you want from the Chart Group on the Insert tab.
  • The chart can be either embedded on the active worksheet or located on a separate worksheet.
  • Changes can only be made to a chart when it is activated and displays selection handles around its edges.
  • You can re-size a chart by dragging its selection handles. Use the Chart Type button to change to a different chart type.
  • To print the chart on its own page, select it before using the Print command.
  • The Range Finder automatically outlines the ranges that have been applied to the chart Data.

Formatting Charts: 

  • A chart is made up of individual elements that you can format, re-size, move and edit.
  • You can format a chart object by using the Format command on its Shortcut menu or by double-clicking on it.
  • To remove a chart object you select it, then press the Delete key or use the Clear command on the Shortcut menu.
  • Selected objects can be moved by dragging them to a new location.

Formatting Menu Options: 

  • Both data and/or formatting can be removed in cells by using the Clear command in the Editing group on the Home tab.
  • The Format command from the Cells group offers the complete range of formatting options.
  • To rotate and/or vertically centre data in a cell, use the alignment options from the Format Cells box.
  • A quick way of opening the Format Cells box is to use the Ctrl 1 shortcut keys.
  • Borders and patterns can be added to improve the visual appeal and Fonts can be formatted in a variety of ways.
  • Text entries in cells can be indented 

Relative & Absolute References: 

  • There are two main references used within Excel formulas, which are Relative and Absolute.
  • Excel uses relative references as the default cell reference. When a formula is copied or moved to another location, this type of cell reference changes.
  • Absolute references refer to a cell in a specific location. Its reference does not change if the formula is copied to another cell. You apply an absolute address to a formula using the F4 key. A dollar sign ($) signifies that an address is absolute.

Splitting, Freezing & Hiding: 

  • The Zoom button enables you to view the worksheet in different magnifications.
  • The Full Screen command enables you to view more of the worksheet on screen.
  • To hide rows and columns select the ones you want, and then use the Hide command on the shortcut menu.
  • The Split command splits the window into panes at the active cell allowing you to view different parts of the worksheet at the one time.
  • To freeze headings in the worksheet, use the Freeze Panes command

Using Functions: 

  • Functions perform various calculations by using specific values, known as arguments.
  • To quickly display a list of functions, and their respective descriptions, use the Insert Function button.
  • To be able to use the current date and time in calculations, use the NOW function.
  • The IF function, which enables you to evaluate if a condition in a worksheet is true or false, produces different results depending upon the true/false value of the condition.
  • The PMT function enables you to work out any periodic payments, such as a loan or mortgage repayment.

Worksheet Design: 

  • The Styles menu offers a wide range of formatting choices.
  • Excel provides a selection of ready defined themes for colour, font, size etc.
  • Use the commands on the Insert tab to insert illustrations into the worksheet.
  • Format your pictures to change size, shape frames and apply preset effects.

Worksheets and Email: 

  • A workbook or worksheet can be sent as an Email message directly from Excel. An entire workbook can be sent as a message attachment whereas a worksheet can be sent as the body of a message.
  • To Email the active workbook or worksheet you use the Send as Attachment command in the Saveand Send options in the Backstage area.
  • Copies of the email can be sent as carbon copies or blind carbon copies. Emails can be marked to alert the recipient to their importance.

Objectives

3D Workbooks: 

  • Name worksheet tabs
  • Rearrange worksheets
  • Copy between worksheets
  • Select 3-D ranges
  • Group worksheets
  • Use multi-worksheet formulas

Additional Formatting: 

  • Insert Columns and Rows 
  • Delete Columns and Rows 
  • Adjust Column Widths 
  • Change Row Heights 
  • Undo and Redo Actions 
  • Autoformat a Worksheet 
  • Create and Apply Styles 
  • Extend Formatand Formulas 
  • Remove formatting

Creating a Web Page: 

  • Identify internet components 
  • Use web page preview 
  • Save as a web page
  • Use a web browser 
  • Edit web pages 
  • Create a hyperlink
  • Activate a hyperlink

Creating Charts: 

  • Identify the function of charts
  • Create charts 
  • Select the data range 
  • Resizeand Move a chart 
  • Change the chart type 
  • Select chart objects 
  • Edit a chart 
  • Preview and print a chart
  • Save or delete a chart
  • Use the range finder

Course Duration

6 Hours

3D Workbooks: 40 Minutes
Additional Formatting: 40 Minutes
Creating a Web Page: 40 Minutes
Creating Charts: 40 Minutes
Formatting Charts: 35 Minutes
Formatting Menu Options: 40 Minutes
Relative & Absolute References: 20 Minutes
Splitting, Freezing & Hiding: 30 Minutes
Using Functions: 30 Minutes
Worksheet Design: 30 Minutes
Worksheets and Email: 20 Minutes