• Wildcards:

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

Excel has three wildcards:

• Asterisk "*"
• Question mark "?"
• Tilde "~"

• 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.

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

• Sparklines:

Sparklines allow you to insert mini graphs inside a cell and provide a visual representation of data.

Use sparklines to show trends or patterns in data.

On the 'Insert tab', click 'Sparklines'.

• Remove duplicates:

Alt+A+M removes duplicates in a set of data in Excel.

• Duplicate:

Duplicate the data from the cell above.

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

• Transpose:

This will transform items in rows to be shown in columns instead, 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 paste special, select Transpose

• Trim:

Trim helps to remove the extra spaces in data.

TRIM can be useful in removing irregular spacing from imported data.

=TRIM()

• Pivot tables:

A powerful tool for calculating, summarizing, and analyzing data, it allows you to compare or find patterns and trends in data.

To access this function, select "Insert" in the Menu bar and select "Pivot Table."

• Stock market data:

You can get stock data in Excel.

1. Enter a list of stock ticker symbols.
2. Then select the cells and go to the Data tab
3. Then click the Stocks button within the Data Types group.

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

• Upper, lower, proper case:

=UPPER, Converts text to all uppercase

=LOWER, Converts text string to lowercase

=PROPER, Converts text to proper case

Excel Shortcut List