Please select topics from below or suggest other topics you would like covered for an
in-house session for your organisation. Contact us at excelsem@aaa-bsol.com or call
868-788-7538 if you have queries or want a proposal

 

Topic Area # Specific topic Description Time in mins Level of difficulty

Basic Excel

[top]

1 Excel overview - Pt 1 MS Excel program uses, benefits, brief history, versions & user interface 20 Basic
2 Excel overview - Pt 2 Excel file types / formats & structuring / best practice layout of workbooks, worksheets and cells 60 Basic
3 Basic shortcuts Keyboard shortcuts to remember 30 Basic
4 Cells & Cell Ranges Pt 1 Copying, pasting, formatting, aligning, editing& moving cells & ranges 30 Basic
5 Cells & Cell Ranges Pt 2 Merging cells, inserting & deleting rows & columns,  15 Basic
6 Styles Using default & custom cell styles in Excel 15 Basic
7 Sheet actions Pt 1 Copying, pasting, editing, grouping, listing& moving sheets 30 Basic
8 Sheet actions Pt 2 Moving within spreadsheets simple shortcuts 10 Basic
9 Save & Print Saving, page layout & printing options for files 15 Basic
10 The Quick access Toolbar The Quick Access Tool bar adding Quick Print, Create chart, othericons tospeed up work 15 Basic
11 Excel view options Excel views, arranging files & freezing panes in Excel 15 Basic
12 Basic formulas Setting basic sum, average & count formulas & summing tricks 20 Basic
13 Error check basics Viewing & editing formulas, control totals 25 Basic
14 Filtering & Sorting basics Learn to use to select transactions & identify errors - filter / sort by amount, colour or icon type, filter by more than 3 criteria in the same column, sorting within filiters, limiting datawith filters eg top 3% of values etc 25 Basic
15 Graphs intro Basic graphs & charts selecting data, charting menu & options, adding & deleting ranges for charts 25 Basic

Shortcuts, Tips & Tricks for work productivity

[top]

 

16 Using the keyboard shortcuts Shortcut / Timesaver.A 1 click method - Show attendees how to use keyboard shortcuts automatically without having to remember any of them - alternative method to using the mouse to execute commands in MS Excel 5 Intermediate
17 Fastest way to Copy a wk sheet Shortcut / Timesaver.A 1 click method to make an exact copy of a worksheet - users usually have to right click at the bottom of the sheet tab - select move or copy, select where to place the new sheet and then click ok 5 Intermediate
18 Viewing a listing of all work sheets & moving to a specific sheet automatically Simplify use & understanding of a workbook.A 1 click method to do this 5 Intermediate
19 Count, Avg & sum without using formulas An easy way to summarise information.Excel users typically use formulas to do calculations - even though they don't need to create a formula to sum, average or count or other simple calculations 5 Intermediate
20 Add, Subtract, Multiply & Divide cells - without using formulas Ever wanted to perform a mathematical operation on a list of values & change them without using formulas?Here's how using the "special" function box in Excel 5 Intermediate
21 Creating a hyperlinked menu Create order & structure in a workbook.Help users find what they are looking for in a workbook.With workbooks with a number of sheets you should have a Table of contents page.This should list the all the sheets in a workbook.Also when you click on a sheet name in the Contents page you should be able to go to the sheet automatically.You should also know ho to get back to the Table of Contents sheet automatically from any sheet 5 Intermediate
22 Viewing 2 worksheets from the same workbook side by side Useful for if you want to make a change on 1 sheet & look at the impact on another sheet in the same workbook e.g. changing compensation cost details for individual employees in a detailed sheet in a budget workbook and looking at the impact on summary compensation figures & charts automatically in the same workbook but different worksheet tab 5 Intermediate
23 Removing duplicates from a list Fast way to get a unique list of items.You may sometimes have a long lists of transactions e.g. a list of payments to all training service providers for the year & you want to get a unique list of service providers.This can be done in 1 or 2 clicks.We can look at 3 methods in Excelto do this 15 Intermediate
24 A 1 click Picklist Save time from retyping data. If you are tying info in a spreadsheet you may want names or descriptions you may not want to have to type out names or descriptions or codesyou already used in a row above - you may want Excel to fill in the name, code etc you used before automatically for you 5 Intermediate
25 Format pasting multiple times Time saver - A click method which helps you apply formatting to cells not in the same range automatically 5 Intermediate
26 Speed up data entry Shows you how to enter the same info in many non-contiguous cells in a spreadsheet at the same time 5 Intermediate
27 Wrapping text to the next line automatically Time saver -Shows you how to wrap text automatically while typing so text fit in the cell 5 Intermediate
28 Moving / jumping to beginning or end of a cell range using the keyboard Shortcut technique to speed up moving in spreadsheets - 1 key press method 5 Intermediate
29 Fastest way to Move to a specific cell in a spreadsheet Want to know how to move to cell AD50321 for e.g. in 2 clicks? 5 Intermediate
30 Moving back to your last position when you move from one worksheet to another worksheet Time saving moving backwards shortcut tip 5 Intermediate
31 Get more screen space Double click technique to maximise screen space 5 Intermediate
32 Jump to the 1st or last row / column in table An alternative double click method to speed up moving in a spreadsheet 5 Intermediate
33 Selecting ranges of data quickly Timsaving methods to highlight ranges of data for copying, cuting or selecting for a formula - 2 clicks.Want to highlight down to row R5620 in 2 clicks without having to scroll down & highlight range? 5 Intermediate
34 Cycling through absolute & relative reference signs when setting formulas Speed up creating formulas used to summarise information or look for certain values 5 Intermediate
35 Data tables Creating data tables - shortcut method.Benefits and uses of data tables to compile & analyse data & create natural language formulas (1st introduced in Excel 2007) 10 Intermediate
36 Automatic grand totals in last column & last row of a regular table How to save time with a 1 click method 5 Intermediate
37 Creating comments with images / pictures Learn how to use image comments to create an employee database or sales catalogue- picture tag employees / items 7 Intermediate
38 Creating cloud type image comments Stand out from the crowd - create Excel comments in non rectangular shapes 5 Intermediate
39 Copying formulas all the way down a column Pt 1 Time saver - Learn to do this with a click - no need to drag down the formula again 5 Intermediate
40 Copying formulas all the way down a column Pt 2: Resolve Multiple gaps issue Time saver - Learn to do this for thousands of rows in 3 quick clicks - no need to drag down the formula again 5 Intermediate
41 Creating automatic hides & shows in your data Using a large spreadsheet create multiple automatic hides and shows in the sheet.Only work with the data you want to see at a particular point in time 10 Intermediate
42 Filling cell values - tips & tricks Value Fill ranges of cells automatically - based on your fill sequence - alternative methods in excel.Custom autofill cell ranges 5 Intermediate

Graphing & Charting Data

[top]

43 Creating basic charts in Excel using the Insert Chart menu We will review basic chart creation using Design, Layout & Format options in Excel 15 Intermediate
44 Review the 5 areas every chart has and how to use them 3 Intermediate
45 How to include trend lines and forward forecast automatically in Excel 3 Intermediate
46 Using data lables in charts to highlight amounts 3 Intermediate
47 Moving charts to a desired position on a sheet easily 1 Intermediate
48 Moving charts to other sheets in a work book 3 Intermediate
49 Graphs & charts - the charting wizard vs the 1 click method for creating charts Time saving 1 click method to create charts reviewed 2 Intermediate
50 Creating graphs with 2 Y axes on a single X axis Create charts to highlight relationships & Trends using line charts superimposed on column charts with a primary and secondary axis e.g. use to review orientation costs vs # of hires 7 Intermediate / Advanced
51 User defined graphs - avoid recreating your custom designed graphs Save time creating charts which require multiple steps done.For more complex charts learn a 2 click method to create these charts on an ongoing basis. 5 Intermediate / Advanced
52 How to create a Waterfall / Reconciliationchart -graphical analysis technique Learn to rank & compare department, products & business units based on different criteria - expenditure, revenue growth, profitability etc 7 Intermediate / Advanced
53 How to create Pareto (optimal performance) charts - graphical analysis technique Use to rank and compare an organisation / departmental performance based on revenue, cost & other criteria on a cumulative & individual basis 8 Intermediate / Advanced
54 Creating Correlation XY charts for performance reporting Learn to create charts to rank performance based on 2 criteria -e.g. comparing behavioural & technical performance of employees 15 Intermediate / Advanced
55 Create compelling charts & flag data without using Excel's Charting menu Learn techniques to create heat charts, data bars & icon sets without using the charting menu in Excel . Learn to apply rule and formula based approaches to identify variances, remove duplicates & flag transactions / amounts - use flags, traffic lights & other indicators automaticaly in your reports 15 Intermediate

Using Formulas

[top]

56 Formulas - automatic callup Limit the amount of time it takes to write a formula.Helps you to write a formula without having to remember the syntax / form of calculation 5 Intermediate
57 Sumif formulas Use to summarise information by a single criteria.E.g. getting totals for compensation for a single department from a detailed list of employees in many different departments when employees names are not sorted in order of department 20 Intermediate / Advanced
58 If then else formulas Learn ho to use the if formula, if with or, if with and & multiple nested if formulas to solve practical business problems - error checking, control total setting, variance & exceptions highlighting, commission calculations etc 30 Intermediate / Advanced
59 Vlookup formulas Learn how to use vlookups to find search & find missing information in a report you are constructing & put it in your worksheet from another worksheet, learn how to use it to reconcile & identify differences in 2 spreadsheet lists.Learn how to use it to calculate commissions etc 15 Intermediate / Advanced
60 Date calculations Date differences, days360, NOW, Today,Using dates in calculations - using YearFrac formula for day basis calculations for loans & debt calculations 15 Intermediate / Advanced
61 Using named ranges in formulas Learn how you can name cells and use the names to create calculations / formulas in other sheets in a workbook.Learn how names can be used to make formulas more understandable to users 10 Intermediate / Advanced

Errors: Check & Limit them

[top]

62 Using the evaluate formula step by step feature Learn how to check for errors in a formula & understand step by step the logic of a calculation 5 Intermediate / Advanced
63 Using the Trace Error function in Excel Learn by default what types of errors Excel automatically checks for, how it flags these errors and how you can use the flags to limit Excel to check for the errors you want 6 Intermediate / Advanced
64 Using Excel watch windows Learn how to to monitor changes to cellsin multiple sheets in multiple workbooks when you make a change in one of the worksheets in your workbook.Using this technique to monitor multiple control totals 5 Intermediate / Advanced
65 How to use Excel's camera tool Learn an alternative technique to watch windows to monitor ranges of cells 5 Intermediate / Advanced
66 Using the Trace precedents / dependents buttons Check a cell in a spreadsheet & see where the links come from or determine which other cells in your workbook use the cell you are in as in input 5 Intermediate
67 Using the Edit / Goto / Special function Use this technique to scan an entiresheet & isolate errors (N/A, Div/0, Num etc)in formulas & numbers 5 Intermediate
68 Trace an error back to source Sometimes one error can create multiple errors in a spreadsheet - learn how to trace an error appearing in a cell back to the source of the error 5 Intermediate
69 Identify & view all formulas in your spreadsheet automatically Learn to use a combination key or icon click to view all formulas & switch back to normal view in a spreadsheet 2 Intermediate
70 Using the speak cells to review reports Want excel to read back your spreadsheets by row, by column, or read back as you enter rthe information? Here is what to do 6 Intermediate
71 Seven levels of protection in MS Excel Learn how to protect your file from access, prevent modification of your copy of a spreadsheet but permit review, protect the structure of your workbook, protect individual cells, hide formulas but display results & more in this section 30 Intermediate / Advanced
72 Live values preview of cells in formulas Convert a cell reference within a long formula to its value - even if linked to another worksheet 5 Intermediate
73 Tracing back to source - cells inside formulas When inside a long formula - highlight a cell reference and go back to its source & return to the sheet with the formula 5 Intermediate
74 Data validation techniques to minimize reporting errors Learn to create drop lists to limit user choices in your forms and templates, restrict what type of date, length of data fields and create in cell comments using this feature in excel 12 Intermediate / Advanced

Sensitivity & Scenarios

[top]

75 Goal seek Have you ever known what output figure you wanted from a formula and wanted Excel to work back an input.We call this "back solving".Learn how to use this in analysis & evaluation of a loan an employee wants to take 5 Intermediate / Advanced
76 Data sensitivity tables If you have two input variables and want to get the impact on output for different combinations of the input variables then here is what you need to do.We will use an employee loan example to illustrate what the monthly installment amount will be for 5 different loan amounts at 5 different loan interest rates.Actually we can do 50 or more different What if Analyses in under 1 minute 6 Intermediate / Advanced
77 Multiple scenario manager tool Learn to use Excel to create forecast / projection reports with many different variables 15 Intermediate / Advanced

Great Reporting: Pivot Tables

[top]

78 Creating & Using pivot tables for automatic data summarisations Learn the quickest way to summarise and report on your data.Learn how to create any report you want without using formulas for totals, averages or subtotals.Learn the 4 data rules you must comply with to generate a pivot report.Get drill back ability to transactions from summary report figures using pivots.Drag & drop data fields to get different views of your information 60 Intermediate
79 Formatting Pivot reports Review of pivot table style options, grand total & subtotal options as well as report layout & alternative formatting techniques for numbers, headers, and tricks to make the report comply with your organisation's layout requirements 45 Intermediate
80 Pivot table nuances and workarounds Manual calculations at the side of a pivot table issues, "Get pivot data" how to resolve, sorting & filtering challenges, calculated fields and grouped fields limitations, audit trail and checking back accuracy of reports to pivot data, disabling drill back on reports 40 Advanced
81 How you can create an adhoc management reporting tool Have a large table / database of records and what quick answers to questions from your data?Here's how 7 Intermediate / Advanced
82 Using pivot tables to analyze non-financial data Learn how to use pivot tables to obtain unique lists.Learn how to summarise names, date information etc 7 Intermediate / Advanced
83 Using pivot tables to create filtered graphs / charts Limit your charts to specific territorities, departments, job positions, products,etc directly from chart drop lists in the pivot chart 10 Intermediate / Advanced
84 Using pivot tables to group transactional dates data by months, quarters, years etc automatically Have hundreds or thousands of rows of detailed transactional data?- maybe employee training details, payments to vendors, sales transaction data. Want excel to automatically summarise the data by months, quarters and / or years.Learn to do it in 3-4 clicks 10 Intermediate / Advanced
85 Usingpivot tables to create calculated fields andformulas required for reports Where reports require more complicated calculations learn the easy way to create them using pivot tables 10 Intermediate / Advanced
86 Using pivot tables to automate departmental reporting Learn a great time saving way to use pivot tables to create reports on separate worksheets for any criteria - e.g. different departments, products, business lines, stock items etc -you want to report on 10 Intermediate / Advanced
87 Linking pivot table reports with data tables to automate reports Learn to create reports without having to redefine the source data range as you add more data on a daily, weekly or monthly basis 20 Intermediate / Advanced
88 Refresh your reports automatically using macros Learn how to create & assign a refresh macro to aclick button to facilitate 1 click refreshing of pivot tables 10 Intermediate / Advanced

New to XL 2010

[top]

89 Power Pivots How to analyse virtually unlimited amounts of transactional data using Excel 2010's new free addin. Will illustrate how to use the tool using a large access database with more rows than Ex cel has in a spreadsheet 20-30 Intermediate / Advanced
90 Data visualization reporting techniques Using slicers to analyze data - new tool to see the criteria you are limiting the data by 6 Intermediate
91 Creating cell size charts to pick up the trend in an entire series of data without going to thecharts menu 10 Intermediate
92 Cloud based backup features in MS Excel 2010 Store your data on a secure site directly from Excel using the new cloud based backup features in Excerl 2010 5 Intermediate

Macros

[top]

93 Creating a macro and modifying it - using practical examples Learn how to automate your repetitive tasks in Excel 20 Intermediate
94 Understanding absolute vs relative reference macros Understand types of macros & when to use them 10 Intermediate / Advanced
95 Macros & variable length files Creating macros to automate reports when # of records differ each week / month & you need to place totals correctly 20 Intermediate / Advanced
96 Assigning macros to images / buttons Learn how to click an image / object automatic to run a macro 5 Intermediate / Advanced

Consolidating Data & Using External Data

[top]

97 Adaptive consolidations Learn to create drag & drop consolidations where you have identically structured templates for departments, business units etc.Learn how to set up the consolidation to incorporate or remove depsrtments / lines of business etc by dragging department or business unit sheet(s) in or out (no change in your formulas required) 40 Intermediate / Advanced
98 Creating multiple consolidation range pivot tables Learn a non formula method to consolidate data on different sheets using pivot tables 10 Intermediate / Advanced
99 Non formula consolidation method Use Excel's consolidation manager to summarise data where you have identical templates e.g. operating cost budgeting for different departments 15 Intermediate / Advanced
100 Acquiring data from external sources for data analytics Learn the appropriate file types, data sources & rules for acquiring data sources (Access files, Text files etc) and loading into Excel for data analysis
Standardise the data to be loaded& create refreshable queries - avoid having to redo data load steps each month
Automate your output reports using load data which changes daily, weekly, monthly etc
35 Advanced

Other

[top]

101 How to create a mail merge Use an excel database to create a mail merge report in MS Word 20 Intermediate / Advanced
102 Text splitting & Concatenation techniques Learn techniques to split appart a full name listin 1 column to 3 columns with first middle & last name.Also learn how to join names, employee codes, GL codes, stock codes& formulas in cells and how to use practically in your work 30 Intermediate
103 Linking Excel data / charts to Powerpoint & word documents Want to link your excel tables & charts to MS Word & Powerpoint so that your documents & presentations automatically update when your Excel data changes - examine what your options are 15 Intermediate

Case Study

[top]

104 Combining the ideas A detailed work based exercise combining the various techniques learntto provide a practical solution to an organisation's management reporting requirements 120-200 Advanced
*Time in minutes taken to teach - does not take into account time to check users having challenges or time to redo each exercise