Microsoft Excel remains one of the most powerful tools for managing, analyzing, and presenting data. As a staple in both business and academic settings, Excel is used daily by millions to track sales, manage finances, create reports, and much more. As part of Office 2021, Microsoft has released Excel 2021, which comes with a number of new features and functionalities aimed at improving workflows and simplifying data management.
In this post, we’ll explore some of the new Excel 2021 features that are set to transform the way you work, followed by 30 essential Excel tips and tricks to boost your productivity. Whether you’re a seasoned Excel user or just starting, these insights will help you master the latest version and improve your data management skills.
Table of Contents
Key New Features in Excel 2021
Microsoft Excel 2021 comes packed with new features that enhance usability, improve collaboration, and make complex tasks easier. Here’s a breakdown of the most significant updates:
1. XLOOKUP Function
The XLOOKUP function is a game-changer for those familiar with VLOOKUP or HLOOKUP. Unlike its predecessors, XLOOKUP is more versatile and intuitive. It allows you to search for data across a range or table and return the corresponding value. What makes it even better is that it requires only three basic parameters:
- Lookup value: The item you’re searching for.
- Lookup array: The range where you’re searching.
- Return array: The range from which you want to return a result.
With XLOOKUP, you don’t need to specify “TRUE” or “FALSE” for an approximate match, as it defaults to an exact match.
2. LET Function
The LET function allows you to assign names to calculation results within a formula. This makes your formulas easier to read and understand, especially when working with complex formulas. With support for up to 126 name/value pairs, the LET function makes Excel 2021 formulas cleaner and more efficient.
3. Dynamic Array Functions
Excel 2021 introduces six powerful new dynamic array functions that automatically spill results into neighboring cells. These include:
- FILTER: Filters data based on defined criteria.
- SORT: Sorts a range of cells by a specified column.
- SORTBY: Sorts a range of cells based on another array.
- UNIQUE: Extracts unique values from a range.
- SEQUENCE: Generates a list of sequential numbers.
- RANDARRAY: Creates an array of random numbers.
These new functions make it easier to manipulate and analyze large datasets.
4. XMATCH Function
The XMATCH function is the successor to MATCH and provides greater flexibility when finding the relative position of a value in a range. It supports exact matches as well as options to find the next smaller or larger value.
5. Co-Authoring and Threaded Comments
Excel 2021 enables co-authoring, allowing multiple users to work on the same workbook simultaneously. You can see changes in real-time and collaborate efficiently. Threaded comments let you add more context to discussions within the workbook by allowing replies directly within the comment section.
6. Improved Performance and Compatibility
Excel 2021 also boasts improved performance, including faster calculations, enhanced file size handling, and better integration with other Office 2021 apps. These improvements make Excel 2021 a more robust tool for managing large datasets and complex operations.
30 Essential Excel 2021 Tips and Tricks to Maximize Your Productivity
Now that you’re familiar with the latest features in Excel 2021, let’s dive into 30 practical Excel tips and tricks to further enhance your productivity. Whether you’re a beginner or an advanced user, these tips will help you perform tasks more efficiently and save valuable time.
1. Use Keyboard Shortcuts
Mastering Excel’s keyboard shortcuts can drastically speed up your work. Whether it’s creating a new workbook or undoing your last action, knowing the right shortcut can save you from clicking through menus.
2. Quickly Delete Blank Cells
To remove blank cells, select the range of cells you want to clean, go to the Home tab, select Find & Select, then click Go To Special and choose Blanks.
3. Add a Diagonal Line to a Cell
To add a diagonal line inside a cell, right-click the cell, choose Format Cells, and under the Border tab, select the diagonal line option.
4. Copy a Formula Across Rows or Columns
To copy a formula across rows or columns, use the Fill Handle (the small square at the lower right corner of the cell) to drag the formula across the desired range.
5. Optimize Print Settings
For better print management, go to the File tab, select Print, and adjust settings such as the number of pages, page orientation, and paper size.
6. Import a Table from the Web
Easily import data from the web by copying the URL, selecting Data > Get & Transform > From Web, and pasting the URL. Excel will load the data into your sheet.
7. Flash Fill
Flash Fill automatically detects patterns in your data and fills in the remaining values for you. Simply start typing, and Excel will suggest the rest. Press Enter to accept the suggestions.
8. Format Painter
Use the Format Painter to copy formatting from one cell to another. Select the formatted cell, click the Format Painter icon on the Home tab, and then click the target cell to apply the formatting.
9. Use Conditional Formatting
Highlight important data points using Conditional Formatting. Select the range, go to the Home tab, and choose the format you want based on conditions like values, dates, or text.
10. Text to Columns
To split text into separate columns, select the column, go to Data > Text to Columns, and follow the prompts to define the delimiter (such as commas or spaces).
11. Insert Excel Data into Word
Easily link or paste Excel data into a Word document by copying the data in Excel 2021 and pasting it directly into Word. You can also use Paste Special to link data for automatic updates.
12. Add Drop-Down Menus
Create drop-down lists for data entry by selecting the cell, going to the Data tab, and using Data Validation to define your list items.
13. Hyperlink Cells to Websites
To add a hyperlink to a website, select the cell, go to the Insert tab, click Link, and enter the URL.
14. Add Checkboxes
Add checkboxes for interactive forms by going to the Developer tab, selecting Insert, and choosing the Checkbox option from the Form Controls.
15. COUNTIF Function
Use COUNTIF to count the number of cells that meet specific criteria. For example, to count how many times “New York” appears in a column, use =COUNTIF(A2:A35, “New York”).
16. INDEX & MATCH Formulas
The combination of INDEX and MATCH functions is more flexible than VLOOKUP. It allows you to search for values in any row or column, providing more control over your data.
17. VLOOKUP Function
Use VLOOKUP to search for data in one sheet and pull it into another. This is especially useful when working with multiple worksheets in the same workbook.
18. Paste Special with Formulas
The Paste Special function lets you perform mathematical operations on copied values, such as dividing a range of values by a constant.
19. Shift Between Different Excel Files
Quickly switch between open Excel workbooks by pressing CTRL+Tab or ALT+Tab.
20. Use the IF Formula to Automate Tasks
The IF function allows you to automate logical tests, making your data analysis more dynamic and responsive to changing conditions.
21. Insert More Than One Row or Column
To quickly add multiple rows or columns, select the number you need, right-click, and choose Insert from the drop-down menu.
22. Add Bullet Points
Use ALT+7 to add bullet points to a cell for better organization in lists.
23. Use Tables to Prevent Data Shifts
Create a table to lock your data in place and prevent accidental shifts when adding new rows or columns.
24. Shift Between Cells Using F8
Activate Extend Selection mode by pressing F8, then use the arrow keys to select non-adjacent cells.
25. AutoFormat Cells for Consistent Formatting
Use AutoFormat for quick and consistent formatting. Add it to your Quick Access Toolbar and apply it to any selected data range.
26. AutoSum for Fast Calculations
Use the AutoSum feature to automatically sum up a column or row of numbers. Simply select the empty cell at the end of the range and click the AutoSum button.
27. Freeze Panes for Easy Navigation
Freeze columns or rows to keep headers visible while scrolling through large datasets. Go to the View tab and select Freeze Panes.
28. Group and Ungroup Data for Better Organization
Use the Group feature to organize data into collapsible sections, making large datasets easier to manage.
29. Use Pivot Tables for Data Analysis
Pivot tables are one of Excel’s most powerful features for summarizing and analyzing large datasets. Create a pivot table by selecting your data and using the Insert > Pivot Table function.
30. Use Data Validation for Error Prevention
Prevent errors in data entry by setting up Data Validation rules. For example, limit cell entries to specific ranges, dates, or values.
Unlock More Productivity Features
By mastering these Excel 2021 features and productivity tips, you’ll be able to work more efficiently, automate tasks, and gain deeper insights into your data. Excel 2021 is packed with tools that can help you streamline your work, whether you’re managing large datasets, performing calculations, or collaborating with others.
Want to explore more features to boost your productivity across the entire Microsoft Office suite? Don’t miss our next post on Hidden Features in Microsoft Office 2021 to Boost Your Productivity, where we’ll dive deeper into lesser-known tips and tricks across Word, Excel, PowerPoint, and more.
