Advanced formatting
-
A recap of the Format Cells dialog box – number format, alignment, font, borders and shading.
-
Conditional formatting
-
Using the Format Painter
-
Changing the worksheet background
-
Using styles to improve consistency and maintainability
Paste Special
- Understanding the different layers of formatting
- Pasting contents without formatting
- Using Paste Special to consolidate data
- Transposing data
- Creating a link between source and
destination data
Relative and Absolute cell addressing
-
Using AutoFill and the AutoFill smart tag
-
What is relative and absolute cell addressing?
-
Using dollar signs to cement rows and columns
-
F4 - The magic key
-
Partial vs full absolute addressing
Naming cells and ranges
- Naming a cell using the Name box
- Naming a cell using the Define Name
- dialog box
- Creating a constant
- Using named cells for navigation
- Using named cells within a formula
- Modifying and deleting named cells
- Creating names from existing headings
- Applying names to existing formulas
- Displaying a list of named cells
Data validation
- Setting the rules that must be met
- Giving instructions to the user
- Alerting the user of an error
- Conveying the severity of invalid data
- Highlighting all invalid data on a worksheet
Advanced Find and Replace techniques
-
Overview of Find & Replace
-
Specifying where to search
-
Case-sensitive searches
-
Searching for and replacing a format, rather than a word
-
Using AutoCorrect
-
Using the Go-To-Special feature
Protecting a worksheet and a workbook
Working with Excel databases
- Entering data using AutoComplete
- Entering data using Pick From List
- Entering data using a Data Form
- Filtering data using AutoFilter
- Filtering data using Advanced Filter
- Sorting rows or columns into order
- Creating and using custom lists
- Applying subtotals to a list
Using Excel functions
- Using AutoCalculate
- Using AutoSum
- Overview of the five basic functions
- Using the Function Wizard
- Exploring the COUNT functions
- Using the PI function
- Using the logical functions
- Exploring the Date and Time functions
- Exploring functions that manipulate numbers
- Exploring Combination functions
|
Hyperlinks
- Creating, editing and removing hyperlinks
- Linking to another file
- Linking to another place in the same workbook
- Linking to a web page
- Linking to a new e-mail message
Templates
- Workbook and worksheet templates
- Saving, modifying and deleting a template
- Creating a new workbook based on a template
Charts
- Creating a chart using the Chart Wizard
- Modifying a chart
- Changing the chart type
- Changing the source of the data
- Setting the chart options
- Showing or hiding different chart components
- Changing the chart location
- Selecting and formatting a chart component
- Applying a gradient, texture, pattern or picture fill to a chart component
- Manipulating 3D charts
- Using the Chart toolbar
- Creating and saving custom chart types
- Previewing and saving a chart as a web page
Data Outlines
- Creating an outline manually or automatically
- Collapsing and expanding an outline
- Removing one or all outlines
- Expanding the range of an outline
- Using outlines with charts
- Outline styles
Linking worksheets and workbooks
- Referencing cells within other worksheets and workbooks
- Viewing and updating links manually
- Updating links automatically
Comments
- Creating, editing and deleting comments
- Viewing and navigating comments
- Moving and resizing comments
- Changing the default user name
Customising toolbars and menus (Bonus)
- Creating a new toolbar or menu
- Loading and unloading toolbar icons
- Repositioning icons
- Creating sub-menus
- Changing an icon image
- Changing the characteristics of a menu item
- Showing full menus all the time
Pictures (Bonus)
- Using the Picture toolbar
- Converting a picture to B&W, greyscale or washout
- Setting the contrast or brightness
- Cropping and rotating a picture
- Placing a border around a picture
- Compressing a picture
- Formatting the picture
- Setting a colour to be transparent
- Resetting a picture to it's original state
Using the Drawing toolbar (Bonus)
- Drawing lines, arrows, rectangles, squares, circles, ovals and autoshapes
- Changing the line, fill and font colour
- Making an object three dimensional
- Adding shadow to an object
- Adding text to an object
- Insert a clipart or picture
- Selecting, aligning, spacing, grouping, layering, rotating and flipping objects
- Using WordArt
- Creating an Organisation Chart
- Creating Cycle, Radial, Pyramid, Venn and Target diagrams
|
