98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Cumulative variance
Cumulative variance
Resolved · High Priority · Version 2016
Claudia has attended:
Excel Intermediate course
Cumulative variance
Hello,
I have a workbook with about 15 sheets with different stores. Each sheet holds information that I add to each week. i want it to automatically populate the variance from the last two weeks on information per sheet.
currently i have this manually where i just change the cell number but to do this for each sheet is time consuming.
RE: Cumulative variance
Hi Claudia,
Thank you for the forum question.
Yes what you want can be totally automated and you have a couple of options, but both options are advanced.
The data on your sheets must be stored in Tables. On the Excel Intermediate course you learned how to create tables (select a cell inside the data and press Ctrl T and click OK).
Append all the tables using Power Query. Query the data to always display the last two weeks, and create a PivotTable showing the result.
The video link below will show you have to append the tables.
https://www.youtube.com/watch?v=oeQPtbFNYIw
Next step is to filter the date to always display the last two weeks.
If you click on the filter in the query editor next to the date heading and click Date/Time Filters and click In the Previous, then you can define that you want only the last two weeks.
You will now have to add custom column to the table in the query. Add a column name and in the custom column box type:
=Number.ToText(Date.Year(["the name of your date column"]))&" "&Number.ToText(Date.WeekOfYear(["the name of your date column"],Day.Monday))
On the Home tab in the query editor click close and load. The query will now create a table in a new sheet in your workbook.
Next step is to create a PivotTable to display the variance.
Create a Pivot Table. Add your stores as Rows and your Values to values and the custom column to Columns. Right click on one of the values in the Pivot Table and click Show Values As and click on the option Difference from.
Base Field select the name of the custom column you created in the query. Select Previous in the Base Item list.
The Pivot Table will now always show the last two weeks variance. You just have to enter the data on the sheets.
Another option is to VBA code it, but you will need a very advanced knowledge of VBA to do this.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 6 Feb 2020: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Saving your Excel Spreadsheet as a CSV FileIn situations where you need to save your Excel spreadsheet as a CSV file, follow these simple steps. |