Tired of editing and changing data in excel every day manually?. Well, there are a few tricks you can pull so that the data updates automatically when you add new information.
The following will help to update a chart automatically:
There is a number of ways to auto-update a chart when you add new data to an existing chart range. They include:
Auto-update a chart when getting into new information by making a table
1. Select the data range and click Table under Insert table
2. In the Create Table dialog box, check My table has headers option to see if your data has headers then click Ok
Update a chart when getting new information with dynamic formula.
This formula helps to update data without changing the range to tables.
1. Create a defined name and dynamic formula for each column by clicking Formulas>Define Name
2. In the New Name dialog box enter Date into the Name box then choose the current worksheet name from the scope drop-down list.
3. Enter the formula =OFFSET($A$2,0,0,COUNTA($A:$A)-1 into the Refers to box
4. Click Ok
Repeat the above three steps
Note: In the above formula OFFSET function refers to the first data point and COUNTA refers to the entire column of data
- After processing the names and formulas for each column information, right-click any column within the chart then click choose data
- In the select data sources dialog box, from Legend Entries (series) section, click the name in volume B such as Ruby then click the Edit button. In the popped-out Edit series dialogue box enter=sheet3!Ruby under series value
- Click OK to go back to the select data sources dialog then repeat step 5 to update the remaining series to reflect their dynamic range such as
James: series values:=sheet3!James
Freda: series values=sheet 3!Freda
- Click the Edit button under horizontal (category ) axis labels after setting the left data to set this option.
- Click OK>OK to close the select data sources dialog
After finishing the steps the charts will update automatically when you add new data to the worksheet.
- This method does not work if you enter new column data
- You must enter new data in a systematic manner, if you skip a row the method will not be effective or work as expected.
Alternatively use a simplified method as at www.support.microsoft.com
1. Click a cell within the external information vary
2. On the information tab, within the affiliation cluster click Refresh All then click affiliation properties
3. Click the usage tab
4. Select the Refresh every check box, then enter the number of minutes between each refresh operations