With over 750 million users worldwide, Microsoft Excel is the traditional tool of choice for presenting and analyzing data. Excel has been an integral part of most companies across the world. Excel is used for different activities by business entities.

Here are 20 Excel tips & functions to increase productivity & make you an expert explained with examples

(1) Wildcards

A wildcard is a special character that allows you to perform partial matches on text in your Excel formulas.

Excel has three wildcards: an asterisk “*”, question mark “?”, and “~”

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(2) Duplicate

Duplicate the data from the cell above.

Ctrl + D fills and overwrites a cell with the contents of the cell above it

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(3) Remove Duplicates

Remove duplicates in a set of data in Excel Alt+A+M

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(4) Transpose

This will transform items in rows, to instead be shown in columns or vice versa.

To transpose a column to a row:

  1. Select the data in the column,

  2. Select the cell you want the row to start,

  3. Right-click, choose to paste special, select transpose

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(5) Filter

The FILTER function allows you to filter data based on a query.

For example, you can filter a column to show a specific product or date.

You can also sort in ascending or descending order.

The shortcut for this function is CTRL + SHFT + L

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(6) Conditional Formatting

Conditional formatting helps to visualize data and can show patterns and trends in your data

Go to: Home –> Conditional Formatting –> Highlighting Cell Rules

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(7) Sparklines

Sparklines allow you to insert mini graphs inside a cell to provide a visual representation of data. Use sparklines to show trends or patterns in data.

On the ‘Insert tab’, click ‘Sparklines’

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(8) Pivot Tables

A powerful tool to calculate, summarize & analyze data, which allows you to compare or find patterns & trends in data.

To access this function, go to “Insert” in the Menu bar, and then select “Pivot Table

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(9) Auto-fill

With large data sets, instead of typing a formula multiple times, use auto-fill.

There are 3 ways to do this:

(1) Double-click click mouse on the lower right corner of the 1st cell, or

(2) Highlight a Section and type Ctrl + D, or

(3) Drag the cell down the rows

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(10) TRIM

TRIM helps to remove the extra spaces in data.

TRIM can be useful in removing irregular spacing from imported data =TRIM()

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(11) XLOOKUP

XLookup is an upgrade compared to VLOOKUP or Index & Match.

Use the XLOOKUP function to find things in a table or range by row.

Formula: =XLOOKUP (lookup value, lookup array, return array)

XLOOKUP is much more versatile and powerful. For example, it is capable of searching in any direction whereas VLOOKUP can only search “forwards” (i.e. down a column or right across a row, never up or left), it defaults to exact match, it can return multiple values, it’s more resistant to suddenly break if you add rows or columns to the search area.

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(12) IF

The IF function makes logical comparisons & tells you when certain conditions are met.

For example, a logical comparison would be to return the word “Pass” if a score is >70, and if not, it will say “Fail” An example of this formula would be =IF(C5>70,”Pass”,”Fail”)

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

An IF formula is really just 3 parts, separated by commas.

  1. A statement that is either true or false

  2. What do you want it to show you if the answer is true

  3. What do you want it to show you if the answer is false

For example, Let’s say in column A I have a list of names and in column B I have those people’s corresponding ages. And I want my formula to tell me if they are old enough to drink or not.

The legal drinking age is 18 in my country, so my three parts would look like this:

  1. A statement that is either true or false: The cell in column B is showing an age of 18 or over.

  2. What you want it to show you if the answer is true: “Old enough to drink”

  3. What you want it to show you if the answer is false: “Too Young”

So the IF formula I could write into C1 would be put together like this:

=IF(B1>17,”Old enough to drink”,”Too Young”)

Then I would drop it down to populate the column, and it should tell me who is old enough to drink and who isn’t.

It is also possible to make what to show you if it is true or false a formula as well.

Using the same example, let’s say the person is too young to drink I want to know how many years it until they are allowed. The three parts would look like this:

  1. A statement that is either true or false: The cell in column B is showing an age of 18 or over.

  2. What you want it to show you if the answer is true: “Old enough to drink”

  3. What you want it to show you if the answer is false: (18 – the age that they are) & ” years until they can drink”

And the formula would look like this:

=IF(B1>17,”Old enough to drink”,CONCATENATE(18-B1,” years until they can drink”))

Which, if the person was 16 would return “2 years until they can drink”.

(13) SUMIF

Use this to sum the values in a range, when they meet specific criteria.

For example, use this if you want to figure out the number of sales in a given region or by a person.

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(14) SUMIFS

SUMIFS sum the values in a range that meet multiple criteria.

For example, use it if you want the sum of two criteria, for example, Apples from Pete.

The formula is SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(15) COUNTIF

Use COUNTIF to count the number of cells that satisfy a query.

For example, you can count the number of times a particular word has been listed in a row or column.

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(16) COUNTIFS

CountIf counts the number of times a criteria is met.

For example, it counts the number of times that both (1) apples and (2)price > $10, are mentioned.

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(17) UPPER, LOWER, PROPER

=UPPER, Converts text to all uppercase,

=LOWER, Converts text string to lowercase,

=PROPER, Converts text to proper case

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(18) CONVERT

This converts one measurement to another.

There are multiple conversions that you can do.

An example is metered to feet or Celsius to Fahrenheit.

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(19) Stock Market data

You can get stock data in Excel

Enter a list of stock ticker symbols, then select the cells and go to the Data tab, then click the Stocks button within the Data Types group.

Excel will attempt to match each cell value to company stock, and fill in the data

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)

(20) Geography/ Maps

Instead of researching geographical data or maps, use Excel

With the Geography data type, you can retrieve data like population, time zone, area leaders, gasoline prices, language, and more

Type the data you need, then go to Data Tab -> Geography

r/excel - Here are 20 Excel tips & functions to increase productivity & make you an expert!!! (I've used Microsoft Excel for 20 years!)