7 Excel Tricks or Treats

Bottom Line: Learn a few shortcuts and productivity tips to save time with Excel. 

Skill Level: Beginner

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

You can use the same file I use in the video to practice these tips.

Excel File Icon 2021 xlsx Excel Tricks or Treats.xlsx

Happy Halloween!

With Halloween 🎃 approaching, I thought it might be appropriate to share a few tricks 😱to improve your productivity in Excel. I hope each of them is a treat 🍫 for you.

But beware! This post is filled with bad puns that are so scary 🧟‍♂️ you'll want to call your mummy 🤕.

1. Horizontal Scrolling

Right off the bat 🦇, I've got a tip that will have you floating through your spreadsheet like a ghost 👻through a graveyard. I'm talking about horizontal scrolling.

Now, of course, you can use the scrollbar at the bottom of Excel to scroll horizontally, but this trick might save you a bit of time.

If you hold the Ctrl and Shift keys down while scrolling with your mouse wheel, you can scroll horizontally across the worksheet. Scroll up to go left and down to go right.

Scroll Horizontally

Another way to speed up your horizontal scrolling is to get a mouse 🐀 with a horizontal scroll wheel. My personal favorite is the MX Master by Logitech.

2. Launch Buttons for the Format Cells Window

The Format Cells window is accessed pretty frequently by many Excel users for various reasons including alignment, text font, borders and shading, etc. Most people access it through the right-click menu or by using the keyboard shortcut Ctrl + 1. But the trick I want to show you is to use one of the dialogue box buttons in the lower right corners of the Font, Alignment, or Number sections on the Home tab.

Clicking on this button will open the Format Cells window with the corresponding tab selected, depending on witch 🧹🧙‍♀️ button you used.

Dialogue Box Opens Format Cells Window with Corresponding Tab Selected
Click to enlarge

3. Copy a Sheet

This is one of my favorite shortcuts that I use all the time. To create a duplicate of the worksheet you are on, left-click and hold the sheet, press Ctrl, drag the sheet, and release. Easy as pie…pumpkin pie!  🎃🥧

How to Copy a Worksheet with Ctrl Left Click

4. Clear Formats

If you've got a worksheet with lots of colorful fonts, fills, and gridlines, and you want to strip all that away quickly, you can use the Clear Formats feature.

On the Home tab, there is a Clear menu that allows you to get rid of just the formatting and leave the contents and formulas as they are. The same menu allows you to strip away comments/notes, hyperlinks, or contents as well.

Clear formats dropdown menu..

When you click the Clear Formats option, it removes all of number, text, and cell formatting leaving just a skeleton 💀 of the data.

Before and after for Clear Formats

5. The Fill Handle

When you hover your mouse over the bottom right corner of a cell, it will change to a plus symbol. If you double-click when the cursor is like that, it will fill down all the cells as far down as the used range in the column to the left of it. If the cell you copied down was a date, it will copy down with sequential dates .

Copy down sequential dates

But perhaps you don't want sequential dates. Maybe you want the same date copied for the whole column. To change this you can click on the Auto Fill Options icon to pull up a menu. Then select Copy Cells.

Auto Fill Options Menu

Or even quicker, you can press Ctrl + D to change the Fill Series to Copy Down. It's one of the fastest shortcuts on the web 🕸 🕷.

Ctrl D Copy Down

6. Preventing Excel from Converting Text to Dates

Sometimes you may write text that looks similar to a date, and Excel automatically recognizes it and converts it to date format. But if it's not a date, you don't want Excel to do that. Here are two ways that you can count 🧛‍♀️ on to prevent Excel from converting text to dates

Type an apostrophe before your text.

If you type an apostrophe before your text entry, Excel will not format it as a date. The apostrophe will be visible in the formula bar but it won't appear in the cell.

use an apostrophe to prevent excel from converting text to a date

You must change the format to Text BEFORE you input the value in the cell(s). The date will not change back to the text you input if you change the format after inputting the value.

Change the Cell Format

Another option is to change the cell formatting before you type the entry. In the Number section of the Home tab, you can select from a range of different format options.

Choose cell format
Click to enlarge

7. Dark Mode

In the spirit 👻 of Halloween, we can change the look of our Excel worksheets to something darker. If you go to File, and then Account, you can change the Office Theme to black.

Office Theme Black

This will change all of the periphery of Excel to a darkened background 🌑.

Dark Mode Excel for Halloween

Conclusion

So there you have seven tricks 😈 that you can treat 🍬 your coworkers to. Do you have a favorite? Let me know in the comments below and feel free to add any tips of your own as well. Stay safe out there this Halloween!

  • Hi Jon.

    Great Tips!

    When I tried to do the CTRL-D tip for copy down (Column D) as in the tutorial, Excel filtered Column E instead and did not perform the copy down. I still had the date series.

    • Thanks Steve! I’m sorry to hear it’s not working for you. You do have to select multiple cells before pressing Ctrl+D. That could be the cause of the issue.

      I hope that helps.

  • something practical and the instruction are nicely and clearly written to help understanding. Thank you for all your work

  • These were all great. Quite a few of the tips were new to me and I will be keeping a record for future use. I spend a fair part of my day using excel and have found dark mode to be a great benefit in preventing tired, sore eyes. It is my favorite.

  • I have a file that is from the Detail for a Pivot Table, I will do a Search (Control +F), keyboard shortcut key + e + v to filter by selected value. Then to remove the filter I use the command that I added to the qat, which for me is Alt + A + 2. Then I have my full table back.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >