❤️ Top 30 Excel Tips and Tricks to save 30+ hours of work

Introduction

This tutorial provides a comprehensive guide to the top 30 Excel tips and tricks that can save you over 30 hours of work. Mastering these techniques will enhance your productivity and streamline your workflow in Excel. Let's dive into these invaluable strategies.

Step 1: Insert Data from a Screenshot

• Use the "Insert Data from a Screenshot" feature to quickly add data.
• Press Alt + N, then select "Screenshot" to capture and insert images.

Step 2: Sum Values Fast

• Utilize three methods to sum values quickly:
  • AutoSum: Click on the AutoSum button on the Home ribbon.
  • Shortcut Key: Press Alt + =.
  • Manual Entry: Use the formula =SUM(range).

Step 3: Toggle Filters On and Off

• Select the filter icon in the header to quickly toggle filters without removing them.

Step 4: Fast Filter Trick

• Hold down the Alt key while selecting the filter dropdown to access options quickly.

Step 5: Center Text Across Cells

• Select the cells, then choose "Center Across Selection" in the Alignment settings.

Step 6: Add Cell Messages

• Use the "Input Message" feature in Data Validation to provide guidance within cells.

Step 7: Freeze Top Row

• Go to the View tab and select "Freeze Panes" to keep the top row visible while scrolling.

Step 8: Freeze Top Row with Table

• Convert your data to a table, which automatically freezes the header row.

Step 9: Freeze Bottom Row

• Similar to freezing the top row, select "Freeze Panes" and choose the bottom row.

Step 10: Print Headers on Every Page

• In Page Layout, check the "Print titles" option to repeat header rows on printed pages.

Step 11: Move Cells with Mouse

• Click and drag the edge of a selected cell or range to move it.

Step 12: Copy Cells with the Mouse

• Use Ctrl + drag to copy cells while moving them.

Step 13: Copy Only Visible Cells

• Select the range, press Alt + ; to select visible cells only before copying.

Step 14: Delete Blank Records

• Use the Go To Special feature (F5 > Special > Blanks) to select and delete blank rows.

Step 15: Fill Blank Cells

• Select the range, go to Home > Fill > Down to fill blanks with the value above.

Step 16: Fill Numbers to the Nth Value Using a Series

• Drag the fill handle down while holding Ctrl to create a series of numbers.

Step 17: Fill Dates to the Nth Value Using a Series

• Similar to numbers, drag the fill handle with a date to extend the series.

Step 18: Use Flash Fill for Numbers

• Start typing the desired format next to your data, and Excel will suggest a fill.

Step 19: Use Flash Fill for Letters

• Use the same Flash Fill method for text formatting.

Step 20: Smart Use of Clipboard

• Use Ctrl + C and Ctrl + V for copying and pasting effectively across sheets.

Step 21: Create a Quick Bar Chart with the REPT Function

• Use the formula =REPT("█", value) to visually represent numbers as bar charts.

Step 22: Create McKinsey Lollipop Chart

• Use the Windows Emoji picker (WIN + .) to insert lollipop characters into your chart.

Step 23: Display File Path and Workbook Name

• Use the formula =CELL("filename") to display the current workbook name.

Step 24: Hide Cell Contents from View

• Use the format cells option to set the text color to white on a white background.

Step 25: Center Text Across Cells with VBA

Sub Center_Across_Selection()
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
    End With
End Sub

Step 26: Remove Cell Formats with VBA

Sub Remove_Formatting()
    With Selection
        .ClearFormats
    End With
End Sub

Step 27: Filter Function with Single Criteria

• Use the formula =FILTER(data_range, criteria) to filter data based on a single condition.

Step 28: Filter Function for Multiple Criteria

• Extend the filter function using =FILTER(data_range, (criteria1)*(criteria2)) for multiple conditions.

Step 29: Create New Sheets from Values in a List Using a Pivot Table

• Create a Pivot Table and use it to generate new sheets based on list values.

Step 30: Create New Sheets from Values in a List Using VBA

• Refer to the article for full VBA code to automate the creation of sheets from a list.

Conclusion

By implementing these Excel tips and tricks, you can significantly boost your efficiency and save valuable time. Practice these techniques regularly to master Excel and consider exploring Excel VBA for even greater productivity.