Excel Tips and Tricks

Summary

The video provides 11 Excel tips and tricks to work faster and more efficiently in Excel. It covers techniques like dragging columns to rearrange them easily, using keyboard shortcuts to quickly sum cells, multiplying a range by a number to update values, getting unique counts, dissecting complex formulas, creating custom lists, leveraging named ranges, displaying metrics in the status bar, extracting data with Flash Fill, using Ctrl + Enter to apply functions to multiple cells, and customizing the Quick Access toolbar.

Takeaways

• 😀 You can move columns by dragging and dropping instead of cut and paste
• 😮 Use alt+= as a shortcut to sum a range of cells
• 🤯 Multiply or divide values in a range without formulas using Paste Special
• 📈 Easily get a distinct count of values with the UNIQUE function
• 💡 Break down complex formulas using the Evaluate Formula tool
• 🍪 Create custom lists to auto-populate drop downs
• 🏷 Name cells/ranges to simplify formulas
• 📊 Quickly view count, sum, avg etc for a range in the status bar
• ⚡ Use Flash Fill to extract or combine data from columns
• ⛑️ Add common commands to the Quick Access toolbar for easy access

Q & A

How can you move a column in Excel by dragging and dropping?

You can highlight the entire column, hover over the edge of the selection until the cursor changes to an arrow icon, then press the Shift key along with the left mouse button and drag the column wherever you want.

What is the shortcut key to sum up a range of cells in Excel?

The shortcut key to sum up a range of cells in Excel is Alt +=. You can select the cells first or select them after pressing the keys.

How can you multiply or divide all values in a range in Excel without using formulas?

You can enter a number like 10 in a cell, copy that cell, select the range you want to apply the operation to, go to the Home tab, click Paste Special, select Multiply or Divide, and click OK. This will apply that operation to all the selected cells.

What is the Excel function to get a distinct count of values?

The UNIQUE function in Excel returns the distinct values from a range or array. You can combine it with the COUNTA function to get a count of those distinct values.

How can you evaluate parts of a complex nested function in Excel to understand how it works?

You can click on a part of the nested function in the formula bar and press the F9 key to show the result of just that portion. Or you can click the function helper icon next to each part to view details about that specific function.

How do you create a custom list to use with the fill handle in Excel?

Go to File > Options > Advanced, scroll down to the Edit Custom Lists section, click New List, enter your list items or select them from a sheet, and click Add. Then you can use that list with the fill handle to populate cells.

What are named ranges and how can they be useful when writing formulas?

Named ranges give names to cells or ranges of cells so you can reference them by name instead of by cell address. They make formulas easier to read and understand. You can create them by selecting cells and entering a name in the Name Box.

How can the status bar provide quick metrics when selecting a range?

When you select a range of cells, the Excel status bar shows the count, sum, average, numerical count, min and max of those cells. You can also right click the status bar to customize the metrics displayed.

What does the Flash Fill feature allow you to do?

Flash Fill lets you extract parts of text from one column into another with just a couple examples. It can also combine text from multiple columns. This automates tasks that would normally require tedious manual work.

How does control + enter work when entering a formula across multiple cells?

Instead of pressing just Enter after typing your formula, press Ctrl + Enter. This will apply that formula to all the selected cells instead of just the active cell.