8 tricks to boost Excel productivity

It does not matter if you are working in finance, supply chain, marketing or project management, being proficient in Excel is no longer regarded as something impressive. Otherwise, it is considered as a must-have skill without which you will be not efficient at work.

It is probably true that most people have a love/hate relationship with Excel. I do believe that working in a smart way and discovering the huge range of Excel functionalities will make your life much easier. In this post, I would like to highlight some of the most useful Excel tips and tricks that should be known by every office worker.

---

1. Do not reinvent the wheel

Everything that you are trying to do in Excel most likely has already been done before. Therefore, the golden rule of saving time is not to reinvent the wheel – just make the most use out of what is available.

To begin with, explore the wide selection of original excel templates. Instead of opening a blank workbook as usual, check out other options: you will be surprised to find great frameworks to start with a Business Plan/Profit and Loss analysis, to track your projects/financial expenses and even to plan your vacation. I find this feature especially useful when you need to build high-level graphs and diagrams like gantt / waterfall / Gauge charts. If you can not find what you are looking for in the Office, you can always look for it online using a search engine embedded in Excel.

If you still did not succeed, take a look at available third-party project management templates. Probably the best ones are TidyForm and Vertex42 .

---

2. Keyboard is more than enough

The thing that actually differentiates “great” Excel users from the “good” ones is that first ones navigate around the file using only a keyboard. Maximum that they can afford is to use a tiny pointing stick on the keyboard from time to time, but never a mouse. It might sound surprising or unbelievable at first, but actually, this allows you to save a lot of precious time. Therefore, you should totally aim to achieve this level of proficiency.

First of all, it is paramount to learn how to use shortcuts. We are not talking about Ctrl C/Ctrl V level. There are plenty of other useful shortcuts that you probably do not use that often: Ctrl+A for selecting the entire worksheet, Shift+spacebar to select an entire row and Ctrl+spacebar to select an entire column, Ctrl-Shift-Arrow to select the entire data column/raw, Cntrl+D to fill in the selected column/raw with the data. Try to navigate around your document only using shortcuts: Cntrl+Arrow to move around the worksheet, Ctrl PgUp/PgDown to switch between worksheet tabs. You can also go to the navigation panel simply by pressing ALT and then switch tabs using arrows. Moreover, it shows you the letters to use for each function shortcut, which makes it easier to learn. 

Find a list of all the shortcuts online (here), print it and have it always in front of you. Once you practice a new shortcut a few times, you’ll wonder how you ever lived without knowing it. Also, you can try a custom keyboard or stickers for learning shortcuts.

---

3. Get the data no matter how

Learning how to import data can also greatly speed up your workflow. If you come across a website with a lot of data that you might need, do not hesitate to convert it into a worksheet. Simply click File > Import External Data and create New Web Query. Choose the web page you want to display and copy – paste the link into the Address box. The direct data import can be very handy when you can not download the data in excel format easily straight from the website.

---

4. Do not do a monkey job

Sometimes you probably have to deal with unformatted data, which requires you to spend a great amount of time preparing it for further analysis. Make sure to get familiar with simple Excel functions that can easily perform all the work for you. For example, TRIM to remove extra spaces in your text, LEFT/MID/RIGHT/LEN to adjust the length of the text in the string, CONCATENATE to join multiple strings of text into a single cell, TEXT TO COLUMN feature to separate fixed-width text into multiple columns and FIND/REPLACE function to make all replacements at once. 

---

5. Optimize your file

Have you ever got lost in your huge Excel file? Probably, yes. When you work with a large amount of data, make sure that you manage your workflow efficiently and focus on what is important at the moment. For example, if you do not use several spreadsheets right now – hide them (select them all holding CNTRL and ALT + O + H + H; to unhide them use ALT + O + H + U). The same applies to unnecessary raws and columns: group and hide your selected ranges using SHIfT+ALT+Right (to unhide SHIfT+ALT+Left).

If you want to copy/paste something and there are some hidden strings/columns in between, it might look like you are in trouble. However, if you use copy-paste in a special way (Ctrl+C > Alt+E+S+V), it will allow you to copy/paste visible cells only. Paste Special function is used quite often – you can also choose between copying formulas, values, formats, column widths or even transposing the data set.

When you work on a document with multiple worksheets, very often you need to compare the content of different sheets. Actually, quite a few people are aware of the option to do this automatically: selecting “new window” in the View tab and then clicking on “Arrange All>Tiled”, which allows you to tile the sheets side by side.

When your data table is too long, you can easily find yourself on row 1000 of your spreadsheet only to realize that you can’t remember what the values in this columns demonstrate. Does it sound familiar? The answer to this common problem is to lock or freeze your header row/column, so as you scroll down, the headers will always be visible. You can do it on the control panel or with a shortcut Alt+W+F+F/R/S.

If several people are working on the same document, it might be also very helpful to use a review function, especially add comments to specific cells. For this purpose, you can use the document validation option available on the navigation panel or simply insert a new comment with a shortcut Shift+F2.

---

6. Deep Dive into data analysis

• AutoCorrect & AutoFill

In order to save time when working in Excel, you need to learn how to type as less as possible. There are two ways to do this: AutoFill and AutoCorrect. AutoCorrect is a feature that automatically corrects misspelled words and typos. To enable AutoCorrect, just click the tools tab and tick autocorrect. AutoFill is especially helpful when you are trying to type in a numbered list manually or applying the same formula to each cell. To activate it, go to the Edit menu, point to Fill and click Series. If your formula refers to a constant cell/raw/column, do not forget to add a dollar sign to this reference to keep it constant.

---

• Filtering

Using filters in excel is extremely helpful. Inbuilt AutoFilter can handle plenty of different scenarios for text, numbers and dates. Plenty, but certainly not all! When a regular AutoFilter function is not able to perform what you want, it is time to check Advanced Filter – the function that sets the filtering criteria exactly suited to your requests. Excel’s Advanced Filter is especially helpful when it comes to finding data that meets two or more complex criteria such as filtering rows that match items in another list, extracting matches and differences between two columns or finding exact matches.

Now you can also add slicers and timeline to your filtered tables, which makes it more user-friendly to navigate around the spreadsheet. To add a slicer to a table, head to the Design ribbon, select Insert Slicer and then choose which column(s) you’d like to filter.

If you want to learn more on filtering, check out free video lesson from Ben, Excel exposure here.

---

• PivotTable

One of the most important skills you have to master in Excel is a powerful tool called “PivotTable”.  PivotTable is accessible under the “Insert” menu. PivotTable tool analyzes the data and brings it into a new sheet where you can manipulate it in various ways.

If you want to learn how to build pivot tables and how to work with them, check out free video lesson from Ben, excel exposure: introduction – here , advanced (1 & 2).

---

• Vlookup & Index Match

The functions that are used quite often to build dynamic tables are: Vlookup, Index, Match. VLOOKUP finds data in one Excel table and automatically inserts it into another. The key difference between INDEX MATCH and VLOOKUP is that VLOOKUP requires a static column reference while INDEX MATCH uses a dynamic column reference, therefore INDEX combined with MATCH function can be more powerful and flexible. 

If you want to learn how these functions work, check out free video lesson from Ben, excel exposure here.  

---

• Conditional statement

Excel has a lot of functionality regarding conditional qualifiers. For example, one of the most commonly used – IF statement – allows the cell to contain content based on the validation of previous entries. This function may be used in the simple IF(if this condition is true, then return this, or else do this) format of usage, but also other ways for more complex conditions and even for multiple conditions. If you want to perform other operations using these conditions, consider the function sumIF that adds up the sum of cells that meet a certain criteria. For example, the sum of all the salaries that were greater than $100,000.

To make your analysis visual, you could try to apply conditional formatting. It can also show the duplicate values.

If you want to learn how these functions work, check out free video lesson from Ben, excel exposure: logical functions here; conditional formatting here .

---

7. Visualize the data properly

If you want to present your data nicely in the form of graphs, charts and diagrams, definitely you need to know the “Align or distribute” Excel function. Do not manually change your graphs one by one – select them all and work with a format function for the entire group. For instance, to align two charts, first select one chart, hold the Shift/Cntrl key down and then select the second chart to align, choose the Format Ribbon -> Align -> Arrange group and then pick your alignment type.

---

8. Explore Add-Ins

For those, who are just starting with Excel: add-ins are mini software applications that can be install into Excel to provide functionality that is not available within the standalone Excel program. Add-ins work like the apps that you can download and purchase for your smartphone. Some of them are developed by Microsoft, others – by third parties. Most known original Excel free add-ins are: Analysis ToolPack that allows statistical analysis. Solver that can be used for what-if/optimization analysis, Table Analysis Tools and Data Mining Client – for data mining, Power Query to help access and explore data in Excel like with Business Intelligence tools, PowerPivot – that could be described as “Microsoft Access for dummies”.

Other interesting Add-ins: Exchange rates – to convert currencies with with real-time rates from the Internet, Asap Utilities to be more productive while using Excel,  Excel Stock Market Functions – to add new user-defined functions that can be used to retrieve stock markets data from the web or Bing Map  or Geographic Heat Map add-in to plot location data from your worksheet onto a Bing Map, providing basic location based data visualization.

You can find more add-ins here. However, keep in mind that several active add-ins decrease the speed of Excel and you should not overload it with unnecessary apps.

---

Useful sources

Blogs to follow

• Lesson Guide | Free Excel Training!

Courses

• Top 10 Excel Skills For Successful Business Professionals | Udemy
• Improve Your Microsoft Excel Skills – Learning Path
• F1F9 | Home
• Microsoft Excel School – Online Advanced Excel & Dashboard Classes, Excel Training Program | Chandoo.org – Learn Microsoft Excel Online
• http://www.skilledup.com/articles/courses-learn-excel-online
• http://www.skilledup.com/articles/free-excel-tutorials

Resources

• MrExcel Message Board
• http://excelexposure.com/
• http://www.excel-easy.com/
• Excel add-ins and Outlook tools – Ablebits.com

Youtube channels

• Contextures Inc. – YouTube
• Dinesh Kumar Takyar – YouTube
• TeachExcel – YouTube
• ExcelIsFun – YouTube
• Bill Jelen – YouTube

---

There are many other advanced things that this post doesn’t cover at all: starting from VBA and macros to advanced charting & amazing integrations with PowerBI. Hopefully, we will leave it for another time. I hope you find this post useful anyway and from now on endless Excel worksheets will seem less scary.

If you are interested, you can subscribe to the email newsletters at the end of the HOME page and like our FB  PAGE not to miss future posts.  Follow Resultize on INSTAGRAM to receive daily updates about productivity, self-development & learning.  Inviting your friends is also highly appreciated 🙂

---