Excel Tools Course
Excel Tools Course
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.
- Time Req – 12 hrs
- Skill Score >3
- FAQ
- All Online Courses
- Live Course Options
- Ask a Question
The secret to success with Excel is application. Apply what you learn as you go along.
Modules
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
