11 Microsoft Excel Tricks That You Should Know About

Microsoft Excel is the most popular program that allows us to create worksheets, as well as manages and manipulate its data for multiple purposes. It can be used to create a simple table with a few rows of data, or for creating, entering, analyzing and calculating business data, for example: sales figures, commissions, or sales taxes.

Since the Excel 2010 version, Microsoft redesigned the interface of Excel and added a lot of new features. Those features will assist you in processing data better, quicker and of course, more efficiently.

If you often use Microsoft Excel, these tricks may be worthless as you might have already known. However, if you have just started using this most popular spreadsheet application, then you shouldn’t overlook these tricks.

1. Select All Cells With One Click

Which method do you use to select all Excel cells?

Press Ctrl + A?

Select all Excel cells with a single click

It’s also a quick way to select all cells. But you might not have noticed there is an icon at the top-left corner of your spreadsheet, which allows us to click and choose all rows and column as well.

2. Open Multiple Excel Files At Once

Instead of opening Excel files one by one, you can press and hold Ctrl, select all files you want to open and then press Enter. All those files will simultaneously be opened.

3. Switch Among Different Excel Files

Using your mouse to shift among different Excel files is slower than pressing Ctrl + Tab. It also helps to avoid incidents, such as working on the wrong worksheet, which can lead to serious problems, even ruin the whole project.

So, it’s better to use Ctrl + Tab to jump from one Excel file to another one. You can also use Alt + Tab to do the same thing, but this one will include all other running apps, not only Excel files.

This function not only works with Microsoft Excel. In fact, it’s a built-in keyboard shortcut in Windows, which allows you to jump among opening tabs of a specific program. For example, if you have multiple Google Chrome tabs open, you can press Ctrl + Tab to shift between them.

4. Customize Quick Access Toolbar

Do you know that Microsoft Excel has a small toolbar on the top of the window, called Quick Access? By default, it will have three functions: Save, Undo and Redo.

But you can easily customize this toolbar by adding more functions to it. There is an icon at the end of the toolbar that you can click on and select the function you want to add.

Customize Excel Quick Access Toolbar

You can also select the “More Commands…” option to view all available functions that you can add to the Quick Access toolbar.

5. Insert Multiple New Row Or Column At Once

Adding a new row or column is quite easy, right? How about adding five or ten rows (or columns)? Obviously, it’s not efficient and seems to waste time when adding one by one.

Insert multiple Excel rows or columns

To improve this, you can select multiple rows (or columns), and then right-click and choose the “Insert” option. For example, if you want to add five rows, then you just need to select five existing rows starting from the position you want to insert. Subsequently, right-click on them and select the “Insert” option from the pop up context menu.

6. Use Microsoft Excel Formulas

Do you often use Excel formulas? How many formulas do you know?

The actual power of this most popular worksheet application isn’t about creating tables with rows and columns, but how it processes a ton of big data. With its formulas, you can manage, manipulate, analyze and process data quite quickly and efficiently.

Microsoft Excel formulas

To learn more about Microsoft Excel formulas, go to the Formulas tab and find them from the Function Library group. Besides, you can click on the Insert Function icon to search for a particular formula/function.

7. Use Microsoft Excel Dashboard

This is a useful feature in Excel to help us to track metrics or key KPIs and then take an appropriate action based on it. This Dashboard contains tables, views, as well as charts, which are backed by the entered data on your spreadsheet. You can also call an Excel Dashboard as a report, but a normal report can’t be called a dashboard.

What is the different between a report and Excel Dashboard?

The fundamental difference is that the report only collects and displays data in one place. It can’t be used to view advanced data, such as how the sales have grown, or the most profitable region. The report is only for showing data, and you have to calculate those advanced requests yourself.

Fortunately, Excel Dashboard can help you solve those cases quickly, such as which regions or products you should focus on and raise the advertising budget. To learn more about Excel Dashboard, visit the following post from TrumpExcel.com.

8. Insert The 001 Or 002 Value In Excel Cell

Have you ever tried to add a number that starts with “0” in a Microsoft Excel cell? I have added this type of numbers several times before, and those zero numbers are always removed.

For example, if you input 001 or 002 into a cell, those numbers will be turned into 1 and 2. It’s a bit difficult when you actually want to add 001, 002 or even 000001, right?

Insert number that starting with zero in Microsoft Excel

However, this problem is quite easy to resolve. You only need to insert a single quote (‘) at the beginning of each number. It will help to keep all those zero numbers in your spreadsheet. For example, if you want to add 0005 to a particular cell, just type: ‘0005 instead of 0005.

That’s it!

Quite easy, right?

9. Double-click To Rename An Excel Worksheet

What would you do to change the name of a worksheet in Excel? Many users often right-click on the name of the spreadsheet at the bottom and then select the “Rename” option.

Rename Excel spreadsheet

However, you can do this more quickly by double-clicking on its name and change it to any name you want.

10. Change The Case Of Text

Do you want to transform the case of text in a specific cell?

You can use:

  • UPPER: To capitalize all characters.
  • LOWER: To change the text to all lower case.
  • PROPER: To capitalize only the first letter.

11. Transform Your Data From A Row To A Column

Do you ever want to transfer your data from a row to a column? This may be a quite hard task for users who don’t know much about Excel.

Excel transpose rows and columns

To do this task, you can copy all the data you want to transform, then move your cursor to another blank position (or to another spreadsheet). Subsequently, navigate to Home -> Paste -> Transpose.

Conclusion

I hope these essential tricks will help you understand more about Microsoft Excel and to better manage your spreadsheets. If you have any useful tricks that I have missed to list out here, share them with me by dropping a comment below. I will consider adding it to the list as soon as I read them.