Forum Discussion
Tracking line graph
Hello!
I'm in the process of creating an excel sheet which has a list of savings we've made. On another tab I want to make a line graph which shows how we are aligning to where we need to be to hit target.
A problem I ran into was how to get excel to recognise how much was saved on each day.
I realise that I could get around this by entering the date for when a saving was entered, but the issue is that the amount we've saved on a product based on how many we've sold to customers and is a live figure. Therefore the amount saved for each product will be constantly changing.
What I need is a way to make excel single out each day and work out how much was saved on each day (or added on top of total amount).
Hope this makes sense. Many thanks in advance.
untested...
Without having more detailed information available, I could imagine that one way to track the daily savings in your Excel sheet is to use a PivotTable.
A PivotTable can summarize your data by date and calculate the total savings for each day.
Here’s how you can do this:
- Make sure your data is organized in a table with columns for Date and Savings.
- Click on any cell in your data table and go to the “Insert” tab in the ribbon. Click on the “PivotTable” button.
- In the “Create PivotTable” dialog box, make sure the “Select a table or range” option is selected and that the “Table/Range” field shows the correct range of cells for your data table. Choose where you want to place the PivotTable and click “OK”.
- In the PivotTable Field List, drag the “Date” field to the Rows area and the “Savings” field to the Values area. By default, Excel will sum the values in the Savings column for each date.
- You can now see the total savings for each day in the PivotTable.
You can then use this PivotTable as the source data for your line chart to show how your savings are aligning with your target over time.
Maybe this helps 🙂
Thanks NikolinoDE will try this as well.
Hi kentleywiltshire4 ,
Could you please send a sample of you file? You can populate it with fake data. I'm just trying to find a solution for it, but I will have to check how you organize your entries.
Just to confirm: you want the line chart to present the cumulative totals from each day? So if you got a total savings of 100 in Jan 31st and a total savings of 50 in Feb 2nd, you want the line chart to present Feb 2nd as 150, correct?
If yes, you first have to transform your savings column in numeric data (right now it is as text, at least in the file that you sent) and summarize the entries by day. You can do this by using a pivot table or the UNIQUE function connected to a SUMIF (example of the attached file).
Please, confirm if my assumptions are correct and let me know if the file that I sent you is in the right direction for solve your problem.
