I have a number of graphs/charts that plot a certain variable versus months. Every time new monthly data is released, I want to extend the data series by one cell in order to capture the most recent month. Is there a quick way to replace the column or row number in all of these cells every time I wish to extend the chart? It doesn’t appear that Control-H searches within chart series data, so any other suggestions you guys can offer would be very helpful.
One time pain for long time gain: 1. Defined a named range for your data and edit your charts to refer to this named range (i.e., rather than directly referencing the cells themselves). 2. Each month, edit your named range to include the new column of data. Your charts will now include this additional data point.
You could also just extend the data range to include blank rows for future monthly inputs. Without any data in the blank included cells, they will not appear on your chart; however as you insert the data, each new month will pop up on the chart.
mcthorp Wrote: ------------------------------------------------------- > You could also just extend the data range to > include blank rows for future monthly inputs. > Without any data in the blank included cells, they > will not appear on your chart; however as you > insert the data, each new month will pop up on the > chart. negative, if you do this then the chart will have a sharp decline to zero for the month with no data. Won’t look very pretty.
Agree with ks112 I was going to suggest recording a macro and then just replaying the macro for each chart, but that could get hairy and I think what Anonymous said may be the best answer.
Thanks Anonymous – could you elaborate on what you mean?
Numi - If you click on the chart/graph it should highlight the fields with which it pulls the data. Then you grab the box and extend it out to grab another column/row. It will pull that into the chart.
kevinf12 Wrote: ------------------------------------------------------- > Numi - If you click on the chart/graph it should > highlight the fields with which it pulls the data. > Then you grab the box and extend it out to grab > another column/row. It will pull that into the > chart. yes
Thanks Anonymous – exactly what I was looking for. kevinf12 – that’s what I was doing in the past, but having to use the mouse slows down my workflow dramatically which is why I was looking for a quicker way to do things. I prefer not to use the mouse whenever possible
Oh OK. Here is a site if you or anyone needs to do much with charting. I used to do a lot more of it for development of mgmt reporting, etc. http://peltiertech.com/Excel/Charts/ It has basically everything you could ever want to show and lots of tips for speed, efficiency, etc.
ks112 Wrote: ------------------------------------------------------- > mcthorp Wrote: > -------------------------------------------------- > ----- > > You could also just extend the data range to > > include blank rows for future monthly inputs. > > Without any data in the blank included cells, > they > > will not appear on your chart; however as you > > insert the data, each new month will pop up on > the > > chart. > Not if you don’t enter the month without corresponding data. Meaning: you plug in your new month and new data at the same time as you update the data. > > negative, if you do this then the chart will have > a sharp decline to zero for the month with no > data. Won’t look very pretty.
kevinf12 Wrote: ------------------------------------------------------- > Oh OK. Here is a site if you or anyone needs to > do much with charting. I used to do a lot more of > it for development of mgmt reporting, etc. > > http://peltiertech.com/Excel/Charts/ > > It has basically everything you could ever want to > show and lots of tips for speed, efficiency, etc. wow… this is AWESOME… (I am admittedly a dork) Thanks kevinf12!
I couldn’t edit that previous post, so try this: ks112 Wrote: ------------------------------------------------------- > mcthorp Wrote: > -------------------------------------------------- > ----- > > You could also just extend the data range to > > include blank rows for future monthly inputs. > > Without any data in the blank included cells, > they > > will not appear on your chart; however as you > > insert the data, each new month will pop up on > the > > chart. > > > negative, if you do this then the chart will have > a sharp decline to zero for the month with no > data. Won’t look very pretty. Not if you don’t enter the month without corresponding data. Meaning: you plug in your new month and new data at the same time as you update the data. Try copying the month column down as you go using this formula for each monthly entry: 1) enter your first month in cell A1 2) copy formula into each successive cell for the month =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Speaking of not using the mouse…how do you move the cursor to the worksheet tab (to label) using only the keyboard? Also, how do you space out all the columns evenly without the mouse? Currently I highlight the whole worksheet (ctrl A) and double click on the border of a column.
TJR Wrote: ------------------------------------------------------- > Also, how do you space out all the columns evenly > without the mouse? Currently I highlight the > whole worksheet (ctrl A) and double click on the > border of a column. Cells.Select Cells.EntireColumn.AutoFit Paste this code into a macro an assign the macro a keyboard shortcut. Store it in your personal excel workbook and you can use it anytime.
TJR Wrote: ------------------------------------------------------- > Speaking of not using the mouse…how do you move > the cursor to the worksheet tab (to label) using > only the keyboard? Do you mean more than ctrl + page up / down? > Also, how do you space out all the columns evenly > without the mouse? Currently I highlight the > whole worksheet (ctrl A) and double click on the > border of a column. Try Shift space bar, alt o,c, return Then pick a number
numi Wrote: ------------------------------------------------------- > Thanks Anonymous – exactly what I was looking > for. > > kevinf12 – that’s what I was doing in the past, > but having to use the mouse slows down my workflow > dramatically which is why I was looking for a > quicker way to do things. I prefer not to use the > mouse whenever possible Numi, be careful when you update the data. If your name range refers to say five columns and you write in the 6th column it won’t automatically add it to your name range. Either: - redefine the range; or - insert a column in the middle
TheBigBean Wrote: ------------------------------------------------------- > TJR Wrote: > -------------------------------------------------- > ----- > > Speaking of not using the mouse…how do you > move > > the cursor to the worksheet tab (to label) > using > > only the keyboard? > > Do you mean more than ctrl + page up / down? Yes. How do you get the cursor in the tab to rename it. The default for excel is Sheet 1, Sheet 2, etc. so how do you rename without the mouse? > > > Also, how do you space out all the columns > evenly > > without the mouse? Currently I highlight the > > whole worksheet (ctrl A) and double click on > the > > border of a column. > > Try Shift space bar, alt o,c, return Then pick a > number That can work but its hard to know the exact column width to use, especially if you have columns with different amounts of data. Alphabound gave a good one (much appreaciated) but I was hoping I didn’t have to use a macro b/c i use a several cpus besides my own.
TJR Wrote: ------------------------------------------------------- > > Yes. How do you get the cursor in the tab to > rename it. The default for excel is Sheet 1, > Sheet 2, etc. so how do you rename without the > mouse? > Most of those options are available at alt o h. For example rename is alt o h return > > > Also, how do you space out all the columns > > evenly > > > without the mouse? Currently I highlight the > > > whole worksheet (ctrl A) and double click on > > the > > > border of a column. > > > > Try Shift space bar, alt o,c, return Then pick > a > > number > > That can work but its hard to know the exact > column width to use, especially if you have > columns with different amounts of data. > > Alphabound gave a good one (much appreaciated) but > I was hoping I didn’t have to use a macro b/c i > use a several cpus besides my own. That’s alt o c a. That does an auto fit, but all the columns won’t be the same size. If you want them the same size it’s not hard to learn some widths e.g. try 2, then find that is stupidly small so try 10 etc. Takes a couple of seconds.