Microsoft Excel is one of the most important tools that is being used in almost every office setup. From managing data to extracting valuable insights from that data, Excel offers you a complete solution to all of your data-oriented problems. By learning all the latest features of Excel, you can give your productivity a real boost!
It has been observed widely that in almost every business environment, about 10% of our time is spent working on Excel. Here are the 5 ways to improve your Excel in 2022 if you don’t have the budget for an advanced Excel training course.
Let’s learn and use them throughout the year to become a pro!
1. Dynamic Arrays
Dynamic arrays are one of the most important updates that Excel has got in recent times. It saves a lot of time when you use formulas that can give you multiple outputs. You can get multiple results by using a single formula.
Let’s understand this principle with an example of the LEN function:
The LEN function lets you calculate the number of characters in a text. In the above example; cell B3:B7 contains the name of colors.
In cell C3, the LEN function was used but it referenced the whole range (C3 to C7) at once. The Excel formula engine identified the range and generated multiple results.
Also read: 5 The Best Ways To Recover Data On A Windows OS Computer
Remember that the dynamic arrays are available in Excel 365 and Excel 2021.
The older version of Excel does not support the dynamic array functionality. If you type the same function in Excel 2019, you will only get the value for the first cell that is B3 and not the entire range.
2. XLOOKUP
If you have worked on multiple sheets to find values and aggregate them into one sheet, then you would have used the Excel lookup functions like VLOOKUP, HLOOKUP, and LOOKUP. However, there are some limitations with these functions.
To address these limitations, Excel has introduced XLOOKUP in Excel 365.
XLOOKUP is a much more versatile and flexible version than older functions and works with dynamic arrays.
Some of the major advantages of XLOOKUP are:
- It can be used to lookup values from either side (right or left)
- It can be used with vertical and horizontal data
- It can be used to return entire columns or rows
- It can be used to lookup values in reverse order (from last to first)
Let’s see a simple example for using XLOOKUP;
3. Co-Authoring
3. Co-AuthoringWhen working in a collaborative environment, there often comes a situation where two or more persons are working on the same workbook. It’s a complete waste of time sending emails back and forth after making every adjustment to let people they can now go into the workbook.
This whole process itself tends to eat up a lot of time and increases the complexity of managing the last saved workbook.
To address this issue, Excel 365 has introduced the co-authoring feature that lets you share your workbook with your teammates and you can all work on it at the same time. in case some of your coworkers accidentally delete the file, forget to save it, or overwrite something, there are still a few ways to recover your Excel file.
Some of the advantages of the co-authoring feature are:
- All the members can work on the sheet at the same time
- Members can add comments within the workbook for proposed changes
- Members can reply to the comments within the workbook
To share your workbook with other teammates, first, you need to save your workbook to OneDrive or Microsoft SharePoint. After saving your workbook to the cloud server, you can share it with your team by clicking on the SHARE button at the top right corner of the Excel panel.
After clicking the SHARE button, you will be asked to provide the email of the person(s) with whom you want to share the workbook. By entering the email address, the person will be able to access the link to the workbook and can start working on it.
4. Freeze Panes
Working with large datasets that contain hundreds of rows and several columns can be a bit complicated. Sometimes, the datasets are so large that they do not fit your display screen.
When working with such big spreadsheets, it even becomes difficult to remember the column header while editing the workbook. The Excel FREEZE PANES option is perfect for this situation.
Also read: 14 Tips to Make Signing Electronic Documents Easier
FREEZE PANES lets you lock the rows or columns or both to make them visible on top even when you scroll down or towards the right of the workbook. This way, you will always be able to see the top section for ease in navigating throughout.
You can access the FREEZE PANES feature from the VIEW ribbon.
By clicking on the FREEZE PANES you will get a drop-down menu containing three options:
- Freeze Panes – Locks the column(s) to the left and row(s) above the selected cell
- Freeze Top Row – Locks the first row
- Freeze First Column – Locks the first column
5. Shortcut Keys
Using shortcut keys to do repetitive tasks greatly increases your working speed while using Excel.
Almost every Excel user would benefit from learning some more shortcut keys.
Excel offers you a good range of shortcut keys that is helpful when working on tasks involving repetitive operations like copying from one place and pasting to another place.
Following is a list of the most frequently used keyboard shortcuts;
- CTRL + DOWN: Navigate to the bottom edge of the data region
- CTRL + UP: Navigate to the topmost edge of the data region
- CTRL + RIGHT: Navigate to the right edge of the data region
- CTRL + LEFT: Navigate to the left edge of the data region
- CTRL + Z: Undo last action
- CTRL + Y: Redo the previous action
- CTRL + O: Open workbook
- CTRL + S: Save the workbook
- CTRL + N: Create a new workbook
- CTRL + C: Copy the selected cell(s)
- CTRL +X: Cut selected cell(s)
- CTRL + V: Paste the copied/cut cell
- CTRL + ALT + V: Open the paste special dialog box
- CTRL + T: Insert table
- F4: Repeats the last action
There are several other useful keyboard shortcuts as well that can make you superfast in Excel. This article shows some more Excel shortcuts.
Learning and practicing these shortcuts will increase your Excel productivity by leaps!
By utilizing the ways mentioned above, you can make the most of Excel in your professional life as well as in your personal life. Since Microsoft believes in continuous improvement, it is always a good idea to stay informed with all the latest updates for Excel.
Luckily, doing so is much easier now than before with a plethora of Excel training programs available online.
Make your new year more productive by learning new features of Excel. Happy learning!
Leave a comment