growth

18 Essential Excel Functions to Level-Up Your Spreadsheets

Summary

Boost your productivity by mastering essential Excel functions like Countif, Sumif, Sumifs, Xlookup, and counting unique values. Improve your efficiency and make data analysis easier in your professional life.

Mastering essential Excel functions can make your work more efficient and productive. In this guide, we'll explore some of the most commonly used functions that can help you navigate and analyze data with ease.

Countif Excel

Countif is a handy function that allows you to count cells within a specified range that meet certain criteria. For example, you can use Countif to count the number of sales above a specific threshold or the number of products with a certain rating.

To use Countif, use the following formula:

=COUNTIF(range, criteria)

Where 'range' refers to the range of cells you want to evaluate, and 'criteria' is the condition that needs to be met.

Excel Sumif

Sumif is a powerful function that enables you to add the values of cells that meet specific criteria within a range. It's particularly useful when you need to sum data based on certain conditions, such as sales in a particular region or expenses in a specific category.

To use Sumif, use the following formula:

=SUMIF(range, criteria, sum_range)

Where 'range' refers to the range of cells you want to evaluate, 'criteria' is the condition that needs to be met, and 'sum_range' is the range of cells you want to sum.

Sumif function in Excel

The Sumif function is similar to the Sumif function mentioned above. It allows you to sum cells that meet multiple conditions. By using Sumifs, you can sum data based on various criteria, such as sales in a specific region and within a certain date range.

To use the Sumifs function, use the following formula:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where 'sum_range' is the range of cells you want to sum, 'criteria_range' refers to the range of cells you want to evaluate, and 'criteria' is the condition that needs to be met.

Xlookup Excel

Xlookup is a versatile function that helps you find and return a value in a table or a range by searching for a corresponding value in another column or row. This function is particularly useful when you need to look up data from different tables or sources.

To use Xlookup, use the following formula:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Where 'lookup_value' is the value you want to search for, 'lookup_array' is the range of cells you want to search within, 'return_array' is the range of cells containing the data you want to retrieve, and the optional parameters 'if_not_found', 'match_mode', and 'search_mode' can be used to customize the function's behavior.

Count unique values Excel

Counting unique values in a range can help you identify the number of distinct items, such as unique customers or product categories. To count unique values in Excel, you can use a combination of the COUNTIF and SUM functions.

To count unique values, use the following formula:

=SUM(1/COUNTIF(range, range))

Where 'range' refers to the range of cells containing the data you want to evaluate.

Data Manipulation

How to find duplicates in Excel

Finding duplicate values in your data can be crucial for ensuring accuracy and consistency. Excel offers a simple method for identifying duplicates using the conditional formatting feature.

Select the range of cells you want to check for duplicates.
Click on the "Home" tab, then "Conditional Formatting."
Choose "Highlight Cells Rules" and then "Duplicate Values."
Pick a formatting style for the duplicates and click "OK."

Excel will now highlight any duplicate values within the selected range.

Highlight duplicates in Excel

To further emphasize duplicate values, you can apply different formatting styles, such as bold text or colored backgrounds.

Select the range of cells containing duplicates.
Click on the "Home" tab, then "Conditional Formatting."
Choose "New Rule."
Select "Format only unique or duplicate values."
In the "Format all" dropdown, choose "duplicate."
Click "Format," apply the desired formatting, and click "OK."

Excel will now apply the chosen formatting to all duplicate values in the selected range.

Find duplicates in Excel

If you need to extract a list of duplicate values from your data, you can use the following formula:

=IF(COUNTIF(range, value) > 1, value, "")

This formula will return the value if it appears more than once in the specified range. Copy the formula for all cells in the range to generate a list of duplicates.

How to add a column in Excel

Adding a column in Excel is a straightforward process:

Select any cell in the column to the right of where you want the new column.
Click on the "Home" tab, then "Insert."
Choose "Insert Sheet Columns."

Excel will insert a new column to the left of the selected cell.

How to add columns in Excel

To add multiple columns in Excel at once, follow these steps:

Select the same number of columns you want to insert.
Click on the "Home" tab, then "Insert."
Choose "Insert Sheet Columns."

Excel will insert the specified number of columns to the left of the selected cells.

How to move a column in Excel

To move a column in Excel, follow these steps:

Select the entire column you want to move.
Right-click the selected column and choose "Cut."
Select the cell where you want to move the column.
Right-click and choose "Insert Cut Cells."

Excel will move the column to the new location.

How to move columns in Excel

To move multiple columns in Excel, follow these steps:

Select the columns you want to move.
Right-click the selected columns and choose "Cut."
Select the cell where you want to move the columns.
Right-click and choose "Insert Cut Cells."

Excel will move the columns to the new location.

How to combine two columns in Excel

Combining two columns in Excel can be useful for merging data, such as first and last names. Use the following formula to combine two columns:

=A1 & " " & B1

Replace "A1" and "B1" with the cell references you want to combine. Copy the formula down the column to combine all cells.

How to enable macros in Excel

Macros can automate repetitive tasks and save time. To enable macros in Excel, follow these steps:

Click on the "File" tab, then "Options."
Select "Trust Center" and click on "Trust Center Settings."
Choose "Macro Settings."
Select "Enable all macros" or "Disable all macros with notification" based on your preference.
Click "OK" to save the settings.

Now, Excel will allow you to use macros in your workbooks.

How to subtract in Excel

Subtraction in Excel is simple. Use the following formula to subtract two numbers:

=A1 - B1

Replace "A1," "B1," and "C1" with the cell references you want to subtract. Copy the formula down the column to subtract values in multiple cells.

Subtraction in Excel

To subtract a range of cells from a single value, use the following formula:

=A1 - SUM(B1:B10)

Replace "A1" with the single value and "B1:B10" with the range of cells you want to subtract. Copy the formula down the column to subtract values in multiple cells.

How to unhide columns in Excel

To unhide a hidden column in Excel, follow these steps:

Select the columns on either side of the hidden column.
Click on the "Home" tab, then "Format."
Choose "Hide & Unhide" and click "Unhide Columns."

Excel will now display the previously hidden column.

Unhide columns in Excel

To unhide multiple hidden columns in Excel, follow these steps:

Select the columns on either side of the hidden columns.
Click on the "Home" tab, then "Format."
Choose "Hide & Unhide" and click "Unhide Columns."

Excel will now display the previously hidden columns.

How to unhide all rows in Excel

To unhide all hidden rows in Excel, follow these steps:

Press Ctrl + A to select the entire worksheet.
Click on the "Home" tab, then "Format."
Choose "Hide & Unhide" and click "Unhide Rows."

Excel will now display all previously hidden rows.

By mastering these essential Excel functions, you'll be well-equipped to handle various tasks and projects in your professional life. Practice using these functions to become more comfortable with Excel and improve your overall efficiency at work.