50 Ultimate Excel Tips and Tricks for 2020

Introduction

This tutorial compiles 50 essential Excel tips and tricks to enhance your productivity. Updated for 2020, these methods combine long-standing techniques with the latest features from Microsoft. Whether you're a beginner or an experienced user, these tips will help you navigate Excel more effectively and tackle common challenges.

Step 1: Move Data

• Select the cell or range of cells you want to move.
• Click and drag the border of the selection to the new location.
• Alternatively, use Cut (Ctrl + X) and Paste (Ctrl + V) to move data.

Step 2: Use XLOOKUP()

• XLOOKUP replaces older functions like VLOOKUP.
• Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
• Use it to find values in a table or range based on a specified criterion.

Step 3: Filter List

• Click on the Data tab, then select Filter.
• Use the dropdown arrows in the header row to filter data based on specific criteria.

Step 4: Utilize Ideas

• Select a range of data and click on the Ideas button in the Home tab.
• Excel will suggest insights and trends based on your data set.

Step 5: Remove Blanks

• Select your data range.
• Go to the Data tab, select Filter, and uncheck the (Blanks) option.

Step 6: Quick Analysis Tool

• Highlight your data range and click the Quick Analysis icon that appears.
• Choose from options like Formatting, Charts, Totals, Tables, or Sparklines for instant analysis.

Step 7: AutoFit Column Width

• Double-click the right border of the column header.
• This automatically adjusts the column width to fit the content.

Step 8: Absolute Cell Reference

• Use $ in formulas to create absolute references.
• Example: Change A1 to $A$1 to keep the reference fixed when copying the formula.

Step 9: Paste Special Values

• Copy the desired range.
• Right-click on the destination cell, select Paste Special, then choose Values to paste only the values without formatting.

Step 10: Create a Drop-Down List

• Select the cell where you want the drop-down.
• Go to the Data tab, click on Data Validation, and choose List. Enter the source values.

Step 11: Use FILTER()

• Syntax: FILTER(array, include, [if_empty])
• This function allows you to filter a range of data based on specific criteria dynamically.

Step 12: Remove Duplicates

• Select the data range.
• Go to the Data tab and click on Remove Duplicates. Choose the columns to check for duplicates and confirm.

Step 13: Compare Lists

• Use the =IF(A2=B2, "Match", "No Match") formula to identify differences between two lists.

Step 14: Flash Fill

• Start typing a pattern next to your data.
• Excel will automatically suggest a fill; press Enter to accept it.

Step 15: AutoFill

• Drag the fill handle (small square at the bottom right of a cell) to auto-fill adjacent cells with a series.

Step 16: Data Types

• Convert text to specific data types using Excel’s Data Types feature under the Data tab.

Step 17: Transpose Data

• Copy the data range, right-click on the destination cell, and select Paste Special > Transpose to switch rows and columns.

Step 18: Freeze Panes

• Go to the View tab and select Freeze Panes to keep specific rows/columns visible while scrolling.

Step 19: Text to Columns

• Select the data, go to the Data tab, and choose Text to Columns to split data into separate columns based on delimiters.

Step 20: Create Recommended Pivot Tables

• Highlight your data range.
• Go to the Insert tab and select Recommended PivotTables for suggested pivot table layouts based on your data.

Step 21: Use Slicers

• For pivot tables, select the pivot table, go to the Analyze tab, and click on Insert Slicer to filter data visually.

Step 22: Apply Conditional Formatting

• Highlight a range, go to the Home tab, and select Conditional Formatting to apply rules for visual data representation.

Step 23: Use IF() Function

• Syntax: IF(logical_test, value_if_true, value_if_false)
• Use this for conditional calculations based on specific criteria.

Step 24: Work with 3D References

• Reference the same cell or range across multiple sheets using the syntax: Sheet1:Sheet3!A1.

Step 25: Create a Forecast Sheet

• Select your data, go to the Data tab, and select Forecast Sheet to visualize future trends.

Step 26: Use SUMIFS()

• Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• This function sums values based on multiple criteria.

Step 27: Implement IFERROR()

• Syntax: IFERROR(value, value_if_error)
• Wrap your formulas to manage errors gracefully.

Step 28: Navigate with Ctrl-Arrow Keys

• Use Ctrl + Arrow Keys to quickly jump to the edges of data ranges.

Step 29: Create Filled Maps

• Select your data with geographic information and go to the Insert tab to create a filled map.

Step 30: Use PMT() Function

• Syntax: PMT(rate, nper, pv, [fv], [type])
• Use this function to calculate loan payments.

Step 31: Show Formulas

• Press Ctrl + ` (grave accent) to toggle between showing formulas and the results.

Step 32: Use Advanced Select

• Use Ctrl + Click to select non-contiguous cells or ranges.

Step 33: Create Named Ranges Shortcut

• Select the range, click in the Name Box (to the left of the formula bar), type a name, and press Enter.

Step 34: Hide Cells

• Right-click on the selected cells and choose Hide to conceal them from view.

Step 35: Use COUNTBLANK()

• Syntax: COUNTBLANK(range)
• This function counts the number of empty cells in a range.

Step 36: Use Natural Language Query

• Type questions in natural language in the Tell Me box to get help or find features.

Step 37: Implement Goal Seek

• Go to the Data tab, select What-If Analysis, and choose Goal Seek to find input values needed to achieve a desired outcome.

Step 38: Insert Screenshot

• Go to the Insert tab, select Screenshot, and choose from available windows or screen snippets.

Step 39: Explore Power Pivot

• Use Power Pivot to manage large data sets and create more complex models.

Step 40: Utilize 3D Maps

• Go to the Insert tab and select 3D Maps to visualize your geographic data in 3D.

Step 41: Use ISBLANK()

• Syntax: ISBLANK(value)
• This function checks if a cell is empty and returns TRUE or FALSE.

Step 42: Activate Analysis ToolPak

• Go to File > Options > Add-ins, and activate the Analysis ToolPak for advanced statistical analysis.

Step 43: Use CONVERT()

• Syntax: CONVERT(number, from_unit, to_unit)
• Convert units of measurement easily with this function.

Step 44: Get Data from Web

• Go to the Data tab, select Get Data, and choose From Other Sources > From Web to import data from a web page.

Step 45: Create a People Graph

• Go to the Insert tab, select People Graph to create a visual representation of your data.

Step 46: Use SORT()

• Syntax: SORT(array, [sort_index], [sort_order], [by_col])
• This function sorts the contents of a range or array.

Step 47: Check Status Bar Info

• Right-click on the Status Bar to customize what information is displayed, such as sum, average, or count.

Step 48: Insert Multiple Rows

• Highlight the number of rows you want to add, right-click, and choose Insert to add multiple rows at once.

Step 49: Use CHOOSE()

• Syntax: CHOOSE(index_num, value1, [value2], ...)
• This function selects a value from a list based on the given index.

Step 50: Use UNIQUE()

• Syntax: UNIQUE(array, [by_col], [exactly_once])
• This function returns a list of unique values from a range.

Conclusion

By incorporating these 50 Excel tips into your workflow, you can greatly enhance your efficiency and productivity. Consider exploring each feature further to see how they can best fit your specific tasks or projects. Happy Excel-ing!