more-arw search

Q&A Forum

What's your greatest challenge with Excel?

Excel blogs are filled with specific questions about using Excel. So I thought it might be useful to back up and ask a more general question:

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

Answers

Topic Expert
Deborah Godfrey
Title: Budget Administrator, Business Manager, ..
Company: Seeking Employment
(Budget Administrator, Business Manager, Project/Program Manager, Seeking Employment) |

My biggest challenge is learning to effectively create and understand pivot tables. Once I master this I will be well on my way. Any suggestions.

Bryan Frey
Title: VP Finance/Corp Controller
Company:
(VP Finance/Corp Controller, ) |

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.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

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.

3862 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.