Excel Tips, Tricks and Shortcuts Part 3: The Functions You Need to Know

Excel Tips, Tricks and Shortcuts Part 3: The Functions You Need to Know

This video introduces 10 excel functions. This is the 3rd batch of excel tips, tricks and shortcuts. My goal here is to help you work faster in Excel. And for you to be easily guided, the keywords to remember each function are indicated at the heading of every illustration.
1. ADVANCED SELECT: This function is useful to instantly find and select the items that you need. It includes the keyboard short-cuts to quickly display the function’s window.
2. SHOW FORMULAS: Use this if you want to see the formula of several cells. You can go to Formulas tab or use the keyboard shortcut ALT+M+H.
3. CONSOLIDATE: It’s an excel function that is used to consolidate data from one workbook, or from different open workbooks. Click ALT-A-N and you will be directed to the consolidate window. You can also find it under the data tab.
4.TABLE: Excel table comes with useful features. It does not only organize and structure your data. It also helps to expedite your work in many ways. Here are the 8 benefits of using Excel Table:
• Easily filter the information that you want to display.
• Quickly add or remove rows or columns with the use of the resize handle that comes along with the table.
• Automatically add a formatted column when you type a text right beside the table and a formatted row when you type a text right below the table.
• Quickly transfer the whole table.
• Create a formula in 1 cell and automatically fill the other cells in the column with the same formula.
• Display a drop-down list of various commonly used formulas or calculations by creating 1 formula right below the table. With the drop-down list, you can easily do other calculations.
• Swiftly hide or show the row containing the total.
• Easily change the format or choose other formatting options.

5. CONCATENATE: There are 3 ways to combine two or more text strings and place them together in 1 cell:
• With the use of the Concatenate Excel formula.
• Through the Textjoin function which concatenates a list or range of text strings using a delimiter.
• With the use of &.

6. AUTO FILL: Used to automatically fill the remaining cells based on the pattern of what was entered.
7. FLASH FILL: Designed to do automatic entry of data. What you do to the first cell will be done to the remaining cells with the use of this function. You just have to enable it through the box for Automatically Flash Fill.
8. AUTO COPY: Used to automatically copy the formula down with just a simple double click at the lower right corner of the cell with formula.
9. IF FUNCTION: There are various logical tests that can be done with the use of the If function. It is used to evaluate data in the spreadsheet. IF Excel function determines whether the logical test is true or false, and it displays a certain value as defined in the formula. It is also helpful if you want to hide formula error from your spreadsheet.
30. IFERROR: This is similar to the IF function which eliminates or hides an error if the function does not suit the given data. You can choose what to be shown in case of an error or if the formula is not applicable. This is a faster way to avoid displaying error.

Title: Excel Tips, Tricks and Shortcuts Part 3: The Functions You Need to Know

Close Menu