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
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.
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
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies