10 Tips to speed up your Excel work

Microsoft has indeed done wonders in creating and continuing the legacy of MS-Excel. At most companies it is the primary and at some places it is the only tool used to manage, process and analyse data

I have some awesome tricks for you today to speed up your work on MS Excel. Sit back with a cup of coffee for a while and enjoy. Your time once invested is going to save you a lot of time in future, I promise!

Universal Speed Building Techniques

1. Use Key Board (shortcuts) over Mouse

People waste most amount of time by switching between the keyboard and the mouse. You can speed up your navigation, selection and formatting simply by using key board shortcuts. Excel has a key board shortcut for almost everything, from running a macro to even deleting a cell. I duly understand that it is hard to remember all the shortcuts so just to make your life simpler you can start with the list of my top 25 key board shortcuts (downloadable file)

2. Use the Quick Access Toolbar

It is very uncommon to see people using the quick access toolbar. Let's start from locating its position on your Excel screen. It is placed on the extreme top left corner of the screen and by default it gives options to Save, Undo and Redo (but these options are less helpful). There is a lot more you can do by customizing this bar. So lets do it!

1. Click on little drop down arrow
2. Then click on 'More Commands' to open 'Excel Options Window'
3. Simply pick and drop the options that you want in the quick access toolbar
4. All the options that you add will be available to you at a single mouse click, so no more wandering inside the ribbon!

I made a short video on it explaining how can you do this in PowerPoint. Well the process remains the same in Excel too, so I thought you might want to take a look [Quick Access Toolbar Customization - Video]

Speed up writing Formulas

3. Master Cell Referencing - for speeding up formulas

You just need not to be good at it but be a PRO at it ..why? because proper cell referencing allows to you write robust formulas across your data and then copy them around with correct results each time. I think It would be unjust for me to skim through this important topic, so I am leaving a link to my blog where I recently wrote a detailed post on Cell referencing + some tricks Enjoy!

4. Use Cell Naming

Cell naming comes extremely handy while writing long formulas with ease plus there are unimaginable (formula based) applications of cell naming. Yet another important topic should receive its due respect, here is a link to learn cell naming

5 Auto-complete formulas

We are half way through and this one is short and interesting..

When you are typing down a long formula lets say =WORKDAY.INTL or =VLOOKUP, excel gives you suggestions based on what you are typing to select the suggestion use the TAB key, it will auto complete your formulas

Speed up your Charts

6. The Indispensable Ctrl + 1 Shortcut

I use this a lot when formatting the Charts. Heard of one remedy for whole lot of problems.. this is exactly that stuff.

When you are trying to format the chart let's say the axis or the data series and you need to access the formatting options, simply select the chart element and press CTRL + 1 it will open up formatting options of that element. Read this quicky here Ctrl +1 shortcut in charts

7. Create your chart templates

If you have a particular style of chart formatting, I would suggest that save that style as a template by clicking on Save as Template in Design Tab (only appears when you select the chart). Do not change the default location where the templates are saved!!

The next time when you select the Data to draw a chart

1. Go to Insert Tab and click on any chart type and choose the option All Chart Types
2. You'll notice Templates (top left corner)
3. Choose the template that you had previously saved
4. You can save as many templates as you want and save as much time :-D

Speed up Data Management

8. The New Window Tool

Has it ever happened that you are working on a workbook which has 20-25 sheets and you continuously have to refer to Sheet 1 and Sheet 20. I know its like one heck of a job to flip so many sheets. Try the New Window Option (placed in the View Tab)

It will open a mirror image of the same file you have been working on. The benefit is you can select Sheet 1 on image and Sheet 20 on the other image and switch between the windows with Alt Tab shortcuts.. You'll love it.. and you are welcome!!

9. Excel Tables

This is like the craziest thing I have ever explored..

1. Select your entire data (for eg Employee Name, Joining Date, CTC, Designation etc) and press Ctrl + T to activate table feature
2. The Tables Dialogue Box will ask you about Header in the Data (Select the option) and click Ok
3. The formatting will automatically change to Table formatting (but the changes are not restricted to formatting)

What can you do with tables - A short feature

• If you want to take a total (use Alt = to for totals) of any column. The table feature will automatically add a filter (in the last row of totals) for changing the operations (sum, average, count etc)
• The totals at the end of the column work according to the filter criteria that means that it will only total up the visible rows
• Also any new data added to the contiguous rows or columns automatically becomes a part of the table
• Since the tables are self updating (when new data is added) you can also reference tables while making pivot tables, charts etc. This will automatically update the charts and pivot (after refreshing) when new data is added

10. Auto Save Options

Jonny Jonny, did you save your sheet?

Auto saving options can come to rescue when Jonny forgets to save his file. Auto save options are located here..

• Excel 2007 - Click the Microsoft button on the top left then choose 'Options' at the bottom right
• Excel 2010 & 2013 - Click on FILE tab and choose 'Options' from the menu

Under the Save tab you can adjust for the auto save time and can also locate the path of the auto recover version of your file

--------

These were my top 10 time saving techniques, I hope you found them useful. If you wish to read more on Excel and PowerPoint, you can visit my blog www.goodly.co.in I do consulting assignments and training programs on Excel and PowerPoint for companies. I can be reached at chandeep.chhabra@gmail.com