What types of spreadsheet tasks are causing particular problems at your company, and why?
Where do you or your Excel co-workers seem to waste most of your time with the product? What sorts of Excel challenges are you facing that ought to have an easy solution?
From a slightly different perspective, what sorts of reporting or analytical tasks should you be able to do with Excel, but have been unable to so for some reason?
Perhaps other visitors can offer solutions.Or perhaps I can.
Charley
My biggest challenge is
Hate to say it, but they're really easy to learn once you have a good use case. You can always "gin" one up artificially as well. Pivot tables excel for extracting data out of multi-column databases. For example, if you have a list of 1,000 customers in column A, and in subsequent columns: their Industries, their city, state, region, date of sale, fiscal quarter of sale, year of sale, and finally total sales to each customer in dollars, you have the basis for a great pivot table. You can pull such a spreadsheet together quickly with fake data and then have at it. With a pivot table you can quickly see you sales by quarter, by region, by industry. Better yet, how about sales by region by quarter or sales by industry by region, etc., etc.. Some time spent with this will de-mystify pivot tables for you instantly.
1
The problem most people have with PivotTables is: "How do I get the data I want?"
But over the long term, the second problem is even worse: "How do I easily add PivotTable data to my standard reports and analyses?"
I know, Microsoft has created many ways to make PivotTables pretty. But it's a long distance from "pretty" to "readable" and "insightful".
So, once I've used a PivotTable to explore data, I often put a staging area (an Excel database) between my reports and the PivotTables. This allows my reports and analyses to use INDEX-MATCH, SUMPRODUCT, and other functions to use data from any number of sources all in the same presentation.
(One reason IT reports often are so useless is that they lack the analytical sophistication that we can add with Excel. That sophistication almost always requires data and calculations in addition to what a single PivotTable can offer. That's one reason why data from a single PivotTable usually isn't sufficient for *good* reporting.)
In the past, I've used GETPIVOTDATA to return data from PivotTables to the database. However, Excel 2010 makes it easier for PivotTables to return standard flat files to Excel. This allows us to treat each PivotTable as a database that can work very nicely with INDEX-MATCH, SUMPRODUCT, and SUMIFS.
As an aside, I intentionally did not add VLOOKUP to this list. There is **never** a reason to use that clumsy function.
