Top 30 Excel Tips and Tricks to save 30+ hours of work
Last updated on May 03, 2024 By Victor Chan
Excel is a powerful tool that can save you a significant amount of time and effort when working with data. This article outlines 30 tips and tricks that will help you work smarter, not harder. These tips cover a wide range of tasks, from inserting data into Excel from screenshots to creating visually appealing charts and automating repetitive tasks with VBA.
Watch my video on YouTube for a complete demonstration of every tip.
If you’ve come from YouTube looking for the VBA code, you’ll find it in tips #25, #26, #30.
Tip 1: Insert data into Excel from Screenshot
Tip 2: Sum values fast (3 ways)
Tip 3: Toggle filters on and off (keyboard shortcut)
Tip 5: Center text across selected cells without merging
Tip 6: Cell message (data validation)
Tip 7: Freeze Top Row (Freeze Panes)
Tip 8: Freeze Top Row (Table Headers)
Tip 9: Freeze Bottom Row (Split Panes)
Tip 10: Print Headers on Every Page
Tip 11: Move cells using Mouse
Tip 12: Copy cells using Mouse
Tip 13: Copy visible cells only
Tip 20: Smart use of clipboard (multiple items)
Tip 21: Quick bar chart (REPT function)
Tip 22: McKinsey Lollipop Chart (REPT function)
Tip 23: Display file path and file name
Tip 25: Center text across selected cells without merging (VBA)
Tip 26: Remove cell formatting (VBA)
Tip 27: Filter() function for single criteria
Tip 28: Filter() function for multiple criteria
Tip 29: Create multiple worksheets from a list (Pivot Table)
Download FREE Excel Workbook
Step 1: Sign up for free Click Here
Step 2: Log in for access Click Here
Step 3: Download file Top-30-Excel-Tips-and-Tricks
Are you new to Excel VBA Macros?
• Find out how to enable and disable macros in Excel with this tutorial
• Save time and effort by copying VBA macros from one workbook to another with these instructions
When you download the Tips andTricks workbook ZIP file, you’ll get the full list of Top 30 tips and tricks with instructions on how to use them.
The first sheet is a clickable table of contents in Excel like you see in the image above. Click on the header to navigate to the tip.
This Master Sheet automatically updates itself and you can customize the fonts, colors, and layout to suit your taste. You can learn how to build your own Master Sheet (and get a free copy to download) in my full step-by-step VBA code tutorial here: How to Create a Master Sheet in Excel.
Tip 1: Insert data into Excel from Screenshot
Summary: Instead of manually typing data from an image or website, you can extract and insert it directly into Excel using the Snipping Tool and the "From Picture" feature.
- Take a screenshot of the data using the Windows key + Shift + S shortcut.
- In Excel, go to the Data tab and click "From Picture" > "Picture from Clipboard".
- Review and insert the extracted data.
Tip 2: Sum values fast (3 ways)
Summary: Quickly calculate the sum of a range of cells using three different methods.
- Method 1: Select the cell below your column of data to sum and press Alt + =.
- Method 2: Use the “Total” feature in the status bar at the bottom of your Excel window.
- Method 3: Select the cell range, go to the Insert tab, and click "Table". Check the “Total Row” option. Choose “sum” from the cell dropdown. The sum will appear in the Total Row.
Tip 3: Toggle filters on and off (keyboard shortcut)
Summary: Quickly apply and remove filters using a keyboard shortcut.
- To apply filters, select the desired range and press Ctrl + Shift + L.
- To remove filters, press Ctrl + Shift + L again.
Tip 4: Fast filter trick
Summary: Filter data based on the value in a specific cell with a few clicks.
- Right-click on the cell containing the value you want to filter by.
- Select "Filter" > "Filter by Selected Cell's Value".
Tip 5: Center text across selected cells without merging
Summary: Center text across multiple cells without merging them, allowing you to sort, select, and filter the data.
- Select the cells across which you want to center the text.
- Press Ctrl + 1 to open the Format Cells dialog box.
- Under the "Alignment" tab, select "Center Across Selection" from the "Horizontal" drop-down menu, and click "OK".
Tip 6: Cell message (data validation)
Summary: Display a helpful message when a user selects a specific cell, providing additional context or instructions.
- Select the cell you want to add a message to.
- Go to the Data tab and click "Data Validation".
- Under the "Input Message" tab, enter a title and message.
- Click "OK".
Tip 7: Freeze Top Row (Freeze Panes)
Summary: Keep row headers visible while scrolling through data.
- Method 1: Go to the View tab and click "Freeze Panes" > "Freeze Top Row".
- Method 2: Select the row below the header row. Go to the View tab and click "Freeze Panes" > "Freeze Panes". This is more flexible as you can freeze your data at any sheet, not just the top row.
Tip 8: Freeze Top Row (Table Headers)
Summary: Automatically freeze the top row by creating a table.
- Select the data range, including the header row.
- Press Ctrl + T to create a table, or go to the Insert tab and click "Table".
- Confirm the data range and headers, and click "OK".
Tip 9: Freeze Bottom Row (Split Panes)
Summary: Keep the bottom row visible while navigating through data, useful for displaying totals or summaries.
- Select the row above the row you want to freeze.
- Go to the View tab and click "Split".
- Scroll up or down to view the data while keeping the bottom row visible.
Tip 10: Print Headers on Every Page
Summary: Ensure that row and column headers are printed on every page when printing a large dataset.
- Go to the Page Layout tab and click "Print Titles".
- In the "Rows to Repeat at Top" field, enter the row number(s) containing the header(s).
- Preview the print output to verify that headers appear on every page.
Tip 11: Move cells using Mouse
Summary: Quickly move cells or rows to a new location using the mouse.
- Select the cells or rows you want to move.
- Hover the mouse over the selection border until the cursor changes to a move pointer.
- Click and drag the selection to the desired location, then release the mouse button.
- Optional: Press and hold the Shift key to change to insert mode.
Tip 12: Copy cells using Mouse
Summary: Copy cells or rows to a new location using the mouse.
- Select the cells or rows you want to copy.
- Hover the mouse over the selection border until the cursor changes to a copy pointer (press and hold Ctrl).
- Click and drag the selection to the desired location, then release the mouse button.
Tip 13: Copy visible cells only
Summary: Copy only the visible cells, excluding any hidden or filtered data.
- Hide the rows or columns you don't want to copy.
- Select the visible data range.
- Go to the Home tab and click "Find & Select" > "Go to Special" > "Visible Cells Only".
- Press Ctrl + C to copy and Ctrl + V to paste the visible cells.
Tip 14: Delete blank records
Summary: Remove rows or records that contain blank or empty cells.
- Apply filters to the data (Ctrl + Shift + L).
- In the filter dropdown, deselect all values and select "Blanks".
- Select the blank rows and right-click > "Delete Row".
- Press Ctrl + Shift + L to remove the filters.
Tip 15: Fill blank cells
Summary: Fill blank cells with a specific value or text.
- Select the blank cells you want to fill.
- Press F5 to open the "Go To" dialog box, and click "Special".
- Select "Blanks" and click "OK".
- Enter the desired value or text, and press Ctrl + Enter to fill the blank cells.
Tip 16: Fill numbers to Nth
Summary: Quickly fill a range of cells with sequential numbers up to a specified value.
- Enter the starting number in the first cell.
- Select the cell and go to the Home tab > "Fill" > "Series".
- In the "Series" dialog box, select "Columns" and enter the "Stop Value" (e.g., 30 for numbers 1 to 30).
- Click "OK".
Tip 17: Fill dates to Nth
Summary: Quickly fill a range of cells with sequential dates up to a specified number of days.
- Enter the starting date in the first cell.
- Select the cell and go to the Home tab > "Fill" > "Series".
- In the "Series" dialog box, select "Columns" and enter the "Stop Value" (e.g., 100 for the first 100 days).
- Click "OK".
- Double-click the fill handle (bottom-right corner of the selected cell) to fill the range.
Tip 18: Flash Fill numbers
Summary: Use Excel's Flash Fill feature to extract parts of a number or code based on a pattern.
- Enter the first example in the first cell (e.g., extracting the first four digits from a code).
- Press Ctrl + Enter to confirm the example.
- Press Ctrl + E to let Excel detect the pattern and fill the remaining cells.
Tip 19: Flash Fill letters
Summary: Use Excel's Flash Fill feature to extract initials or parts of text based on a pattern.
- Enter the first example in the first cell (e.g., extracting initials from a full name).
- Press Ctrl + Enter to confirm the example.
- Press Ctrl + E to let Excel detect the pattern and fill the remaining cells.
Tip 20: Smart use of clipboard (multiple items)
Summary: Copy and paste multiple items to and from the clipboard, even across different applications.
- In Excel, go to the Home tab and click the small arrow next to the Clipboard icon to open the Clipboard pane.
- Copy items (text, values, etc.) using Ctrl + C. The items will appear in the Clipboard pane.
- Select the destination cell and click the "Paste All" button in the Clipboard pane.
- To clear the Clipboard pane, click the "Clear All" button.
Tip 21: Quick bar chart (REPT function)
Summary: Create a simple bar chart using the REPT function without relying on Excel's chart tools.
- Enter the formula =REPT("|", value*scale) in the first cell, replacing "value" with the actual value and "scale" with a scaling factor.
- Copy and paste the formula for the remaining values, adjusting the cell references as needed.
- Format the text (e.g., font, color) to enhance the visual representation of the bar chart
Tip 22: McKinsey Lollipop Chart (REPT function)
Summary: Create a "lollipop" chart often used by consulting firms like McKinsey, using the REPT function and conditional formatting.
- Set up the characters for the start, line, and end elements of the chart using special characters or emojis.
- Combine these characters in a formula using REPT to repeat the line character based on the data value and ‘&’ to concatenate the characters.
- Use conditional formatting to highlight the top and bottom values.
- Sort the data to display the top and bottom performers.
Tip 23: Display file path and file name
Summary: Display the full file path, workbook name, and sheet name in a cell using formulas.
- To display the full file path: =CELL("filename", A1)
- To display the workbook name only (Excel 365): = TEXTAFTER (TEXTBEFORE (CELL( "filename" ), "]" ), "[" )
- For older Excel versions, use a longer formula (see ExcelJet.net/formulas/get-workbook-name-only for details).
Tip 24: Hide cell from view
Summary: Hide the contents of a cell from view without deleting or clearing the data.
- Select the cells you want to hide.
- Press Ctrl + 1 to open the Format Cells dialog box.
- In the "Number" tab, select "Custom" and enter three semicolons (;;;) in the "Type" field.
- Click "OK".
Tip 25: Center text across selected cells without merging (VBA)
Summary: Create a custom button to quickly center text across selected cells without merging, using VBA.
- Copy the provided VBA code into a module in your Personal Macro Workbook.
[VBA Code Box]
Sub Center_Across_Selection()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
End With
End Sub
- Customize the Quick Access Toolbar by adding the "Center Across Selection" macro.
- Modify the name and symbol to make it easy to find.
- Select the cells you want to center across a selection, then click the new button to center the text across the selection.
For a beginner’s guide on how to work with VBA macros in Excel, read this article How to Edit Macros in Excel: The Ultimate Beginner's Guide.
Learn how to create and use your own Personal Macro Workbook here: Excel VBA Tutorial: Get your personal macro code vault (aka Personal Macro Workbook)
Tip 26: Remove cell formatting (VBA)
Summary: Create a custom button to quickly remove all formatting from selected cells using VBA.
- Copy the provided VBA code into a module in your Personal Macro Workbook.
[VBA Code Box]
Sub Remove_Formatting()
With Selection
.ClearFormats
End With
End Sub
- Customize the Quick Access Toolbar by adding the "Remove Formatting" macro.
- Modify the name and symbol to make it easy to find.
- Select the cells, then click the new button to remove all formatting. Note this can’t be undone so use it carefully.
Tip 27: Filter() function for single criteria
Summary: Use the FILTER function in Excel 365 to extract data based on a single condition or criteria.
- Type =FILTER(data_range, range = condition) where "data_range" is the range of data, and "condition" is the criteria (e.g., region="East").
- Press Enter, and the function will automatically spill the filtered results.
Tip 28: Filter() function for multiple criteria
Summary: Use the FILTER function in Excel 365 to extract data based on multiple conditions or criteria.
- Type =FILTER(data_range, (range1 = condition1)*(range2 = condition2)) where "data_range" is the range of data, and "condition1" and "condition2" are the criteria separated by * for AND
- Type =FILTER(data_range, (range1 = condition1)+(range2 = condition2)) where "data_range" is the range of data, and "condition1" and "condition2" are the criteria separated by + for OR.
- Press Enter, and the function will automatically spill the filtered results.
Tip 29: Create multiple worksheets from a list (Pivot Table)
Summary: Generate a new worksheet for each unique value in a list using a Pivot Table.
- Create a Pivot Table from the list of values you want to use to make new sheets.
- Add the unique values field to the Pivot Table Filters area.
- Go to Pivot Table Options > Show Report Filter Pages. This creates the new sheets using the values in your list.
- Clean up the generated worksheets by deleting unnecessary data and reordering them.
Tip 30: Create multiple worksheets from a list (VBA)
Summary: Generate a new worksheet for each unique value in a list using VBA, which is faster and more efficient for larger lists.
- Copy the provided VBA code into a module in your Personal Macro Workbook.
[VBA Code Box]
Sub Add_Sheets_From_Selection()
Dim wksCurrent As Worksheet
Dim rngSource As Range
Dim c As Range
Dim wksName As String
Set wksCurrent = ActiveSheet
Set rngSource = Selection.Cells
Application.ScreenUpdating = False
' Loop through every cell in selection
For Each c In rngSource
wksName = Trim(c.Text) ' to remove leading and trailing spaces
If Len(wksName) > 0 Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = wksName
End If
Next c
wksCurrent.Activate
Application.ScreenUpdating = True
End Sub
- Select the list of values in your worksheet.
- Run the macro, and it will create a new worksheet for each value in the list.
For a beginner’s guide on how to work with VBA macros in Excel, read this article How to Edit Macros in Excel: The Ultimate Beginner's Guide.
Learn how to create and use your own Personal Macro Workbook here: Excel VBA Tutorial: Get your personal macro code vault (aka Personal Macro Workbook)
Next Steps
I hope these tips will help you unlock the full potential of Excel and take your skills to the next level.
Here’s an idea for what to do next. See the image above? It’s a clickable table of contents in Excel for the workbook containing the Top 30 Excel tips and tricks. This clickable Master Sheet automatically updates itself and you can customize the fonts, colors, and layout to suit your taste. You can get your own free copy and learn how to build it in my full step-by-step VBA code tutorial here: How to Create a Master Sheet in Excel.
I also have a library of videos you can watch to learn more about Excel. You’ll learn new ways to improve your spreadsheets. Here’s the link to my YouTube channel.
And if you’d like to learn Excel VBA in a structured, methodical way, I have an online course called “Launch Excel Macros & VBA School”. You can find it in the shop.
Happy Excelling!
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.