Home

How do I use defined names to automatically update a chart range in Excel 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-314391%22%20slang%3D%22en-US%22%3EHow%20do%20I%20use%20defined%20names%20to%20automatically%20update%20a%20chart%20range%20in%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314391%22%20slang%3D%22en-US%22%3E%3CP%3EMicrosoft%20Support%20Help%20Article%20183446%20(How%20to%20use%20defined%20names%20to%20automatically%20update%20a%20chart%20range%20in%20Excel)%20does%20not%20state%20it%20applies%20to%20Excel%202016.%20%26nbsp%3BI%20have%20though%20tried%20using%20the%20instructions%20given%20in%20Method%201%20for%20Microsoft%20Office%20Excel%202007%2C%202010%20and%202013%20in%20Microsoft%20Excel%202016%20MSO%20(16.0.9126.2336)%2032-bit%20within%20a%20Chart%20that%20uses%20the%20Stacked%20Bar%20Chart%20Type%20simulate%20a%20Gantt%26nbsp%3B%20Chart%20from%20Start%20Date%2C%20Duration%2C%20and%20Task%20Description%20columns.%26nbsp%3B%20The%20chart%20displays%20correctly%20when%20the%20defined%20names%20are%20first%20created%20and%20the%20Chart%E2%80%99s%20Select%20Data%E2%80%A6%20is%20first%20modified%2C%20but%20adding%20additional%20cell%20data%20does%20not%20auto-update%20the%20Chart.%26nbsp%3B%20Clearly%20the%20rand()*0%20is%20not%20volatile%20enough%20in%202016.%26nbsp%3B%20So%20what%20can%20I%20use%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-314391%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eexcel%20chart%20names%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314622%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20defined%20names%20to%20automatically%20update%20a%20chart%20range%20in%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314622%22%20slang%3D%22en-US%22%3E%3CP%3EI%20agree.%26nbsp%3B%20Using%20a%20Table%20within%20the%20Excel%20sheet%20in%20this%20instance%20is%26nbsp%3Bby%20far%20the%20easiest%20technique%20to%20use%2C%20the%20reference%20method%20is%20a%20lot%20easier%20too%3A%20%22%3D%3CTABLE_NAME%3E%5B%3CCOLUMN_NAME%3E%5D%22.%26nbsp%3B%20The%20chart%26nbsp%3Bnow%20automatically%20updates%20as%20soon%20as%20the%20table%20is%20extended%2Fshrunk%20in%20rows.%3C%2FCOLUMN_NAME%3E%3C%2FTABLE_NAME%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20the%20suggestion.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314560%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20defined%20names%20to%20automatically%20update%20a%20chart%20range%20in%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314560%22%20slang%3D%22en-US%22%3EYou%20should%20be%20able%20to%20achieve%20exactly%20that%2C%20just%20by%20formatting%20your%20data%20table%20as%20table%20(Home%2C%20Format%20as%20table).%20No%20dynamic%20range%20names%20required.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314476%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20defined%20names%20to%20automatically%20update%20a%20chart%20range%20in%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314476%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Fhelp%2F183446%2Fhow-to-use-defined-names-to-automatically-update-a-chart-range-in-exce%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Fhelp%2F183446%2Fhow-to-use-defined-names-to-automatically-update-a-chart-range-in-exce%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314472%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20use%20defined%20names%20to%20automatically%20update%20a%20chart%20range%20in%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314472%22%20slang%3D%22en-US%22%3EA%20link%20to%20the%20helkp%20article%20would%20help%20a%20lot!%3CBR%20%2F%3ECan't%20you%20convert%20your%20range%20to%20a%20table%3F%20It%20will%20cause%20Excel%20to%20update%20the%20chart%20when%20data%20is%20added.%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Microsoft Support Help Article 183446 (How to use defined names to automatically update a chart range in Excel) does not state it applies to Excel 2016.  I have though tried using the instructions given in Method 1 for Microsoft Office Excel 2007, 2010 and 2013 in Microsoft Excel 2016 MSO (16.0.9126.2336) 32-bit within a Chart that uses the Stacked Bar Chart Type simulate a Gantt  Chart from Start Date, Duration, and Task Description columns.  The chart displays correctly when the defined names are first created and the Chart’s Select Data… is first modified, but adding additional cell data does not auto-update the Chart.  Clearly the rand()*0 is not volatile enough in 2016.  So what can I use?

4 Replies
Highlighted
A link to the helkp article would help a lot!
Can't you convert your range to a table? It will cause Excel to update the chart when data is added.
Highlighted
You should be able to achieve exactly that, just by formatting your data table as table (Home, Format as table). No dynamic range names required.
Highlighted

I agree.  Using a Table within the Excel sheet in this instance is by far the easiest technique to use, the reference method is a lot easier too: "=<table_name>[<column_name>]".  The chart now automatically updates as soon as the table is extended/shrunk in rows.

 

Many thanks for the suggestion. 

 

Related Conversations
Insert an image in Excel header
mat74 in Excel on
1 Replies
DAX Powerpivot - How to solve this problem in PowerPivot
kayyeah11 in Excel on
0 Replies
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
4 Replies