We all know that to make a chart we must specify a range of values as input.
But what if our range is dynamic and keeps on growing or shrinking. You cant edit the chart input data ranges every time you add a row. Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows?
Well, you can do it very easily using excel formulas and named ranges. It costs just $1 per each change. 😉
Ofcourse not, there are 2 ways to do this.
The easiest way to make charts with dynamic ranges
If you are using Excel 2003 or above you can create a data table (or list) from the chart’s source data. This way, when you add or remove rows from the data table, the chart gets automatically updated.
See the below screencast to understand how this works
Using OFFSET formula to make dynamic ranges for chart data
For some reason if you cannot use data tables, the next method is to use OFFSET formula along with named ranges.
We all know that OFFSET formula is used to get a range of cells by passing on starting point and number of cells to offset. Steps for creating dynamic chart ranges using OFFSET formula:
1. Identify the data from which you want to make dynamic range
In our case the data should be filled in the following table. As user keeps on adding new rows we will have to update our chart’s source data.
Lets assume the data table is in the cell range: $F$6: $G$14
2. Write OFFSET formulas and create named ranges from them
Ok, the problem is that as and when we add a row at the end (or remove a row), we should update the chart’s data range. For this, we can use OFFSET formula.
A refresher on how to use OFFSET formula:
3. Create a new named range and type OFFSET formula
Create a new named range and in the “refers to:” input box, type the OFFSET formula that would generate a dynamic range of values based on no. of sales values typed in the column G. I have used the below formula. You can write your own or use the same technique.
=OFFSET($G$6,0,0,COUNTA($G$6:$G$14),1)
Set the named range’s name as “sales_data” or something like that.
Now repeat the same for years column as well and call it “years_data”
4. Create a column chart and set the source data to these named ranges
Create a column chart. For the source data use the named ranges we have just created.
Important: You must use the named range along with worksheet name, otherwise excel wont accept the named range for chart source data.
That is all, now your chart is dynamic
Download the Dynamic Chart Ranges Tutorial Workbook
Click here to download the dynamic chart ranges workbook and use it to learn this trick. I have given Excel 2007 file since the file includes tables.
Bonus Tip: Edit chart series data ranges using mouse
If you have no time for writing lengthy formulas or setting up data tables, you can still save time when editing chart series data ranges. Just select the series by clicking on the chart. Now excel shows highlighted border around the cells from which the chart series is created. Just click on the bottom-right corner and drag it up and down to edit the chart series data ranges. (more: Edit formula ranges using mouse)
See the demo to understand this:
More tricks to make dynamic charts using Excel
Here is a list of tutorials and examples recommend just for you. Go check them out and make your charts even more dynamic.
- Filter Charts just like you filter Data
- Select and show one chart from many
- Dynamically Group Related Data in the Charts
- Use Data Filters as Chart Filters and Make Dynamic Charts
- A Dynamic Donut Bar Chart – Show total and break-ups in an interesting way
Tell me about your experience with dynamic charts using comments.
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)