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
=TOCOL(A1:B6,0,TRUE)
4. Combine Excel Files with Power Query
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
- 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
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
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
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.