0

Every Monday, Freddy, "the numbers guy" of ACME Widgets Incorporated, downloads a spreadsheet of the widget production data from the past week. He opens it in Excel and writes some formulae to summarize the data, which he then presents ACME Widgets' CEO.

Every week Freddy is putting the same formulas into the same spreadsheets. Each week's spreadsheet has the same columns but a variable number of rows, as the number of data points fluctuates.

What is the most efficient way for Freddy to summarize the spreadsheet data each week? Copy-and-paste the formulae?

(Freddy really likes Excel. He doesn't want to learn Matlab, R, Julia, Python, Fortran, jQuery, or how to delegate.)

3

Freddy should look into Power Query, a free add-in from Microsoft for Excel 2010 and 2013 and built into Excel 2016.

Freddy can create a query to open a specific file (RawData.xlsx) and write the data into the current spreadsheet. Apply the calculations and save as Report.xlsx.

Next week Freddy saves the downloaded file over the old downloaded RawData.xlsx file, then opens the Report.xlsx file, clicks Refresh All and -- Viola!

Power Query for Excel can be downloaded here.

There are numerous possible variations on how to handle this scenario. It all depends how much Freddy is willing to learn.

  • 1
    Great answer! Is Viola Freddy's wife:)? – Máté Juhász Dec 5 '16 at 7:25
  • 2
    @MátéJuhász She's the cousin of Marco, who is married to Forumla. – teylyn Dec 5 '16 at 8:23
  • Learning should be to a minimum ;) I'm not manipulating the data, it's the end users, whom I need to keep happy. I want to give them the tools, but if throw too much at them they'll push back. – Theron Luhn Dec 5 '16 at 17:21
  • Then you can do the learning, set up the query, make it easy to use, distribute it. Then all the end user needs to do is hit the refresh button once a month/week/day. – teylyn Dec 5 '16 at 21:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.