In the Screenshot above, this Pie Chart will adjust automatically to new values added into the Excel Sheet, adding or removing new categories as values are entered or deleted.
I have something like around 80 different Categories, but each day perhaps only 10 or so get used, but they could be any 10 of the 80.
This chart shows the Activity, followed by the total daily duration engaged in the activity, followed by the percent of 24 hours the activity comprised.
Step 1 – Set up the Aside Table:
- In your Existing Table, enter some random bits of Information that get calculated into your Value Data column you want to make a chart representation of.
- Copy the column or rows which contain the value data and calculation formula you want represented in your Chart or Graph.
- Paste the data, with formulas and everything, to a part of your worksheet which is below any other rows you use in your sheet, and its best to put it out to the right as well. So essentially just to the lower right of all of your existing data, with now sharing of rows or columns with anything else. Just put it somewhere you can forget about it and never need to see it.
- Delete any cells which aren’t related to the data you want, such as any sectional breaks or sum total lines etc, by deleting the entire row from your spreadsheet.
- Copy and paste the Text Labels that describe what the value column represents, and paste it below the previous data you pasted
- Again, Delete any cells which aren’t related to the data you want, such as any sectional breaks or sum total lines etc, by deleting the entire row from your spreadsheet.
- Cut all of the Text Labels you just cleaned up and Paste them just to the side of the value column you pasted earlier, so you now have 2 side-by-side columns that match exactly Label to Value, and which are below and out to the side of all of your normal working area.
- Give each Column a Heading at the top of each
- Highlight the Value column you created from top to bottom
- Go to > Ribbon > Insert > and Click on “Table”
- Check “My Table has Headers” & hit OK
- Click on the little Arrow in the header of your new Column Table & Click Number Filter > Does Not Equal > and uncheck ‘0.0’ and ‘(blank)’ and be sure everything else is checked > hit OK
Step 2 – Make the Chart:
- Highlight the New Value Table from top to bottom & go to Ribbon > Insert > Pie Chart
- Put the chart next to your data sheet or wherever you want to place it that looks nice.
- Right-Click the Pie Chart > Select Data Source and make sure your Column Header is listed in the left box
- In the Right Box, click “Edit” and select the first to the last Text Label Name Cells that are beside your table & click OK. Be sure everything is checked & hit OK
- Click on the Chart > click the “+” > check “Data Labels” > Best Fit
- Click on the Data Label which shows the value number and they should all be selected. Right-Click & Select “Format Data Labels”
- Select ‘Category Name’ % ‘Percentage’
- Click on the Chart > Ribbon > Home > Adjust Text Size etc
- Click on the Pie Chart so only the Pie is selected rotate the pie so labels are how you want them
- Click on Chart > click ‘+’ > Legend > click the arrow > Select ‘Right’ or wherever you want it.
- Enter & delete some data in your main sheet and watch the Chart change and automatically update
Step 3 – Save your work:
- Do as Save As at this point, and keep this as your Master File that you’ll change later if you need to do anything major or fundamental to the worksheet data
Step 3 – Create a Script to Auto Update your Filtered Column:
- Hit Alt + F11 & Be sure “Worksheet” is selected in the top left box, and “Change” is selected in the top Right
- Copy & Paste in the Following:
Private Sub Worksheet_Change(ByVal Target As Range) ' first remove filter ActiveSheet.Range("$AM$91:$AM$164").AutoFilter Field:=1 ' then apply it again ActiveSheet.Range("$AM$91:$AM$164").AutoFilter Field:=1, Criteria1:="<>0" End Sub
- Change the four ‘AM’s above to the Column Letter(s) of your Value Column; Change the two ’91’s to the Row number of your Column Table Header where you named it; and change the two ‘164’s to the last row in the column that contains your value data
- Hit Alt + F11 again.
- Enter some Data in your main worksheet and see that your chart and the results in your filtered column list are updated
Step 4 – Create the Form Fields:
- Select all of the cells where data should be entered manually, go to Format Cells > Protection > and uncheck “Lock” then hit OK
- Go to > Ribbon > Review > Protect Sheet > and select only “Select unlocked cells” and nothing else, then give it a Password if you want & hit OK
Optional Step 5 – Finalize:
- You’ll want to keep save this as a separate Master xlmb file
- If you want to make any major edits, don’t use the Master one, Save a new copy, make your edits, then Save as a New Master & replace the old one
- Once it’s done, there aren’t any edits you’ll want to do directly in the sheet, its just for your data entry and to see the results, as a the update feature will remove the ctrl+Z functionality for anything before it updates, making it dangerous to edit the original.
- Anyway, just be careful making edits
- Set your Page Breaks as you want them printed, then after each day just print to PDF or Paper, for your records.