|
|
|
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 |
|
Problems and Solutions |
Problems and Solutions |
Problems and Solutions |
The Tutorial on Excel
Table of Contents |
Help
and Tips on Excel
|
|
Chapter 1: Introduction and General Tips on Excel What do you really need to know in Excel? Chapter 1A: General Tips
|
|
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 |
Help
and Tips on Excel
|
|
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 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 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
|
| 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
easier...to make things sexier here are 3 interesting tools: |
Help and Tips on Excel
|
|
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 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 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 |
|
Ideas and reflexions on working with business data Excel or Access |
|
|
|
Help and Tips on Excel