Excel Tools Course
Or log in to access your purchased courses
In this course you will learn about tools available in Excel to make your job easier. This is more than just an Excel 2 course, as it covers the practical uses of Excel at this level.
The secret to success with Excel is application. Apply what you learn as you go along.
Modules
Downloads and Course introduction
Ultimate Excel Tool
Lessons
Tips and Tricks- Intermediate
Lessons
Filtering and Sorting
Lessons
The Specials- GOTO and Paste
Lessons
- GOTO Special- find comments, values, formulas and more
- Find errors, conditional formatting and data validation
- Copy Visible Cells Only
- Fill in all the blanks
- GoTo Special Examples
- Paste Special- Values, Formulas with/without formatting
- Paste Special- convert negatives to positives Free Preview
- Switch rows to columns and columns to rows
- Paste without overwriting blank cells
- Re Use the Format Painter
Tools for text
Lessons
- Find Replace- standard uses
- Find Replace- Change formatting on many cells
- Remove between brackets or characters
- Find Replace Examples
- Single Column to Multiple Columns
- Text to Columns Examples
- Import CSV’s into the correct columns
- Control what is entered in an Excel cell- Data Validation
- Tables- Critical for Excel use going forward
- Change text across many sheets at the same time
Pivot Tables
Lessons
- Required data format for Pivot tables
- Create a Pivot Table- Step by Step
- Inserting Values into a Pivot Table
- Enhancing the values e.g. percentage of another column
- Show the details of a number in the Pivot Table
- Pivot Examples: SUM, COUNT, AVERAGE, Percentage of
- Laying out the Pivot Report in completely separate columns
- Working with the rows in a Pivot Table- dropdown
- Working with the rows in a Pivot Table- field settings
- Normalize Pivot Reports to use elsewhere as a copy paste
- Pivot Examples: rows in a pivot table
- Working with the Columns in a Pivot Table
- Pivot Examples: values, rows and columns
- Working with the Filter in a Pivot Table
- Automatically duplicate your Pivot Tables with different selections
- Slicers in a Pivot Table
- Control all Pivot Tables and Charts at once
- Timelines in a Pivot Table
- Grouping Dates into days, months, quarters and years in a Pivot Table
- Grouping Dates into weeks in a Pivot Table
- Grouping Text in a Pivot Table
- Grouping Numbers in a Pivot Table
- Pivot Charts in Excel
- Problems with grouping in Pivot Tables
- Pivot Examples: All features learnt so far
- Pivot Examples: Business Intelligence
Conditional Formatting
Lessons
- Change cell colour based on value
- Change color based on benchmark in another cell
- Highlight all above average cells
- Add comparative bars into cells
- Create heat maps in Excel
- Add risk robots, milestone, up and down icons to Excel cells
- Control the heat maps, icons and data bars in conditional formatting
- Conditional Format Examples
Function and Formula Basics
Lessons
Text Functions
Lessons
Date functions and issues
Lessons
Charting In Excel
Lessons
- Excel Charting introduction
- Create a chart
- Align chart to the grid lines
- Chart doesn’t look right? First thing to try
- Customise the chart title, area, legend and more
- Vertical Axis- force the scale, reverse the order, labels and more
- Horizontal Axis- dates vs text, reverse order, show all labels
- Series- overlapping, big and small series, gaps
- Chart Examples- Look and Feel
- Add more series to the chart- 3 ways
- Show big and small numbers on the same chart (and be able to see them)
- Show months, quarters AND years on the chart axis
- Chart Examples- Series and Axis
- Bar Charts
- Pie Charts
- Area Charts
- Radar Charts (to compare projects?)
- Chart Examples- Line, column, bar, pie, radar,area
- Show mini trend charts for large reports
- Sparklines Exercise
- Paste a linked picture of data onto a chart (or anywhere)
- Copy the format of one chart to another chart
- Combine a column with a line chart (or other combinations)
- Add linked commentary directly to the chart
- Add commentary to the axis
- Add commentary to labels and call outs
- Add commentary to labels for older versions of Excel
- Chart Examples 3- Add Commentary
- Change the default colour scheme in charts
- Add a trendline to a chart
- Link Excel chart to PowerPoint or Word
Protecting spreadsheet information and formulas
Lessons
- How to protect an Excel Spreadsheet from opening
- Allow Spreadsheet to be viewed but not changed (read only)
- Protect only certain cells/ formula from everything except input changes
- Lock cells but allow colour formatting or comments
- Lock Cells but allow insert or delete of columns/ rows
- Lock Cells but allow column/ row size change
- Protect cells but allow filter/ pivot table/ sort
- Hide and protect a sheet in a spreadsheet
- Protect cells with different passwords to allow levels of authority
- Protection Examples
Avoiding Errors
Lessons
