Summarizing daily reports in Excel
April 30, 2016 12:04 PM   Subscribe

I get a daily report of activity from a set of items and I want to make a automatically-updating summary sheet (I'm fine doing this in Excel, Google Sheets, or Apple Numbers).

The report I get has one item per row, and then columns for various data points about the item. For example, quantity sold, average sale price, quantity returned, category, and so on. I get one report per day (as a CSV file) with every item represented. I would like to have a "summary" (or "dashboard") sheet that has things like the top selling item over the last N days, a graph of the sales in each category over time, and the total sales for each item.

Currently I have each day's report as a separate sheet in the workbook, but I could easily add a "report date" column and put them all in one sheet. I'm just a little stuck on how to roll all that data up into another sheet in the same workbook. I'm enough out of my depth on this that I don't really even know what to Google to find tutorials...

I think if someone can get me started with just one example here, I'd be able to figure out the rest. Perhaps graphing sales per category over the last 7 days (where the dataset might have more than 7 days worth of data, and there can be one or more item per category).

In the past I've solved stuff like this by writing a script in Python or something to do the rollup and spit out the summaries I want (sometimes I'll have the reporting script store stuff in sqlite or postgres to make it easier). And I could definitely do that here as well, but I'd really like to expand my skillset a bit and learn how to do this in Excel.
posted by primethyme to Computers & Internet (4 answers total) 2 users marked this as a favorite
 
If you want to keep this in excel (or any speadsheet application) i would change the format you're storing them in excel-

i would write a script to break each column out into its own sheet so that each page has a list of the items and then the columns are the dates with the data for whatever sheet you're on (quantity sold, avg sale price, whatever). You'd then have another script to import the new data each day.

Once you have it in this format, pivot tables are your friend and will allow you to present the data however you'd like.
posted by noloveforned at 3:55 PM on April 30, 2016


(btw- doing the above is basically turning excel into an sql database with a table for each data point. The main advantage is the ease of manipulating the data in excel through formulas and pivot tables to answer questions)
posted by noloveforned at 4:00 PM on April 30, 2016


Excel with pivot tables and Power Pivot.
posted by Ruthless Bunny at 6:16 PM on April 30, 2016


You can definitely do this on Excel. I can't walk you step by step but here are some of my tips. My background is that I get input data that is spat out in a csv file from another source, with data on individual rows and columns.

Tips:
1) Learn the use of slicers and pivot tables to make pivot charts or linked slicers.
2) I use Index Match (or vlookup) a lot , and I use it especially in conjunction with Pivot tables. Pivot Tables are annoying in that they are not particularly configurable to how I want them to be, so I have to index match pivot tables to another table
3) Name Manager! OMG best thing ever. Name Manager is really good as a data source for pivot tables, especially if you have a input data range that changes every day.

Good luck! It took me about 3 months of full time employment to create my current workflow, so do not despair!
posted by moiraine at 9:58 AM on May 1, 2016


« Older How To Ask Mechanic/Friend About Body Damage   |   Is enlisting in the Air Force at age 38 a terrible... Newer »
This thread is closed to new comments.