How to Make a Graph in MS Excel That You Change Every Day
Microsoft Excel 2010 graphs provide a great way to visualize your numerical data, but if your data changes daily it can become cumbersome to constantly update the graph. Excel can automatically update any changes to your existing data, but the default charts don't know when you have added any new data to your spreadsheet. To get around this issue, create a normal chart, then create an expandable named range which you can direct the chart to use.
Other People Are Reading
Instructions
-
-
1
Open the Microsoft Excel 2010 spreadsheet that contains the data you want to make into a dynamic graph.
-
2
Click anywhere in your data, then click the "Insert" tab at the top of the screen. Click the chart button that corresponds to the type of graph you wan to use, then select your desired graph from the popup menu to make it appear on the spreadsheet.
-
-
3
Click the "Formulas" tab at the top of the screen, then click the "Define Name" button in the middle of the ribbon. Type a name for the first data series in your chart. This is normally the same text as appears in the header above the data series on your spreadsheet, but can be anything you desire.
-
4
Place your cursor into the "Refers to:" box. Delete whatever is in the box, then type in "=OFFSET(" and click on the first cell in the column or row where the data for this series is located. Type in a comma, the number one, another comma, then number zero, then another comma. Then type in "COUNTA($A:$A)-1)" but change each "A" after the dollar signs to the column letter or row number where your data is located.
Your final formula should look similar to this:
=OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)
Click "OK" to close the Define Name window. Click the "Define Name" button again and repeat the entire process for each data series in your graph.
-
5
Right-click on your graph and choose "Select Data" from the popup menu. Click on your first data series, listed on the left side of the window that appears, then click "Edit."
-
6
Place your cursor into the "Series values" text box and press backspace several times to delete everything after the exclamation point. Type in the name of the range you created for this set of data.
-
7
Click "OK" to go back to the Select Data Source window. Click on a different data series and repeat the process. Continue until you have entered your names for each data series, then click "OK" to close the Select Data Source window. Your chart now changes as you add new data each day.
-
1
Tips & Warnings
If your graph's Y-axis has text or data values that are listed on the spreadsheet, name these as well and apply the name by clicking the "Edit" button on the right side of the Select Data Source window.
