We all like to customize things, to personalize them so that we feel better, faster and smarter using them. Microsoft Excel is a perfect example of highly customizable software. It is simple to learn and use, and at the same time it is relatively easy to change the way it works for you.
Here is a list of 15 useful excel tweaks that can make you hyper-productive. Happy Thursday 🙂
1. Change the order in which you edit cells
We all know that when you hit enter on a cell excel usually takes you to the next cell in that column for editing. But what if you need to go to next cell in that row? Of course you can use tab. But you can also customize the cell edit order when you are typing out that large list of entries so that you need not change your habits for the software. Just go to Menu > Tools > Options > Edit tab and set the “move selection after enter” to whatever direction you like.
2. Change the number of default sheets on open from three
Whenever you open a new workbook, excel shows 3 spreadsheets by default. Most of the times we need one or two of them. And when we are sharing the project plan or sales report (or whatever else the excel file has) with colleagues, we remove the other 2 sheets. You can save the trouble by telling excel to create only one sheet by default and let you add more if you need it. In Menu > Tools > Options > General Tab change the “sheets in new workbook” from 3 to 1.
3. Customize excel’s standard font to Arial to your favorite
Each one of us have our own favorite fonts. I like Verdana better than Arial. May be you like Georgia compared to Arial. But when you set out to create that gantt chart for your new project you have to manually change the font from Arial to Georgia everytime. No longer. Tell excel to change the default font from Arial to your favorite. In Menu > Tools > Options > General Tab set the “Standard font” to what you like. You can set the font size as well.
4. Hack auto-fills using custom lists
Excel has few built-in lists that it uses for auto-filling cells when you drag that little pointer across. For eg. you can write Monday in 1st cell, Tuesday in 2nd cell and select these two and drag that over the next few cells by clicking in the corner and excel would fill rest of the weekdays in that range. This is a very useful feature. But what if you do repetitive typing your company products or annual holidays ? Of course you can tell excel to use your own lists for auto-fills. Just go to Menu > Tools > Options > Custom Lists and add your own lists by typing them or pointing to a location where they are.
5. Change the colors to something bold and better
Excel (2003 and earlier) has a limitation of 56 colors. We all have been living with that for a while now. But what if you need to tweak the colors to suit your company’s color scheme without spending too much time on it. Simple. Just change the colors for the current workbook by going to Menu > Tools > Options > Color and define your own RGB values for each of the colors. Alternative you can try this hack to get more colors in your charts.
6. Configure thousands separator and decimal symbols
Excel’s number formatting is pretty intelligent. It can get your country locale information from the system you are using and thus format the numbers (the thousands separators symbol and decimal point symbol) based on that. This is a very useful feature since you dont have to worry how the numbers are shown. But what if you are in US but your reports needs to show numbers according to some other country’s format? You can change the thousands separator and decimal point symbols to suit your preference. In the Menu > Tools > Options > International Tab, uncheck the “use system separators” and enter your own.
7. Bugged with annoying error checking options? Turn them off forever
Excel’s formula error checking options are both useful and annoying. For eg. Excel would tell me if there is an “inconsistent formula in region”. Very useful feature to spot omissions. But what if you already know what you are doing and you need to omit few cells in that region in that formula? Still excel would bug you to correct that error. It may be better to turn off this error checking option that silence it every time. Go to Menu > Tools > Options > Error Checking Tab and uncheck error checking rules that you don’t want excel to apply.
8. Reduce your typing by using auto correct
We have come to rely on features like spell check, undo, auto correct so much that our productivity would drop 50% if these features were to vanish tomorrow. But do you know that you can use auto-correct feature to be even more productive? You can set your own auto correct rules in Excel (Word, PowerPoint as well) and this little tweak can help you reduce typing. For eg. while writing blog posts I often write PHD to convey Pointy Haired Dilbert. Instead of actually typing Pointy Haired Dilbert every time, I can define an auto-correct rule that says replace PHD with Pointy Haired Dilbert. Imagine how much of typing you can reduce by defining simple replacements for several day to day words you type. Define your own auto correct rules in Menu > Tools > Auto Correct Options > Auto Correct Tab.
9. Tell excel to show full menus, Always!
One of the smart features of MS Office is that the menus learn what options you use often and show only them when you click on the menu. But this gets in the way of being productive if your work involves using various options all the time. Fortunately you can turn off this feature and tell Excel (and other office apps) to show Full menus always. Just go to Menu > Tools > Customize > Options Tab and check “Always show full menus” option.
10. Reduce the workbook size by compressing pictures
Whenever you are sharing the workbooks with colleagues either through mail or by uploading to a server, it is recommended to keep the size of workbooks low in order to let the receivers quickly get the file. Sometimes excel files can be very huge depending on the number of sheets and formulas you have used. There is one trick to reduce the size of excel files with images. You can tell excel to compress the images for web viewing. This will force saving the images at lower resolution of 96dpi instead of standard 200dpi. In the Menu > File > Save As dialog box, click on “Compress pictures” option and adjust the resolution in the “change resolution” area.
11. Tell excel how to calculate your formulas
Often when you are working on spreadsheets with lots of formulas it can be a bit inconvenient to have excel recalculate every formula at each key stroke. Especially if the formulas involve a large range then excel can take quite a while to perform the calculations hogging system resources. Of course there is a way to force excel to calculate formulas when you choose to (by hitting F9). In Menu > Tools > Options > Calculation tab set the calculation to Manual or Automatic except for tables.
12. Save time by using templates
Every organization / individual has their own tastes on how a status report, project plan, gantt chart or grade table should look like. And often we spend hours touching up that spreadsheet / document to make sure it complies with standard styles / fonts / layouts. There is a simple work around that could reduce the time spent on formatting in Excel. Next time you make a project plan, save it as a template and use it whenever you need a project plan and edit the stuff you need to. Saves time for other nicer things like.. mmm, sipping coffee or reading Pointy Haired Dilbert.
13. Use paste special when copying charts to PowerPoint, it saves space as well
Ok, this is pretty common, we design charts, tables in Excel and then paste them in to PowerPoint. But somehow the formatting is not preserved or the file is too huge. Here is a simple hack that you may already know. Use paste special whenever you are pasting the charts / tables to PowerPoint / word or outlook and select “enhanced meta file” option. This makes sure you have a good quality chart that looks slick when projected (or printed) while taking up less space.
14. Remove any personal information from the spreadsheet with one click
Often when you make a spreadsheet about that vacation plan or to share party expenses and send it to others you may want to remove your personal information from the excel file, just to make sure the file is harmless even if it is posted on the cloud. In Menu > Tools > Options > Security tab, check the “Remove personal information from file properties on save” to make sure your company name, last name etc. are removed from the excel file properties.
15. Bored with Excel menus? Create your own
Finally, how would you like your own menu with your own shortcuts ? This can be super-productive if you spreadsheet a lot and need to access all those nifty features with one click. Here is a clue on how to get your own menus. Go to Menu > Tools > Customize > Commands Tab and add your menu to the list. How? That is your home work. After all you are a spreadsheet customization guru now.
That is all. So go ahead and tweak that spreadsheet software and have fun in all the time you have just saved.
Note: all tips are tested on MS Excel 2003. Let me know if you face any difficulties.
32 Responses to “15 MS Excel Tips to Make you a Productivity Guru”
great site, splly useful for free bingo tickets i was looking for all over!
thanks a lot.
Thanks a lot for this site. One of my goals is to become an Excel Ninja and this site is a cool source of support in that process.
Number 11: Couldn't disagree more -- inevitably, you'll forget to recalculate, and you (or someone else) will unknowingly read an old value from your spreadsheet and use it to make a decision. If your worksheet has so many formulas that you need to put it in manual, you need to rethink your worksheet design.
what if it is a sheet with bloomberg-linked formulae and you want to open it on a regular desktop just to check a value or two? Manual is pretty helpful then...
I've seen models so large this advice was well-heeded.
In one case recalculating took upwards of 15 seconds and would have been horrible to work in on automatic mode.
That said, none of my models are so large and I leave them in auto mode.
A good list, mostly.
1. I don't move the active cell on Enter. I rely on the arrow keys if I want to move (you don't need to press Enter).
2. Good one. Especially since it's as easy as clicking Shift+F11 to insert a new sheet.
6. You're probably better off using the system settings.
9. Also, check "Show Standard and Formatting toolbars on two rows".
13. It makes smaller PowerPoint files, because each pasted chart really contains the entire parent workbook. This increases file size, and risks transmitting proprietary information.
🙁 I immediately wanted to try some of your tips, but realized you weren't on Excel 2007... Can't find the actual paths to menus you talk about. Would that be too much of a pain to also post 2007 equivalents?
with Excel 2007, click on the Office button on the top left corner of Excel and on Excel Options.
This will bring up the Excel Options dialog box where you can find most of the settings Chandoo described in this post.
@Raj, Kyle: thanks for the words. I am happy you find my site useful 🙂
@JP: Thanks for raising this. hmm, I agree that turning off auto calculation will impact if you are forgetful. But I have used it for good reasons when there are vlookups over large ranges, formulas with references to closed workbooks etc. to make sure my system isn't hanging trying to calculate. Of course in those cases the calculations were mostly one time and hence I could just save the values for future.
1. Another thing you can do is, select the range of cells in which you want to enter data and start typing in cell 1 and when you hit enter excel would move pointer to next cell in the selection irrespective of direction.
6. Agree, But this is for the rare cases 😀
9. That is a good one.
13. You are right, just copy pasting a chart is huge waste of memory and takes up time
@Jacques : I am sorry to disappoint you. As noted most of these are tested in Excel 2003, I have very little exposure to ribbon. But as Robert says most of them are still valid in 2007, just that they are tucked away somewhere.
@Robert: Thanks 🙂
I use VLOOKUP for quick and dirty stuff, but have largely switched to INDEX/MATCH, especially when you have to fill down the formula over many rows, the speed increase is very noticeable.
Great love PHD,
Use "google sets" with 'Custome lists' to Autofill your imagination to hearts content
[...] 15 MS Excel Tips to Make you a Productivity Guru [16 October] [...]
Hi Chandoo / Other Readers,
Was trying to build a Custom Menu in Office 2007.
Realized that the process defined here was applicable to Office 2003. Did some search in Excel Help -> No results. Did some Googling -> Appears this is not easily possible in Excel 2007.
Can someone please confirm if Custom Menus are possible in Excel 2007.
Ron de Bruin offers a lot of useful information about customizing the Ribbon in Excel 2007:
Robert, thanks for the info. Will check out Ron's page.
[...] menu > tools > options > error checking tab and disable errors you don’t want to see… Get Full Tip 54. To change the shape of cell comments from rectangle to some other symbol, select the comment, [...]
its a very good effort rom ur side .ther are a lot of prople that r not able to use execel .these tips are very usefull for them .excel is very inmportent in this era. you are serveing .mayGOD give you reward in shape of progress
@Sadiq: Thank you very much for the kind words. And a warm welcome to PHD blog. Hope you will find more and more useful stuff here...
[...] can also customize excel lists so that you can auto-fill, lets say bank holidays in your country or types of beer in your pub. One [...]
[...] Excel productivity secrets - part 1 & part [...]
Good site to hang on for excel tips. PHD addiction is not bad i think..
Save a lot of time selecting the chart to be copied in PowerPoint, then press+hold [Shift], then click [Edit], and Select [Copy Image] (I'm guessing since my Excel is spanish version), continue with one or two dialog options...
At this moment the chart is kept in the clipboard as an image, JPG or BMP, depending on your previus selection.
Really nice site Chandoo, I really enjoy learning new Excel tricks.
When I use copy & paste special to paste numbers from Excel into Word the pasted number occasionally has an addtional space at the end of the number that messes up formatting when ever the linked value is updated. For example if the lionked number is at the end of the sentence, their will be a space between the number and the period. Do you why this happens and how to correct it?
[...] 15 Excel Productivity Tips & Tweaks [...]
New to the computer, but like #9 shortcut to insert a new sheet.
new to high tech. computer stuff but i like #9 shortcut to insert a new sheet.
Anyone able to create own menu in Excel 2007? If yes, can you share the trick?
Thanks in advance
in the excel filter why not taken the more than 10000 datas how i am take more than 10000 raws data please help to me !!
You can take as many rows as you want
Its just that you will see Excel slow down the more rows you take
I HAVE 9 SHEETS IN MY EXCEL WORK BOOK..........IS THERE ANY SHORTCUT TO SWITCH THEM,,,,,,,LIKE
F1 F2 F3 F4 F5
Some great Tips.
Ctrl & Pge Up/Pge Dwn. Quick and dirty and 9 sheets is a huge amount.
Also Custom lists are available 2007. Excel Options > Edit Custom Lists (Alt +f, i, Alt +o)
very useful tips thank you