Top Excel Tips and Tricks (HACKS) to Make You a PRO (Free PDF)

Welcome to our NEW LIST of Top Excel Tips and Tricks for 2024. Whether you’re a professional Excel user or just starting, these simple and practical tips will help you get more out of Excel.

We’ll cover everything from basic to advanced tips and tricks to make your work easier and more efficient. These tips will save you time and help you become more productive in your daily tasks.

So, let’s get started…

1. Get Unique Values from a List of Duplicate Values

In Excel, there is a new function which is called UNIQUE. It can take a range of cells and return unique values.

To get unique values from a list of duplicate values using the UNIQUE: Select the cell where you want the unique values to appear.

Write the formula: =UNIQUE(A1:A10), where the list of duplicate values is range. Press Enter to see the list of unique values.

=UNIQUE(A1:A10)

In the above example, we have ten names; out of those ten, only six are unique, and we have gotten the result.

2. Searchable Drop-Down List

After a long time, Microsoft has added a feature to the drop-down list that allows you to search for a value. This feature is also quite smart.

You don’t need to do anything extra to use this feature. When you create a drop-down list, you can search for any value in the list.

See the above example; when I entered the low in the cell where I have the drop-down list, it showed me all the values where I have the word “Low”.

Watch the video below, which I have explained in detail.

3. Combine Two Columns/Rows in a Single Column/Row

A new function in Excel, TOCOL, can help you combine two columns into one column. The TOCOL function takes a range of cells (array) and returns a combined column.

In the above example, we have two lists of numbers 1 to 6 and 7 to 12 combined into one single list using the TOCOL function.

=TOCOL(A1:B6,0,TRUE)

And in the same way, you have the function TOROW to combine data from multiple ranges into a single row.

Both functions can help you to restructure data quickly.

4. Combine Excel Files with Power Query

One of the most amazing things you can do with a power query is combine multiple files into one file.

The best part is that it’s like a one-time setup. Once you use it, you can refresh the query, which will combine all the updated data from the file in a few seconds.

I highly recommend watching the video below or following the mentioned steps.

Once you go to the data tab in the Excel ribbon, click “From Folder” to select the folder where you have all the files you want to combine in a single workbook.

5. Fit Image Inside a Cell

In Excel, a new feature allows you to create an image within a cell. With this option, when you select an image and then go to the “Picture Format,” you need to click the “Place in Cell” option to fit it into the cell.

This will automatically resize and adjust the image to fit perfectly within the boundaries of the first cell where the image is. In the above example, we fit into cell A2, the first cell behind the image.

6. Sort Data by Color

In Excel, sorting by color allows you to quickly identify and group related data, making it easier to analyze trends and patterns.

For this, follow the steps below:

First, go to the “Data” ribbon and click the “Sort” button.
In the “Sort” dialog box, under the “Column” drop-down, select the column containing the colored cells you want to sort by. And then, under the “Sort On” drop-down, select “Cell Color”.
After that, click on the “Order” drop-down to choose the color you want to sort first. If you have multiple colors and want to sort by more than one, click “Add Level” and repeat the process for each additional color.
In the end, click “OK” to apply the sort.

7. Using WRAPCOLS and WRAPROWS Functions

In Excel, two new functions can help you arrange data from a single column or row to multiple columns and rows.

WRAPCOL can transform a single row or column into a specified number of columns. In the same way, WRAPROWS can transform a single column or a row into a specified number of columns.

These two functions organize and display data more effectively.

8. Sheet View

In Excel, if you are working on data and want to present it to your boss, you need to make changes to make it more presentable.

With Sheet View, you can create a separate view of the worksheet that you can use to present to your boss. And your original view will stay intact.

In the View Tab > Sheet View, clicking on the “New” button creates a new view of the worksheet where you can make all the changes and then click on “Keep” to save them.

Select that view from the drop-down menu when you want to switch from the default to the new view.

9. Sum only Visible Cells

You can use the SUBTOTAL function to sum only the visible cells in Excel. When you insert the SUBTOTAL, you can select the calculation you want to perform. Then, you can specify the entire range where you have the value you want to sum.

When you apply a filter to the data, as SUBTOTAL can work on dynamically, it only shows you the sum for the filtered values, in simple words, only of the visible cells.

But in SUBTOTAL, you can perform further calculations, like count or average, which you can specify.

10. XLOOKUP Multiple Criteria

In XLOOKUP, you can use a dynamic array to work with multiple criteria. In the video below, I have shared a way to create a dynamic lookup_array that helps you combine two arrays into one.

—XLOOKUP with Multiple Criteria in Excel—

In this method, you don’t need to create a helper column.

11. New Data Types

Excel has introduced new data types to make handling and analyzing data easier. These new data types allow you to bring rich, structured data into your workbook, making your data analysis more dynamic and insightful. You need to go to the Data Tab and then the Data Type group to use these data types.

These new data types enhance Excel’s ability to work with rich, structured data, making it a more powerful tool for data analysis.

12. Search Bar

The new search bar makes it much easier to find what you need quickly. Located at the top of the Excel Window, it helps you find options and open recent files.

Instead of going through the ribbon, you can type what you’re looking for, and Excel will show you where to find it.

You can also use the search bar to insert data like tables or charts into your worksheet. Just type what you need, and Excel will guide you.

13. New Checkboxes

In Excel, recently, Microsoft has added a new checkbox that is much easier to insert and super powerful. One thing I like about this is the look of these new checkboxes.

Open Excel, select a cell to insert the Checkbox in, go to the Insert tab, and click on the Checkbox from the control group.

14. Dynamic Arrays

Dynamic Arrays allow you to use one formula to return multiple values. This makes it easier to work with data by automatically filling the cells around your formula with the results.

For example, if you want to filter a list of tasks to show only the completed ones, you can use the FILTER function, and Excel will display all the matching tasks in a list automatically. You can also use functions like UNIQUE to list only distinct items from a range or SORT to order your data.

15. LAMBDA Function

LAMBDA allows you to create your own custom functions using simple formulas. That means you can write a formula once and use it multiple times throughout your worksheet or workbook without repeating the same calculations.

Go to the Formulas tab and click ‘Name Manager.’ Then click ‘New’ to create a new named range. In the ‘Name’ field, enter a name for your custom function, and enter =LAMBDA(x, y, x + y) in the refers to.

Once you’ve set it up, you can use it anywhere in your sheet by entering it with the numbers you want to add, such as =mySUM(10, 5), which will return 15.

16. LET Function

LET is a new addition to Excel, allowing you to assign names to calculation results. That makes your formulas easier to read and can improve performance by calculating an expression once and reusing the result. Enter the below formula in any of the cells.

= LET(x, 5, y, 3, sum, x + y, sum + 10)

First, we set x to 5 and y to 3. Then, we create a variable called sum, which calculates x + y, giving us 8. Finally, the formula adds 10 to the sum, resulting in 18.

This way, the LET function helps break down the calculation into clear steps, making it easier to understand and modify.

17. IMAGE Function

IMAGE function allows you to insert images directly into your cells using a URL. You can display product images, employee photos, or any other pictures within your workbook without manually uploading them.

You can also adjust the height and width of the image by specifying it in the arguments.

18. Import XML Directly into the Excel

You can now import an XML file directly into your workbook. Go to the Developer Tab and click the “Import” button in the XML group.

It will ask you for the cell on which you want to insert data; select the cell, and then click OK.

19. Filter Data Using Right-Click

You can quickly filter data in Excel by using the right-click menu. To do this, click on any cell in the column you want to filter. Then, right-click, go to “Filter”, and choose “Filter by Selected Cell’s Value”.

It will instantly filter your data to show only the rows that match the selected cell’s value.

20. No Code Data Entry Form

Create a table with headers. Enter the heading and convert it into an Excel Table using Ctrl + T. Then, use the keyboard keys Alt, D, and O.

Now, you can use this to enter data into the table.

21. Script Lab

With Script Lab, you can use the Office JavaScript API. To get the “Script Lab”, you must install the script lab add-in from Insert Tab ⇢ Get Add-Ins ⇢ Script Lab.

The next thing you need to do is add the Script Lab tab to the ribbon. For this, go to the File Tab ⇢ Options ⇢ Customize Ribbon ⇢ Tick Mark ⇢ Script Lab. Return to your worksheet and click the “Code” button from the Script Lab Tab.

When you click the code button, a Pane will appear on the right side of the Excel window.

With Script Lab, you can use JavaScript to write codes and create a custom function within Excel. Now, click the menu button and then the New Button.

Next, delete the code from all three tabs: Script, HTML, and CSS.

When you paste the code, the HTML and CSS tab will disappear because this code creates a function. This function helps you to calculate the SQUARE OF A NUMBER.

/** * calculates the square root of the number.
* @customfunction
*/
function getsquare(num) {
return Math.pow(num, 2);
}

From here, you need to register for this function within your Excel application. First, click on the “Register” button.

When you click the Register Button, it shows “The following functions have been registered successfully”.

Now, return to the worksheet and enter a number in a cell. For example, enter “=ScriptLab” in another cell and select the GETSQUARE function. Refer to the cell with the number.

In the end, hit enter to get the result.