By Sections
Other Links
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: General Excel Tips
Cool and useful tips to do more with Excel and work faster
Lesson 2: CTRL, ALT Function Keys
The short cuts
Lesson 3: External Data
Seven ways to get the data into Excel
Lesson 4: Importing External Data in Excel
Getting the data from outside sources
Lesson 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 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 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 9: Sorting Data
A very basic yet powerful tool bring order into the data and to analyse it
Lesson 10: Filtering Data
Just look at what is important. Don't be distracted by the rest. The most powerful analytical tool in Excel.
Lesson 11: Database Subtotals and Outlines
Another way to group the data for a better look.
Lesson 12: Database Form
The easy and efficient way to enter data en an Excel database
Lesson 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 1: General Excel Tips Functions and Formulas
The F4 Key to add dollar signs ($) and other useful tips
Lesson 2: Naming Fields
Some use them some don't. I only use them with drop-down lists
Lesson 3: Signs and Formulas
The asterisk multiplies but it really becomes powerful when use with SUMPRODUCT
Lesson 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 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 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
VBA 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 8: INDEX/MATCH Formulas
Very useful and less limiting than VLOOKUP. Type in the name and the address comes AUTOMATICALLY.
Lesson 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 10: Financial Functions and Formulas
53 functions about money. Here are a few useful ones: PMT, FV, NPER and RATE
Lesson 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 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 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 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 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 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 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 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
Articles
Excel or Access
Moving "UP" to Access. A costly mistake
All About Money
With the right Excel application you can save or make tons of money. The cost/benefit ration can be awesome
Database 101
When you discover what it is you see that it is very simple and indeed the best way to store information
Excel as Database
If there is one person maintaining it there can be any number of users. Without the millions of small Excel databases in large corporations it would be the end of corporate America. When you feed you generals and not the soldiers you loose the war.
Excel and Other Databases
If you are allowed to access the data Excel is the best analysis and reporting tool. That doesn't make IT people happy.
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)