Published Now and Again for Business Users of Microsoft Excel.
Excel Dates + Dynamic
Reporting +
Dashboard Coaching + Excel Shortcuts
Charley Kyd
Thursday, Feb. 10, 2011
If you like this newsletter, please forward it to
other Excel users.
There's a lot to cover, so let's get
started...
Dynamic Excel
Reporting
In the early 1990s, I discovered an Excel add-in that slashed my Excel reporting
time by about 99%. Here's how it worked...
It offered industrial-strength lookup functions that could return specific
values from data parked in memory. For example, I could enter a formula to
return sales for Product A in the Northwest Region for March. Then I could copy
the formula to return the sales for April, May, and so on.
This technology meant that I could set up a report for nearly any data I wanted,
for any period, and use the same workbook to report any other period.
In other words, I could generate a report for a new period in four steps:
- Open last month's report.
- Change the date setting in one cell.
- Recalculate the workbook by pressing F9.
- Print.
And that reduced my reporting time by about 99%.
Over the years, I've worked out ways to do the same sort of thing using only
Excel. My post, Introducing Dynamic
Excel Reports, provides a quick sketch of the process.
Oh...the add-in was an Excel-friendly OLAP
product, which I discuss at the link.
How to Convert Unusual Excel Date Formats
When you import data into Excel, dates often arrive in formats that Excel
doesn’t recognize. This often means that you must go to a lot of extra work to convert
the dates into date serial numbers.
However,
Excel offers an easily overlooked command that converts many unusual date
formats into date serial numbers. I recently blogged about it in
How to Convert Unusual Date Text in
Excel.
Excel Dashboard Coaching
I'm trying something new, and I'll see how it works. Here's the issue...
I simply cannot answer all the questions I'm asked about Excel dashboards. I’ll
be happy to support anyone having technical needs, as always. But I just don’t
have the time to answer questions related to enhancing or applying Excel
dashboards.
The reason is simple: I've sold tens of thousands of dashboard products and
there's not enough time in the day to answer questions from everyone.
However, quite a few customers have asked me to offer some kind of affordable
dashboard coaching.
I've finally decided to give it a try. You can
learn more about my Excel dashboard
coaching here.
Download
My Definitive List of Excel Keyboard Shortcuts
In an online forum recently, another Excel MVP posted a link to the best list he knows of Excel
keyboard shortcuts. But my personal list of shortcuts has more than 250 of
them...about twice as many as the other "best list".
So I've decided to make my personal list available to anyone who wants it. You can
download my Excel keyboard shortcuts here.
If you know of a shortcut that's not on the list, or if you find a
mistake on the list, please let me know. I'll update the list so you can
download it again.
More later,
Charley
|
