Other Links
The 33 lessons and the 25 spreadsheets
are only available from the desktop tutorial
|
The lesson available on line on the week starting Excel Lesson 3-7: The SUMPRODUCT Function Next Week Excel Lesson 1-6: The Calculator/Validator Bookmark this site and come back next week |
Tutorial on Excel
Table of Contents
Section 1: Analysing Data with Excel
The tools to get the data into Excel, to validate it and analyse it. There are also these tips to make your life easier and allow you to work faster.
Lesson 1-1: General Excel Tips
Cool and useful tips to do more with Excel and work faster
Lesson 1-2: CTRL, ALT Function Keys
The short cuts
Lesson 1-3: External Data
Seven ways to get the data into Excel
Lesson 1-4: Importing External Data in Excel
Getting the data from outside sources
Lesson 1-5: Import Wizard
The wizard helps you fetch data from outside databases and the WWW
Excel Lesson 1-6: The Calculator/Validator
(Level: Beginner)
There is a calculator integrated within Excel. A marvelous tool to calculate AUTOMATICALLY subtotals that the report designer has not calculated. With this tool you can validate from the data sheet if the totals on the report are valid which is indispensable for Accountants like us.
Lesson 1-7: Comparative Analysis
Look at two programs, two workbooks and even two sheets from the same spreadsheet on the same screen. An easy way to copy/paste
Lesson 1-8: Excel Databases
When Excel recognizes your set of data as a database you get access to powerful functionalities to validate, organize and analyse your data dynamically. Excel is a very interesting database application that doesn't require you to go through an extensive training.
Lesson 1-9: Sorting Data
A very basic yet powerful tool bring order into the data and to analyse it
Lesson 1-10: Filtering Data
Just look at what is important. Don't be distracted by the rest. The most powerful analytical tool in Excel.
Lesson 1-11: Database Subtotals and Outlines
Another way to group the data for a better look.
Lesson 1-12: Database Form
The easy and efficient way to enter data en an Excel database
Lesson 1-13: Pivot Tables
It is not that difficult if you really need it. A powerful analysis tools but poor reporting tool.
Section 2: Reporting with Excel
The report is a communication tool. The purpose of a report is to inform and to highlight the most important conclusions of an analysis. Reports are designed with the reader in mind to help him focus and to make sure he is interested in what is reported. Because of the variability in what needs to be reported about and of the readers themselves the best reporting program to achieve your goals is Excel. Only with Excel can you design exactly what you want and automate the reports without complex training and at a reasonable cost.
Lesson 2-1: General Reporting Tips
Tips to enhance you reports
Excel Lesson 2-2: Drop-Down Lists
(Level: Beginner, Intermediate and Advanced)
A small arrow appears when you select the cell and a list of values that you have created is offered. Anybody can do it and they can get really sophisticated.
Lesson 2-3: Conditional Formatting
For some punch and highlights in your reports
Lesson 2-4: Hyperlinks
For great on screen presentations. Forget "Power Point"
Lesson 2-5: Text Boxes
When you have to deal with many sizes of fonts on a single row. Great headers
Lesson 2-6: Charts
You imaginations is the limit and they say much more to large audiences than tables full of numbers
Lesson 2-7: Camera and Dashboards
Section 3: Excel Functions and Formulas
There are 329 of them but even consultants only use 20% of them regularly. A new language. If it makes sense in English you can translate it into a formula that Excel will understand
Lesson 3-1: General Excel Tips Functions and Formulas
The F4 Key to add dollar signs ($) and other useful tips
Lesson 3-2: Naming Fields
Some use them some don't. I only use them with drop-down lists
Lesson 3-3: Signs and Formulas
The asterisk multiplies but it really becomes powerful when use with SUMPRODUCT
Lesson 3-4: Absolute and Relative References
When you discover how easy it is to add $ with the F4 key you start using them. Then you develop a single formula and copy/paste it everywhere.
Lesson 3-5: ISERROR Function
If you write =A1/B1 and B1 is zero you get an error message #DIV/0!. With an IF/ISERROR formula you prevent all these error messages including #NA!.
Lesson 3-6: SUBTOTAL Function
A function (9 functions in fact) that you will only use with a database and that is an important tool to analyse data DYNAMICALLY
Excel Lesson 3-7: The SUMPRODUCT Function
(Level: Intermediate and Advanced)
The simple tool to automate ALL your reports. How can you live without the most useful and powerful function in Excel? 95% of users don't know about it and
when they do they start using it DAILY.
Lesson 3-8: INDEX/MATCH Formulas
Very useful and less limiting than VLOOKUP. Type in the name and the address comes AUTOMATICALLY.
Lesson 3-9: Date & Time Functions and Formulas
20 functions to work with dates and times. BEWARE OF FORMATS. The useful ones (12): DATE, DATEDIF, DAY, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TODAY, WEEKDAY and YEAR
Lesson 3-10: Financial Functions and Formulas
53 functions about money. Here are a few useful ones: PMT, FV, NPER and RATE
Lesson 3-11: Information Functions and Formulas
17 functions to investigate what you are working with. The useful ones (4): CELL, ISNUMBER and ISTEXT. The fourth one is so important that it has its own page ISERROR
Lesson 3-12: Logical Functions and Formulas
6 functions to try to be logical. The functions themselves are easy the challenge is to BE logical (!!!). The useful ones (3): AND, IF and OR
Lesson 3-13: LookUp & Reference Functions and Formulas
18 functions to locate values in tables and sheets The useful ones (7): INDIRECT and OFFSET. The LOOKUP group (HLOOKUP, LOOKUP and VLOOKUP). The other two are so important that they have their own page INDEX and MATCH
Lesson 3-14: Math & Trig Functions and Formulas
58 functions to work with numbers. The useful ones (12): ABS, COUNTIF, MOD, POWER, ROUND, ROUNDUP, QRT, SUM, SUMIF and TRUNC. Then two functions that have their own page. The interesting SUBTOTAL and the most important of the category and of Excel SUMPRODUCT
Lesson 3-15: Statistical Functions and Formulas
80 functions to make your brain boil working with numbers. The useful ones (8): AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN and MINA
Lesson 3-16: Text Functions and Formulas
24 functions to work with strings (sequences of letters and numbers). Assembling names and first names, dissembling them and doing all kinds of tricks with text. The useful ones (10): CONCATENATE, FIND, LEFT, LEN, MID, REPT, RIGHT, TEXT, TRIM and VALUE
Lesson 3-17: Array Formulas
Using the same functions as in other formulas you submit them with SHIFT/CTRL/ENTER and all of a sudden they do very special things.
Lesson 3-18: Obsolete Functions
They are not really obsolete but when you find another function that does the same thing and much more you tend to put them aside. 18a - Excel SUMIF Function 18b - Excel COUNTIF Function 18c - Excel HLOOKUP Function 18d - Excel LOOKUP Function 18e - Excel VLOOKUP Function
Appendix
Working on hundreds of projects each year as a Consultant I use only 20% of the 328 Excel functions. I even use one that is not in this list (developed from the Microsoft's "Insert Function" dialog window). The function is simple yet powerful and it is DATEDIF that calculates the number of years, months or days between 2 dates.
As a reference here are all the functions and what they do.
Appendix A: Excel Database Functions (12)
Appendix B: Excel Date and Time Functions (20)
Appendix C: Excel Engineering Functions (40)
Appendix D: Excel Financial Functions (53)
Appendix E: Excel Information Functions (17)
Appendix F: Excel Logical Functions (6)
Appendix G: Excel Lookup and Reference Functions (18)
Appendix H: Excel Mathematical Functions (58)
Appendix I: Excel Statistical Functions (80)
Appendix J: Excel Text Functions (24)