Tips and Help on Excel and VBA for Excel


Why rediscover Excel?

Nothing has really changed in Excel since 1997. What has changed radically is the computer on your desk that is 10 times more powerful and allows you to accomplish small miracles with old functions and functionalities in Excel

24 chapters

Search Excel by keyword


Problems and Solutions
Excel Beginners Level

Problems and Solutions
Excel Intermediate Level

Problems and Solutions
Excel Advanced Level


  The Tutorial on Excel

Table of Contents


 Help and Tips on Excel
Excel Functionalities

Chapter 1: Introduction and General Tips on Excel

What do you really need to know in Excel?

Chapter 1A: General Tips
Chapter 1B: Excel Drop-Down Lists
Chapter 1C: CTRL, ALT and Functions Keys


Chapter 2: External Data

You can key it in, copy/paste it from all kinds of sources and you can import all kinds of data from all kinds of other file types, programs and databases in Excel.

Chapter 2A: The Import Wizard
Chapter 2B:
Importing Data 


 Help and Tips on Excel
Analyzing Data

Chapter 3: Analysing Data Dynamically

Once the data is within the Excel workbook the adventure begins. You begin by validating the data and then you start analyzing it.

Chapter 3A: The Calculator
Chapter 3B:
Comparative Analyses


Chapter 4: Database Functionalities

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.

C hapter 4A: Database Form
Chapter 4B:
Subtotals of Data
Chapter 4C:
Pivot Tables


Chapter 5: Sorting Data

A very basic yet powerful tool to analyse the data

Chapter 6: Filtering Data

Extracting segments of data at the click of a button.


Discover more in 50 Excel spreadsheets


 Help and Tips on Excel
Reporting with Excel

Chapter 7: Reporting with Excel

Excel is the MOST USER FRIENDLY, the MOST CUSTOMIZABLE reporting application. With Excel you can develop extremely powerful analyses and have AUTOMATED reports refer to them. To enhance the presentation of your reports, to make the surfing make things sexier here are 3 interesting tools:
Chapter 7A: Excel Conditional Formatting
Chapter 7B:
Excel Hyperlinks
Chapter 7C:
Excel Text Boxes
Chapter 7D: The Camera and Dashboards in Excel


 Help and Tips on Excel
Excel Functions and Formulas

Chapter 8: Introduction and General Tips on Functions and Formulas

Let's begin by some general tips to save time and avoid irritants.

Chapter 8A: General Tips
Chapter 8B: Naming Fields


Chapter 9: Signs and Formulas

Some Excel functions are almost useless when you use the right sign.

Chapter 10: IF/ISERROR Formulas

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!.

Chapter 11: Dollar Sign ($)

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.

Chapter 12:  SUBTOTAL Function

A function (9 functions in fact) that you will only use with a database and that is an important tool to analyse date DYNAMICALLY.

Chapter 13: SUMPRODUCT Function

The most useful and powerful function in Excel. From the database to the report AUTOMATICALLY

Chapter 14: INDEX/MATCH Formulas

Very useful and less limited than VLOOKUP. Type in the name and the address come AUTOMATICALLY.

Chapter 15: Date and Time 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

Chapter 16: Financial Formulas

53 functions about money. Here are a few useful ones: PMT, FV, NPER and RATE

Chapter 17: Information 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

Chapter 18: Logical 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

Chapter 19: LookUp and Reference 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

Chapter 19A - Excel HLOOKUP Function
Chapter 19B - Excel LOOKUP Function
Chapter 19C -
Excel VLOOKUP Function


Chapter 20: Math and Trig Formulas

58 functions to work with numbers. The useful ones (12): ABS, COUNTIF, MOD, POWER, ROUND, ROUNDUP, SQRT, 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

Chapter 20A - Excel SUMIF Function


Chapter 21: Statistical Formulas

80 functions to make your brain boil working with numbers. The useful ones (8): AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN and MINA

21A - Excel COUNTIF Function


Chapter 22: Text 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

Chapter 23: 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.

Chapter 24: All the Functions in Excel and What They Do

Working on hundreds of projects each year and as a Consultant I use only 20% of the 328 functions. As a reference here are all the functons and what they do.

Chapter 24 A: Database Functions in Excel
Chapter 24 B:
Date and Time Functions in Excel
Chapter 24 C:
Engineering Functions in Excel
Chapter 24 D:
Financial Functions in Excel

Chapter 24 E:
Information Functions in Excel
Chapter 24 F:
Logical Functions in Excel
Chapter 24 G:
Lookup and Reference Functions in Excel
Chapter 24 H:
Mathematical Functions in Excel

Chapter 24 I:
Statistical Functions in Excel
Chapter 24 J:
Text Functions in Excel


Ideas and reflexions on working with business data

Excel or Access
Database 101
Excel as Database
Excel and Databases
Excel and Money


    Tips and Help on Excel and VBA for Excel

Help and Tips on Excel