How to create a roadmap in Excel? Different methods with detailed explanations. Step-by-step guide. Handy tips & visuals included.
One of the most popular searches about Excel in Google: “What are the most used Excel functions/formulas should I know?”
So if you have already searched something similar, probably you come across a lot of websites titled “100 most used formulas of excel, 200 effective excel shortcuts etc.”
But think for a minute… Would you spend all your time just for being aware of hundreds of excel tricks?
In fact, it is pretty much impossible to use every single function in your daily Excel problems. Because even if you are quite familiar with many formulas, you may still unable to use excel effectively.
In this blog post, we have compiled some practical excel tricks that are super easy to apply. It shows that Excel not only consists of many calculations and formulas but also has many quick tips to make your life/work easier.
Here are 10 useful Excel spreadsheet secrets from Someka Excel Experts!
1. 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.
2. 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.
3. 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?
Use Center Across Selection!
- First, select the areas you want to merge (B2 and C2),
- Right Click and Open Format Cells.
- Open Alignment tab
- Click on the Horizontal drop-down
- Select Center Across Selection!
4. 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.
5. Organize and format your data:
It is very easy to make mistakes in complex spreadsheets.
But you can easily create and maintain with consistently good Excel files by bringing some style and formatting that will add meaning to your data:
- Bold header texts
- Color or Underline the total amounts
- Italic Notes
So, you can display your data effectively to your clients or manager. Not just for presentations but also you will avoid hidden mistakes which is hard to find sometimes.
The few minutes that you spend here, will save your hours.
6. 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!
7. 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.
8. 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!
9. 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.
Warning! : Remove external links before saving!
In Excel workbook, sometimes, you may need to refer other worksheets to link some data. These external links create confusion while moving or copying worksheets.
Let’s say, you move your worksheet from one workbook to another but external links will refer point back to the original file. So if there is an external link, you can see them in the formula bar like this:
To avoid this, you can check whether the links are removed from your sheet in Data > Edit Links section.
10. 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!
Hope these simple Excel tricks get you on your way to a great experience with Microsoft Excel.
Already mastered these? Then check our Complete List of Things you can do With Excel article to find out more!