Question: I tried searching but couldn't find anything specific to my issue. I am an excel newbie.

I have a sheet for consumption of essentials at a hostel that gets updated weekly. This is the table - 

```
Essentials  Day 1   Day 2   Day 3   Day 4   Day 5   Avg
Soap        10      9       8       -       2       7.25
Toothpaste  3       2       4       -       1       2.5
Bread       8       7       6       7       3       6.2
Butter      5       4       5       6       2       4.4

```

From this sheet I copy the averages to a separate sheet manually on a weekly basis. This table looks like -

```
Essentials      Week 1      Week 2      Week 3      Week 4      Week 5      Week 6
Soap            7.25        7           7.75        8           6           2
Toothpaste      2.5         2           2.75        3           2           1
Bread           6.2         6           6.5         7           6           1
Butter          4.4         4           4.6         6           4           1

```

I plot graphs of the averages on a weekly basis.

**My question:** This kind of activity seems like a common problem and Excel must have some tools and techniques to automate this process. I want that as soon as I update the first table, previous averages automatically move to Table 2 and graphs get updated. Is it possible? Am I overlooking something obvious or are the methods to do this somewhat tricky?

It would be extremely helpful if anyone can point me to the right direction. Any links to tutorials/videos will also be helpful.

Answer: 
Transpose your data, and insert a column for week number:

```
Week  Day  Soap  Toothpaste  Bread  Butter
  1    1    10        3        8       5
  1    2     9        2        7       4
  1    3     8        4        6       5
  1    4     0        0        7       6
  1    5     2        1        3       2
  2    1    12        2        6       3
  2    2     6        1        5       2
  2    3     4        1        5       3
  2    4     5        3        6       5
  2    5     5        0        8       1

```

Convert this into a Table (select the range, and click on Insert Table from the Ribbon).

Create a pivot table from this data (select the Table and click on Insert Pivot Table from the ribbon). The pivot table can go onto a different sheet.

Put the Soap, Toothpaste, Bread, and Butter fields into the Data/Values area of the pivot table, and change each from Sum to Average (right click on each and click on Value Field Settings). Then move the Values field that appears in the Columns area to the Rows area of the pivot table, and put the Week field into the Columns area. Finally convert the Pivot Table to Tabular View.

The pivot table will look like this:

```
                       Week
Values                    1     2
Average of Soap         5.8   6.4
Average of Toothpaste     2   1.4
Average of Bread        6.2     6
Average of Butter       4.4   2.8

```

After you add your data each week, refresh the pivot table, and the next week of averages will be appended.
```html
Sub copyVals()

Dim weekNbr As Long

weekNbr = Sheets("Sheet1").Range("A1").Value

Sheets("Sheet1").Range("D2:D6").Copy
Sheets("Sheet1").Range("G2:G6").Offset(0, weekNbr - 1).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

End Sub
```

Unless you want to retain a table for each week, you may need a simple macro to further automate the process. If you overwrite the first table each week, the values in table 2 will also be overwritten. I have written an example macro here that can hopefully get you started. 

To start, when you update table 1, you should have an indicator cell showing what week that table represents.

Then you can either map this to a button or just run it manually.

Hope this helps!