Excel Beginners 2013

Overview

This course contains the following 13 modules on the following:

Introducing: 

  • Excel is a spreadsheet application for organising, analysing, and presenting data. 
  • To open Excel you select Microsoft Excel from the Programs sub-menu.
  • A spreadsheet contains a grid of cells arranged in columns and rows. 
  • The Ribbon displays commonly used commands. 
  • Excel's Help provides information and useful tips. 
  • The shortcut menu offers editing options for a selected object.

Creating Workbooks: 

  • To open a new blank Excel workbook you use the New button.
  • When you enter data, it appears in both the selected cell and the Formula Bar. 
  • Once you confirm an entry, Excel automatically advances the cell pointer to the next cell within the range.
  • To select a range of cells click on the first cell, then hold down Shift and click on the last cell.
  • To select a range of cells that are not connected press the Ctrl key as you click on them.
  • You can select a column or row by clicking on the column or row headings respectively.

Formatting Data: 

  • The Formatting toolbar contains buttons that change the appearance of the worksheet.
  • To change the way numbers in a worksheet are displayed use the numeric formatting buttons. 
  • Excel automatically adjusts the height of rows to accommodate the data they contain. 
  • You can align data in cells using the alignment buttons.
  • To centre data across a selected range of columns use the Merge and Centre button.
  • The Format Painter applies existing formats to other cells in the worksheet.

Formulas and Functions: 

  • A formula is a cell entry that calculates data. Functions are ready made formulas that perform specialised calculations automatically.
  • Using the AutoSum button is the easiest way to add values.
  • When you copy formulas, Excel automatically changes the relative cell addresses to reflect their new location.
  • Instead of using cell references in formulas, you can use row and column labels. You type the row name, followed by a space, then the column name. To use this feature you first select the Accept label in formulas option.
  • Formulas automatically update results when values change because they refer to cell references, not the values they contain.
  • Excel identifies errors in formulas and helps you to correct them.

Move and Copy Data: 

  • The Shortcut menu is an alternative way of working with Excel. It is displayed by right-clicking on the required object.
  • When using the Cut or Copy commands, the Windows Clipboard is used as a temporary storage area for the data.
  • The Collect and Paste feature, which enables you to hold up to twenty four items in the Office Clipboard, can be activated by displaying the Clipboard toolbar.
  • You can move data within a worksheet or between workbooks by using the drag and drop method.
  • To display all open workbooks on the screen, use the Arrange command on the Windows menu.

Navigating Workbooks: 

  • To move quickly to other cells in the worksheet use the Scroll bars.
  • Using the Ctrl End shortcut keys moves you to the last entry in a worksheet. 
  • Pressing the Ctrl Home keys moves you to the left-hand corner of the worksheet. 
  • To move directly to a specified cell use the Go To command, or the Name box.
  • A fast method of activating Go To is by pressing the F5 key. 
  • Excel allows you to have up to 256 worksheets stored in a file which is known as workbook.
  • To display a worksheet, simply click on its sheet tab.
  • To display the other sheet tabs use the Tab scrolling buttons.

Open and Save Workbooks: 

  • A folder is the Parent of it's sub-folders. New folders can be created by using the New Folder button when in the desired location.
  • Recently opened files can be quickly accessed via the Recent Documents panel in the Backstage view.
  • File names can be up to 255 characters and may contain spaces.
  • The Save As command makes a copy of an existing workbook file in formats that other applications can use.
  • Closing documents after you are finished with them can reduce confusion and increase computer performance.

Page Breaks and Previewing: 

  • Print Preview allows you to view how the worksheet appears when printed.
  • To see the page in greater detail you use the Zoom button.
  • You cannot edit the worksheet while in Preview mode.
  • Page Break Preview lets you adjust page breaks by dragging them.
  • Excel automatically scales the information to fit the printed page.
  • To insert manual page breaks use the Break command in the Page Setup group in the Page Layout tab.
  • To delete manual page breaks use the Remove Page Break command.

Page Setup Options: 

  • The Header/Footer options are used when you require the same data printed on the top or bottom of every page in the worksheet.
  • You can choose from Excel's built-in headers or footers or customise your own.
  • The Orientation box, in the Page section, allows you to select whether you wish to print down the page (Portrait), or across the page (Landscape).
  • The Scaling options allow you to shrink or enlarge the data on the printed page.
  • The Print titles options within the Sheet section are used to repeat column and row titles on every page of the printed worksheet.
  • The Margins section enables you to centre the worksheet horizontally or vertically and change the blank area left around the outside of the printed page.

Printing: 

  • When printing a section of the worksheet regularly, you can set a current print area using the Print Area/Set Print Area command.
  • Setting a new print area will override the previous one as you can only have one print area set at a time.
  • A quick way to print the active worksheet(s) is to use the Print button.
  • You can control how the worksheet is printed by using the Print command on the File tab.
  • To select another printer use the Name box.
  • Always preview your worksheet before printing it.

Proofing Tools: 

  • The Spell Checker feature checks the worksheet for typing errors. It compares the words in your document against its own dictionary.
  • AutoComplete enables fast entry of repetitive text within one column.
  • You can enter your common typos for automatic correction as you work by using AutoCorrect.
  • Use the Find and Replace commands to locate text within a worksheet.
  • Be aware of the balance between searching for part of a word, and searching for the whole word.
  • The AutoCalculate feature offers statistical functions other than Sum.
  • You can produce an Average, Maximum, or Minimum value, as well as a Count of a range of selected cells.

Screen Components: 

  • The Ribbon presents commands in groups in tabs for easy access.
  • Modify the Quick Access toolbar to display your most frequently used commands.
  • Learn to alter the view or layout for more successful editing.
  • Use and understand the keyboard shortcuts and Tool Tips for faster working.
  • Make the most of the information on the Status bar and the Taskbar.
  • Recognise and appreciate the different Mouse pointers.

Working with Ranges: 

  • Naming cells allows you to refer to them by name instead of by cell reference.
  • You can name a cell or range of cells by using the Name Box on the Formula bar or the Ctrl F3 shortcut keys.
  • To move directly to a named range you can use the Go To feature or the Name Box list.
  • The Range Finder uses colours to identify the different components of a formula, and their corresponding cells in the worksheet.
  • Range Finder can be activated by clicking at the end of the entry on the Formula bar or double-clicking on the cell that contains the formula.

Objectives

Introducing: 

  • Identify the Function of Excel 
  • Start Excel 
  • Understand Workbook layout 
  • Understand Ribbon layout 
  • Open Help 
  • Use the Shortcut Menu 

Creating Workbooks: 

  • Create a New Workbook 
  • Use Values and Labels
  • Input Dates 
  • Enter Data into a Range 
  • Edit Data 
  • Select Data Ranges
  • Select Workbook Components
  • De-select Cells

Formatting Data: 

  • Change numeric formatting 
  • Set decimal places 
  • Modify font, size and colour
  • Centre text across columns
  • Align data in cells 
  • Apply bold, italic and underline
  • Add borders 
  • Change background colour 
  • Use the format painter feature

Formulas and Functions: 

  • Use Formulas and Functions 
  • Use the AutoSum Button 
  • Copy Formulas 
  • Copy Functions 
  • Use Labels to Enter Formulas 
  • Apply the AutoFill Feature 
  • Update Formula results
  • Manage Errors 
  • Manage Circular references

Move and Copy Data: 

  • Differentiate Cut and Copy
  • Move Text using the Toolbar
  • Copy using the Shortcut Menu
  • Use Paste Special Feature
  • Paste Multiple Items
  • View Multiple Documents 
  • Drag and Drop Text

Navigating Workbooks: 

  • Scroll through a Worksheet 
  • Activate the Go To Command 
  • Work with Sheet Tabs 
  • Use Tab Scrolling Buttons

Open and Save Workbooks: 

  • Open an existing workbook
  • Navigate drives and folders
  • Rename or delete a folder 
  • Use recently used workbooks 
  • Create a Workbook 
  • Move between workbooks
  • Use the Save As command 
  • Convert file formats
  • Close a workbook

Page Breaks and Previewing: 

  • Print Preview worksheets
  • Adjust Margins
  • Apply Page Break Preview 
  • Insert Page Breaks 
  • Remove Page Breaks

Page Setup Options: 

  • Use Headers and Footers 
  • Control page order 
  • Print column and row titles 
  • Change page orientation 
  • Scale the worksheet 
  • Adjust margins 
  • Centre a worksheet 

Printing: 

  • Set a Print Area
  • Clear a Print Area 
  • Print a Worksheet 
  • Print Multiple Worksheets 
  • Apply Print Options 
  • Select a Printer 
  • Change Print Properties 
  • Print Preview

Proofing Tools: 

  • Check Worksheet Spelling
  • Use AutoComplete 
  • Use AutoCorrect 
  • Find and Replace Data 
  • Work with AutoCalculate

Screen Components: 

  • Navigate the Ribbon
  • Use the Quick Access Toolbar
  • Change to Full Screen view 
  • Use Tool Tips and Shortcuts 
  • Use Page Layout 
  • Understand workbook layout 
  • Understand worksheet layout 
  • Hide and restore the Taskbar 
  • Understand Excel pointers

Working with Ranges: 

  • Name Cells 
  • Name Ranges 
  • Use the Go To Box 
  • Use the Name Box 
  • Use the Range Finder

Course Duration

7 Hours

Introducing: 20 Minutes
Creating Workbooks: 35 Minutes
Formatting Data: 30 Minutes
Formulas and Functions: 40 Minutes
Move and Copy Data: 35 Minutes
Navigating Workbooks: 40 Minutes
Open and Save Workbooks: 45 Minutes
Page Breaks and Previewing: 20 Minutes
Page Setup Options: 35 Minutes
Printing: 40 Minutes
Proofing Tools: 40 Minutes
Screen Components: 30 Minutes
Working with Ranges: 20 Minutes