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.

ExcelShortcutsandMore-compressor


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 .

Picture21


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.

excel-shortcuts.png

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. 

Screenshot_1

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.

Picture16


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.

Picture23


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. 

17311171_10202895471625653_797995271938266011_o


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.

Picture10

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.

Excel Freeze Panes row and column frozen

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.

commentinsert01


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.

Screenshot_8


  • 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.

autofilternumbered08

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).

Picture19


  • 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.  

mec9905024


  • 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.

Picture20

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 hereconditional formatting here .

Picture18


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.

Picture13


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”.

select-histogram

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.

Picture3


Useful sources

Blogs to follow

Courses

Resources

Youtube channels

Picture8


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.

P.S. If you enjoyed the post, please do not forget to share it with your friends and like our FB  PAGE / subscribe to email notifications on the main page not to miss future posts.

logo1

14 Comments

Add yours →

  1. Thanks for the tips. I used Excel some at work and found it pretty useful for numbers and financial data. But it drove me crazy when colleagues tried to use it with big text boxes.

    Liked by 1 person

  2. Fantastic post with lots of great info! I appreciate the references to my videos, and would encourage anyone thinking they can’t get better at Excel to rethink that! I’m sure you will be an Excel ninja in no time 🙂

    Cheers,

    Ben – Excel Exposure

    Liked by 1 person

  3. I do not use Excel often but understand the basics. I have been on a number of courses but unless you are using systems regularly you struggle to retain the information.

    Your article breaks down the process perfectly.

    Liked by 1 person

  4. You are so right about the love/hate relationship most people have with Excel. I used to hate it until I had no choice but to learn how to use it and could see how useful it is. Great user tips!

    Liked by 1 person

  5. I honestly don’t use excel at all anymore. I used to know some basic math equations in excel, but my skills never got more advanced than that. And I don’t even remember how to do those anymore.

    If I ever have to use excel again, I’ll probably have to learn all but the basics from scratch. I’ll definitely bookmark this post as you’ve mentioned lots of things I would never have even thought of!

    Liked by 1 person

  6. Stopped using Excel a long time ago for many reasons. Mainly because I was sick and tired of Microsoft constantly asking for money and how they behave. In Sweden you, for some reason, have to pay them fior using even doc.but not in the UK and Saudi Arabia. Have excel for free through my university but am not keen on that company. The worst Microsoft did to me happened a few years ago when they crashed my computer three times becasue I was not using their search engine as my main search engine. Microsoft had to pay billions of fines to the EU court for doing so. If I can I avoid Microsoft. In other words if I can avoid Microsoft, I do.

    Liked by 1 person

  7. I use Excel a lot and consider myself reasonable proficient, but I learned a few tricks here I didn’t know. Thanks.

    Liked by 1 person

  8. When I became a Business Analyst, I had little experience with Excel.
    Once I learned how to use it, it has saved me tons of time, and increased my productivity.
    Thanks for sharing this with us.

    Like

  9. Holy smoke, do you know a lot about Excel! I frankly feel like a novice when I think about all the knowledge you just dropped on me. Thanks. I obviously have some learning to do.

    Like

  10. Sushmita Thakare Jain April 16, 2017 — 4:18 am

    Such an amazing post!
    Would love to invite you friend to Guest Blog om my website 😀
    Hope you would love to share your knowledge with my audience 🙂

    Like

  11. Robert Mcintosh April 19, 2017 — 11:30 am

    Thanks

    Like

  12. Really nice one.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: