Excel tips
Excel ≠ Repetitive work!
You don’t have to be an Excel expert, but keep in mind that Excel always has an easy way for you. If you start to repeat the same things in a workbook always remember that there should be an easy way in Excel to do less repetitive work!
Save regularly
When you see this headline you probably say: “Yeah, yeah I know” Alright, now think about it: How many times have your excel files unexpectedly closed? Then you suddenly noticed that all the works you’ve done before it closed, has gone! Or have you ever lost your existing file because you had already overwritten by clicking the Save button? Is that nod for a “Yes”? So, it’s better to create a backup copy for every change in your data if you want to look at the previous version. Just click on “Save as…” and create different versions for yourself like: MySuperFile_V1, MySuperFile_V2 etc.
Learn Commonly Used Excel Formulas
There is no standard for “most-used formulas” in general. However, some Excel functions are essential to learning in every business to compile data. That’s why you‘d better learn at least 20-30 commonly used excel formulas. Even if you don’t use and need practice on it, at least you’ll know that there is a quick formula you can learn in a few minutes. By the way, if you need to improve your excel skills by practicing, our Excel Formulas Training Kits will guide you with a lot of formulas and instructions!
By the way, if you need to improve your excel skills by practicing, our Excel Formulas Training Kit will guide you with a lot of formulas and instructions!
F4 key: Keep variables constant!
F4 key makes it easier to drag formulas without losing the reference to a particular cell. Instead of writing variables into cells manually, you should give reference to constant. To do this, you’ll need to add a dollar sign before the row and column with the F4 Key as shown above.
Freeze Headers for Scrolling
The breaking point while working on a spreadsheet with a massive data set, is scrolling in any direction and trying to find that specific row or column you want. Users tired of this situation found a simple excel trick to deal with it. The trick is freezing a header row or column so that when you scroll, it doesn’t move. And below is how you do it:
- Go to the View tab and look for Freeze Panes
- You will find options like:
- If you want to do both select Freeze Panes
- If you changed your mind, select Unfreeze Pane – to reverse the freezing
Learn Pivot Tables
Probably you would say “It is just one feature of Excel. I am not doing data analysis, then Pivot Tables are useless to me.” But undoubtedly Pivot Tables allows you to summarize a set of data – no matter what size in a meaningful way. It can automatically sort, count, total or average the data stored in a spreadsheet and brighten results in a nice looking sheet.
Here is how it looks like in our Sales Dashboard Template.
The dashboard charts present background data thanks to Pivot tables!
Don’t start at A1!
Alright, it doesn’t sound like an “excel trick”, but some people create and share spreadsheets that have no content in column A and no content in row 1. They always start the content in B2. Because it feels nice to have some blank space to play with if necessary. You can also use this space as a helper column to perform a lookup to the data and save your time without needing to insert new rows or columns.
Don’t Merge Cells
This is something you hear often from most of the Excel experts. Probably you use merge cells to make your spreadsheet look nicer. But it can cause lots of problems with sorting and moving data. Some functions do not work correctly if the cells are merged. Or when you try to sort a data set with merged cells, excel won’t allow you to do that. That being said don’t use merge cells because the functionality is not less important than its look. So, what is an alternative solution?
Freeze Panes
Suppose you have a huge data titled nearly 50 subjects stored in an Excel sheet. In order not to lose track, freezing panes is the greatest way to keep the names insight as you scroll across the sheet. It can be applied to the worksheet in 3 different ways:
- Freeze panes
- Freeze top row
- Freeze 1st column. You can find all these 3 options in the View tab, and then by choosing Freeze option in the tools ribbon.
Drop the mouse!
In daily work life, you may not notice that it really takes time to move your hand between the keyboard and the mouse. Even if using a mouse seems practical to handle easy work, the keyboard shortcuts make everything faster for navigation. Whenever you need to use your mouse in excel, stop and find out a shortcut on how to do it with the keyboard.
