|
1
|
Conventions, formulas and other essentials
|
|
|
Part 1a – Efficient operation
|
|
|
Set up Excel optimally
|
|
|
Use good technique to operate Excel efficiently
|
|
|
-
|
For regularly-used commands, use the keyboard
|
|
-
|
Set up a somewhat standardised Quick Access Toolbar
|
|
-
|
Access files and folders efficiently
|
|
-
|
Understand and use the different Excel file types
|
|
-
|
Backups and recovery
|
|
-
|
Navigate quickly
|
|
-
|
Manage multiple windows
|
|
-
|
Manipulate data
|
|
|
|
|
Part 1b – Formats
|
|
|
Create custom number formats
|
|
|
Format quickly and consistently with Styles
|
|
|
Learn formatting tips
|
|
|
- |
Avoid merged cells |
|
-
|
Avoid hidden rows and columns
|
|
-
|
Make use of Outlines
|
|
|
Part 1c – Conventions
|
|
|
Understand the need for standards
|
|
|
Apply conventions for the layout of a sheet
|
|
|
-
|
Discover the benefits of range names
|
|
-
|
Apply sheet structure and formatting conventions
|
|
|
|
Apply conventions to headings
|
|
|
Minimize borders, include gutters and apply conventions to totals
|
|
|
Apply conventions to alignment, input cell colour, underline and case
|
|
|
Document cells
|
|
|
Apply conventions to print
|
|
|
|
Apply conventions for the layout of sheets within a workbook
|
|
|
-
|
Structure the sheets of a workbook for clarity
|
|
-
|
Create a Params sheet for settings
|
|
-
|
Document for ease of use and maintenance
|
|
-
|
Add Lists and Welcome sheets if needed
|
|
-
|
Include a Menu sheet when there are many sheets
|
|
-
|
Arrange each section per sheet, name sheets and synchronise
|
|
|
|
Apply conventions to manage files
|
|
|
Part 2a – Formulas
|
|
|
Learn important aspects of formulas
|
|
|
-
|
Understand calculation precedence, number precision and its issues
|
|
-
|
Be aware of dangers with calculation mode, bug and circular references
|
|
-
|
Be aware of causes of slow calculation
|
|
-
|
Design formulas to be robust
|
|
-
|
Discover array formulas
|
|
-
|
Know how to create robust file-links
|
|
-
|
Understand the meaning of formulas error types
|
|
-
|
Check and trace formulas
|
|
-
|
Discover issues with numbers as text and convert
|
|
-
|
Set the formula warning messages as appropriate
|
|
-
|
Make formulas as clear as possible
|
|
-
|
Understand the pros and cons of custom functions
|
|
|
|
Master conditional logic with IF and Boolean formulas
|
|
|
-
|
Learn IF, AND, OR, NOT, IFNA, IFERROR and IS functions
|
|
-
|
Shorten formulas with Boolean logic
|
|
-
|
Use MIN and MAX as short alternatives to Ifs
|
|
-
|
Use N to avoid #VALUE!
|
|
|
|
|
-
|
Use SUBTOTAL to avoid omissions and double-counting
|
|
-
|
Use INDEX for current month and year-to-date formulas
|
|
-
|
Sum absolute and largest values
|
|
-
|
Calculate with SUMPRODUCT
|
|
-
|
Learn all about SUMIF, SUMIFS, COUNTIFS, DSUM and other D-functions
|
|
-
|
Create a cross-tabulation report
|
|
|
|
|
-
|
Understand VLOOKUP, INDEX, MATCH, the issues and solutions
|
|
-
|
Make lookups robust
|
|
-
|
Create faster lookups
|
|
|
|
Calculate with dates and times
|
|
|
-
|
Calculate service years & months
|
|
-
|
Accurately calculate the age of an asset in months
|
|
-
|
Calculate time differences and totals
|
|
-
|
Use YEAR, MONTH, DAY, DATE, DATEDIF, NETWORKDAYS and other date functions
|
|
|
|
|
-
|
Concatenate and split
|
|
-
|
Remove spaces and substitute unwanted characters
|
|
-
|
Change case
|
|
-
|
Use LEFT, RIGHT, MID, TRIM, TEXT, SUBSTITUTE, VALUE, PROPER, SEARCH and more
|
|
|
|
|
-
|
Group data with the FREQUENCY function
|
|
-
|
Count unique values
|
|
-
|
Calculate a TREND and determine the goodness of fit
|
|
|
|
Learn accounting formulas
|
|
|
-
|
Create a formula that pays every third month
|
|
-
|
Cash flow forecasting of sales, debtors, purchases and creditors
|
|
|
|
Calculate cash from a constant debtor days across months
|
|
|
Calculate cash from a debtor days figure that varies by month
|
|
|
Calculate receipts with a formula that works for any number of debtor days
|
|
|
Incorporate actuals in a cash flow model
|
|
|
Calculate a debtor (or creditor) days value
|
|
|
Calculate cash from sales using a debtors profile
|
|
|
Calculate cash from sales using SUMPRODUCT & TRANSPOSE
|
|
|
Create a rolling cash flow forecast
|
|
|
Create rolling month titles
|
|
|
-
|
Calculate debtor days
|
|
-
|
Learn depreciation calculations
|
|
-
|
Calculate with loans
|
|
|
|
Calculate loan payment and interest
|
|
|
Convert nominal & effective interest rates
|
|
|
Part 3a – Tables, filtering, import, export and consolidation
|
|
|
Make spreadsheets more reliable by creating Tables and referring to them
|
|
|
-
|
Include Table references in formulas
|
|
-
|
Source PivotTables, Validation Lists and Charts from Tables
|
|
-
|
Build crosschecks into charts
|
|
-
|
Set the Print Area to self-adjust
|
|
-
|
Apply the ESPTable style
|
|
-
|
Enter data into a Table with a Form
|
|
-
|
Apply advanced filters
|
|
|
|
Make Table formulas reliable by using Table references
|
|
|
-
|
Duplicate Table formulas correctly
|
|
-
|
Create running totals, row-by-row differences and row index numbers
|
|
|
|
|
-
|
Sort and Filter by colour and icons.
|
|
-
|
Sort into non-alphabetical order (such as month names)
|
|
-
|
Discover Advanced Filter and any criteria you require
|
|
-
|
Create a unique list
|
|
-
|
Remove unwanted records from imported print files
|
|
|
|
|
|
|
|
Use the Text Import Wizard
|
|
|
Keep settings for a repeat import
|
|
|
Solve import problems
|
|
|
-
|
Extract directly from source
|
|
|
|
Download selected records with Query Wizard or MS Query
|
|
|
Join tables in MS Query
|
|
|
Append columns in Excel
|
|
|
|
|
-
|
Export CSV and fixed-width text files
|
|
|
|
|
-
|
SUM through sheets and synchronise
|
|
-
|
Consolidate with Data Consolidate
|
|
-
|
Create a consolidation PivotTable from multiple data ranges
|
|
-
|
Append tables and build a regular PivotTable
|
|
|
Part 3b – Charts, Conditional Formatting and PivotTables
|
|
|
|
|
|
-
|
Create and format a simple Column chart
|
|
-
|
Alter a chart to suit your needs
|
|
-
|
Create Column, Bar, Line and Pie charts
|
|
-
|
Combine chart types
|
|
-
|
Create a Waterfall chart
|
|
|
|
|
-
|
Determine whether a chart, table or both is the best way to communicate
|
|
-
|
Choose the appropriate chart type
|
|
-
|
Generally, be monochromatic, but for many series, use soft colours
|
|
-
|
Remove distractions |
|
|
|
Apply Conditional Formatting |
|
|
-
|
Format cells based on their values
|
|
-
|
Set formula-based criteria
|
|
|
|
Format entire rows based on the value of a single cell
|
|
|
Highlight cells based on the sum of other cells
|
|
|
Shade alternate rows
|
|
|
-
|
Apply icon sets
|
|
-
|
Highlight KPIs with Data Bars or traffic light icons
|
|
-
|
Shade duplicates
|
|
-
|
Shade actual months
|
|
|
|
Build PivotTables reports |
|
|
-
|
Create and format a PivotTable report
|
|
-
|
Refresh and rearrange
|
|
-
|
Show percentages of column total
|
|
-
|
Display only certain countries and remove total
|
|
-
|
Drill down to underlying data
|
|
-
|
Chart a headcount report
|
|
|
Part 3c – Collaboration, reconciliation, protection, crosschecks, errors, data validation, testing and troubleshooting
|
|
|
|
-
|
Compare collaboration approaches
|
|
-
|
Share on a Network server without using Shared workbook
|
|
-
|
Use Shared workbook
|
|
-
|
Master basic Cloud: Sign up, share folder, use Excel Online |
|
-
|
Master advanced Cloud: Compare methods – Excel Online, Excel2013/16, Download
|
|
|
|
|
-
|
Line up for visual comparison
|
|
-
|
Remove duplicates
|
|
-
|
Compare by using formulas
|
|
|
|
|
-
|
Protect some or all of a workbook from change
|
|
-
|
Provide password entry to certain ranges
|
|
-
|
Hide sensitive data
|
|
|
|
|
-
|
Allow only certain entries
|
|
-
|
Create conditional drop-down list selections
|
|
-
|
Check the validations set-up
|
|
|
|
|
-
|
Avoid many kinds of errors by inclusion of crosschecks
|
|
-
|
Understand the issues in creating effective crosschecks
|
|
-
|
Apply conditional formatting to make the errors stand out and not be overlooked
|
|
-
|
Create crosschecks areas and a crosschecks sheet for ease of checking
|
|
|
|
Know common errors and how to avoid them |
|
|
-
|
12 common errors and the solutions
|
|
|
|
|
-
|
Use patterns to quickly spot errors
|
|
-
|
Reset inputs to zero |
|
-
|
Clear all data in order to test one aspect and then reinstate the data
|
|
|
|
|
-
|
Clear deadwood from overly-large files
|
|
-
|
Find the causes of Excel slowing down
|
|
-
|
Deal with Out of memory or not enough resources message
|
|
-
|
Fix problems with Comments, Conditional Formatting, Corrupted Files, Excel crashing |
|
|
Part 4a – Macros
|
|
|
Create a simple macro for a repetitive task |
|
|
Record and edit a print macro |
|
|
Automate an import application |
|
|
Prompt for input and check for valid entry |
|
|
Loop to delete records |
|
|
Understand objects, properties, and methods |
|
|
Create custom functions |
|
|
Create a custom dialog box |
|
|
Use error-trapping to prevent crashes for anticipated errors |
|
|
Send an email that has an attached spreadsheet |
|
|
Part 5a – Applications
|
|
|
Import trial balance and report |
|
|
Consolidate business unit actuals from report downloads |
|
|
Categorise imported records on multiple-column criteria and create reports |
|
|
Maintain a fixed assets register |
|
|
Create a weekly cashflow application |
|
|
Build a multi-unit import, translate, adjust and report applications |
|
|
|