Creating A Pivot Table (aka Data Pilot Table) using QuickBooks Data and an Excel or Open Office Calc spreadsheet
Let’s say it’s Monday morning, you’ve arrived at your office with your steaming cup of caffeine. You are pumped up and ready for a productive day. You sit at your desk and are about to start slogging through your emails when your boss comes rushing in. She says, “Quick! I need a visual showing each sales rep’s sales by month from September 2011 to December 2012.” “No problem!”, you say knowing that QuickBooks has a standard sales graph.
You print the following report with a smile on your face:
Standard Quickbooks Sales Graph
She grimaces and says, “I’m sorry but I need a comparative bar chart showing each sales rep’s monthly sales, not one lumping all the sales together. And, get rid of the pie chart.”
You say you’ll get right on it, when a shot of despair runs through your brain. You try to wash the thought away with a swig of Starbucks, but to no avail. Then, from somewhere in the dark recesses of your subconscious, you remember seeing a tutorial on the blog Excel Analytics that would walk you through creating such an animal.
You quickly navigate to the site and click on the following link to begin…
Hope your Pivot Tables put a smile on your boss’ face. It’s good to keep them in a happy mood!
Until next time… learn, love and live your best,
Untapped resources. They are all over the place. Some are remote, some are right under our noses, some are already in our hands waiting to be used.
Having used Microsoft Excel for nearly 20 years, it has become my go-to tool whenever I need to quickly calculate and graph simple sets of numerical data. It’s simplicity makes it a handy tool for math calculations.
Need to add a list of numbers? No problem. With a simple sweep of the mouse to highlight the numbers on the spreadsheet, Excel automatically knows to add those numbers and display the total at the bottom of the page. What can be easier than that?
Sometimes, though, simplicity can be a curse. It is easy to overlook the high-performance engine hiding under Excel’s hood. It has been estimated that most people only use 10 to 20% of Excel’s capability.
Why don’t people use the other 80% of Excel’s tools?
- “I didn’t know it could do that!” — Many users are unaware of the vast number of easy-to-use tools that Excel offers.
- “I’m not a bean counter, I don’t need to know about that!” — As a small or mid-size (or larger) business owner, what you don’t know may bite you. Excel makes it easy to get to the deeper meaning buried within your financial data.
- “I’m afraid to try anything beyond basic arithmetic; I might break something!” — In the words of Master Po from the TV show Kung Fu, “Time for you to go, Grasshopper.” It’s time to put your credit card calculator (or abacus) back in the drawer and begin the journey to greater insight. The demos on this site will demystify, clarify and guide you along the way.
- “I don’t know where to look to get the data I need!” — Not a problem. I’ll be adding new resource sites for industry, national and international data. Or, you can easily supply your own from your own financial software; i.e, Quickbooks.
- “I’m not awake yet, I need my coffee first!” — Uh, okay, you got me! Just kidding. (TRIVIA OR DATA RESOURCE?: Did you know there are hermit monks in the wilds of Wyoming that roast coffee? www.mysticmonkcoffee.com Oh, the hidden resources out there that are yet to be discovered!)
This blog is dedicated to learning Excel, gaining better insight through data analysis, and a directory for locating hard-to-find, freely accessible data.
Keep your eyes open and don’t stop learning. Sometimes today’s insignificant piece of data will be tomorrow’s saving grace.
Should you have additional comments, resource links, or questions, feel free to give me a shout-out. (Time allowing, I’ll do my best to respond.)
Until next time: learn, love and live your best,
Kent Puntenney; Excel Analytics