If you're uncertain as to whether or not you need advanced Excel
training why not take the quiz shown below? No one is watching,
just you. I hope you enjoy the quiz. Please launch Excel
on your computer and follow the questions below.
Question 1: Intermediate.
Time: 1 second.
Excel software since 1995: This is a
classic and one of my favorites. Less than 1% of my
clients know the answer. In
Excel, how do you instantly display all of your formulas so they
display not as values but as logical text?
Question 2: Intermediate.
Time: 15 minutes.
How old are you today?
Using Excel, can you determine your exact age? We don't want to
know that you are 28. We want to know if you are 28.57 years of
age. Please do it for you and 3 friends.
This is "date math" and it's incredibly important in
Excel. If you understand it, then you can determine the age of
your receivables, the age of your inventory, how long it's been since
you last called your clients, and so forth.
This assesses your knowledge of formulas, time, copying, and
pasting. It's a good intermediate exercise.
Question 3: Intermediate - difficult.
Time: 20 minutes.
Examine the Excel screen shown below. Please build this table in
Excel format.
Now, please build a bar chart for Profits for all three years. We
don't want Total Revenue or Total Expense information, only
Profit. Charts are essential in business and many managers
delegate that to their staff (hoping their staff knows how to do it).
The next part is even more difficult. With the same chart, can
you predict Profit for the years 91 and 92? This isn't
easy. If you can do it, you certainly don't need our training
help.
Question 4: Intermediate - difficult.
Time: 1 minute (if you know the concept).
In the late 1980's Lotus developed a revolutionary spreadsheet named
Improv. It did some pretty unusual things with data. Well,
Improv died out as software but Microsoft re-engineered the concept
into something useable within Excel.
Take a look at the spreadsheet shown below. Imagine that this
spreadsheet goes onward to include all 12 months of the year and is
between 120 and 150 rows deep. What special technique would show
you this data with a brand new perspective?
|
Month |
Service |
Location |
Revenue |
|
Jan |
Books |
Chicago |
20000 |
|
Jan |
Magazines |
Chicago |
750 |
|
Jan |
Pens |
Chicago |
1235 |
|
Jan |
Seminars |
Chicago |
900 |
|
Jan |
Books |
Oak Park |
8300 |
|
Jan |
Magazines |
Oak Park |
950 |
|
Jan |
Pens |
Oak Park |
1300 |
|
Jan |
Seminars |
Oak Park |
850 |
|
Jan |
Books |
Broadway |
7800 |
|
Jan |
Magazines |
Broadway |
600 |
|
Jan |
Pens |
Broadway |
350 |
|
Jan |
Seminars |
Broadway |
1525 |
|
Feb |
Books |
Chicago |
12910 |
|
Feb |
Magazines |
Chicago |
940 |
|
Feb |
Pens |
Chicago |
1210 |
Question 5: Intermediate - difficult.
Time: 1 hour.
In the late 1980's a telephone long distance company asked me to work
on a 6 week project analyzing 15,000 rows of data in a spreadsheet.
The client had row after row of financial data that needed to be
analyzed. If certain numbers were in a certain range for a row
the client wanted to audit that row of data.
If the rows of data were OK, they didn't plan on auditing the data.
With one function, 1 hour of logic and 2 hours of testing, a 6 week
project was completed in less than a day. What formula function
did I use?
