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

Download the Excel File

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

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.

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.

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!  🎃🥧

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.

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

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 .

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.

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 🕸 🕷.

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.

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.

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.

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

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!