Excel - Automatically create sheets for each day of the month

 

Excel - Automatically create sheets for each day of the month

 

Sometimes you need to create an Excel workbook where each worksheet has your daily data.

So you need to create (in advance) 30 (or 31) sheets, a sheet for each day.

 

Defining manually 30 sheets one by one?
Bad idea: cumbersome, error-prone and time-consuming.

 

There is a much better, simpler and shorter way.

The following method does this for you in a jiffy 😊

 

1)    Create a data-set with consecutive dates of the month (in column B), as demonstrated here:

 

Pic1: create a data-set with consecutive dates of the desired month

 

2)    Add a column TextDate converting the dates into text, using the TEXT function:

         =TEXT(B3,”YYYY-MM-DD”)

And then propagate the formula all the way down by double-clicking the small black cross on the lower right corner of cell C3

 

                                Pic2: Add a column with dates converted to text

 

 

3)    Create a pivot table on the same sheet, where the TextDate Column is in the Filters area of the Pivot table, the income is in the Values area and the customer is in the Rows area

 

                Pic3: Pivot table fields. Make sure to drag the TextDate to the Filters area

 

 

4)    In the Analyze tab of the ribbon, select:
Options*Show Report Filter Pages…

 

                        Pic4: Analyze->Options->Show Report Filter Pages…

 

 

5)    In the pane that appears, press OK

 

                        Pic5: Click OK to confirm filtering the dates to separate worksheets

 

6)    Et voilà,
now you have 30 new consecutive sheets with consecutive dates: 2020-11-01, 2020-11-02,….. 2020-11-30

  

                            Pic 6: Final Result – 30 worksheets from 2020-11-01 to 2020-11-30