It is very easy to customize Excel ribbon and save time. You can make a new ribbon or modify an existing one with new group of commands. This can be a huge productivity boost for people using MS Office applications.
How to create your own ribbon in Excel 2019 / 365 / 2016 / 2013 / 2010:
Customizing ribbon is as simple as customizing your coffee at Starbucks.
- Right click on ribbon area and select “customize ribbon” option.
- Now, add a new tab (or group or both) – see below for illustration.
- Add a few commands (or buttons) to your new ribbon
- Click ok and you have a sparkling new ribbon ready.
10 things you should know about ribbon customization
This is how the customize Excel ribbon screen looks.
I have highlighted 10 items on the screen. Read thru below 10 points to master ribbon customization.
- Use New Tab button to create a new ribbon tab.
- Use New Group button to add a new group of commands to an existing or new ribbon.
- Rename button helps you to change the name of an existing custom group or tab.
- Once you add a group / tab, you have to select it to add items to that group / tab.
- You can choose the type of commands you want to add to your ribbon tab / group. You can also add any macros as well (sweet!).
- Now select the command you want to add to your group
- Click on “Add” button to add the command to your ribbon tab / group.
- You can use “Remove” button to remove any commands from custom tabs / groups.
- Use the up / down arrow buttons to move your ribbon tab / group up or down. (For eg. you can move your custom tab to first, ie before home tab).
- You can export your ribbon customizations and re-use them in other computers (both ribbon and QAT settings will be exported).
Ribbon and QAT Customization – Few Tips:
Use “Hide Command Labels” option to shrink your ribbon groups
See the below illustration to understand what I mean.
Customize tool ribbon tabs to save a ton of time:
By default, when you go to “customize ribbon” screen, you only see main tabs. But you can also customize tool specific tabs. For eg. I use only a handful of chart formatting options and all of these are spread across 3 different tabs – design, layout and format. So I combined all the options I use regularly to come up with a simple ribbon tab like this:
As you can guess, the above ribbon tab appears only when I am formatting a chart.
Add groups of commands to QAT:
You can now add a group of commands (for eg. all alignment options) to Quick Access Toolbar to improve your productivity.
Minimize ribbon with a click:
Press the ^ icon you see next to help icon to instantly collapse / expand ribbon. You can also use CTRL+F1 keyboard shortcut to do the same.
Export Ribbon Settings
In 2010 and later you can Export your Ribbon & QAT to a file that can be imported to another computer, or after reinstalling Office
In the Options dialog > Customize Ribbon (or Quick Access Toolbar) options > Import / Export button at bottom of both dialogs.
Ribbon Customization Gotchas!
While ribbon customization is a great move ahead for Excel in particular and Office apps in general, there are a few gotchas. Beware of the following to avoid un-necessary troubles.
- When you add a group or tab, excel doesnt ask you for a name. Make sure you click on “rename” button to change the name to something you remember.
- You cannot add commands to an existing excel defined group. You can however add groups to existing ribbons.
- Even if you try to make a group with exactly same commands, the group may look different.
- The ribbon and QAT customizations you do are local to your installation of excel only. You have to export the customizations and import them before they work on other comps.
What is your opinion about ribbon customization?
I am very happy to see the possibilities of ribbon customizations. It can improve productivity and simplify a lot of things.
What about you? How are you planning to customize your ribbon? What tips and ideas you have to share with us? Please tell me 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)