Nowadays, a lot of people use spreadsheets to create and manipulate data quickly. It helps to calculate those numbers quicker with built-in formulas. These skills are also asked when you apply for some specific jobs, mostly jobs in the office. However, if you don’t know about how to use worksheets or know a little bit of it, you will waste a lot of time to do tasks that can be done quicker.
Don’t worry! These five essential Microsoft Excel tricks will make you feel much more proficient at creating, managing and manipulating data, charts, lists on your spreadsheets.
1. Use Keyboard Shortcuts
If you want to use Microsoft Excel quickly, you need to learn its keyboard shortcuts. Gradually, you will use them instead of your mouse, which helps to do tasks quite quicker and more efficiently. So, if you want to master this most popular spreadsheet application, stop using your mouse.
There is an application called KeyRocket that will allow you to track your mouse when using Excel. Then lets you know whether there are any keyboard shortcuts you could use to replace those mouse activities.
2. Replace VLOOKUP With INDEX MATCH
If you often use Excel, you might have known about the VLOOKUP function and used it a few times. It’s used to search for and move data between tables. However, you will need more processing power to do tasks with VLOOKUP. Instead, you can use INDEX & MATCH functions together to replace VLOOKUP.
More detailed, you can use the INDEX function to get a returned value based on a selected column and row. Its syntax will be:
=INDEX(array, row)
Meanwhile, the MATCH function will return a value based on the relative location of a lookup value in a specified column. Its syntax will be:
=MATCH(lookup value, lookup array, match type)
When combined, with the following syntax, you will get the same value when using VLOOKUP.
=INDEX(array, MATCH formula)
For more useful Excel tips or details on Excel functions, visit TrumpExcel.com.
3. Use MMULT Function To Multiply Matrices
It’s simpler to multiply matrices values than you may think. That said, you only need to choose the cell where you would like to show your matrix product. Then fill in the following syntax to the function box:
MMULT(Array1, Array2)
Subsequently, press Ctrl + Shift + Enter to generate the result.
That’s it!
Quite simple, right?
4. Hide All Error Cells On Your Worksheet When Printing
If you are in a hurry and don’t have much time to fix error cells, then there is a way to hide those errors before a staff meeting, a presentation, or printing out.
To get started, select the Page Layout tab, and choose the expand icon in the Page Setup group, to open the Page Setup window.
Switch to the Sheet tab, and then change the value of “Cell errors as” field to “<blank>”. It will replace all error cells on your spreadsheet with empty cells.
5. Format Dates With VBA Function
If you have used TEXT function before, you will know that it can convert a row or column of dates on your worksheet to months. However, you can’t convert those data to weeks or quarters.
But don’t worry! You can do that by using VBA (Visual Basic for Applications). To get started, press Alt + F11, and navigate to Insert -> Module.
Subsequently, enter the following code into the editor:
Function MyFormat(Cell Number, “FormatCode”)
MyFormat = Format(Cell Number, “FormatCode”)
End Function
For instance, if you want to convert data in the cell B1 to week, then type:
MyFormat(A1,”WW”)
or
MyFormat(A1, “Q”)
to convert your date to quarter.
If you want to convert more cells, press Ctrl + Enter to execute this function to the whole column.
Conclusion
We hope these useful tips will help you manage your worksheet better and use Microsoft Excel more efficiently. Nevertheless, you need to use those features and practice regularly to remember them.
Besides, many other potential tricks are waiting for you to discover. So, to become a power user, keep finding more tips and learn them.
