Thirty-two of us laughed along with Bob as we learned spreadsheet tips last Thursday. I apologize that I don’t have a recording for you of part one. I’ll continue to try recording events until I get it right. (See Bob’s part two presentation in: Google Sheets Video Training.)

I’ve gone through Bob’s slides and re-created his information for you in this post, so join me for part one…

Excel / Google Sheets Tips and Tricks with Bob Mac Leod

It’ll help you to understand this post if you also follow along in Bob’s slideshow. Click on bit.ly/BobExcelPowerPoint or on the following slide to open his presentation in your browser.

Much of the following information should work for Excel 2010, 2013, and 2016 and for Google Sheets. Google Sheets has “only” the most common features you’d expect for spreadsheets. It also has the ability to link spreadsheets and for users to collaborate. (Excel links but the two files must be in the same place.)

Ready, Set, Go

Also open Excel or Google Sheets. Ready? Let’s try some tips and tricks Bob presents in his slides as we work through the presentation. My post will follow the same order as his slides. I put links in so you can go straight to the slides of whatever topic you’re interested in (like Freeze Panes or Conditional Formatting).

Outline and Links for Excel / Google Sheets Tips & Tricks

If the slides are self-explanatory, I don’t have any comments.

Introduction (slides 1 – 7)

Navigation Tips (slides 8 – 15)

Auto-size Columns & Rows (slides 16 – 19)

Move or Delete Columns or Rows (slides 20 – 21)

For slide 20, watch this YouTube video to see how to use left click + shift to drag and drop a row or column to a new location.

Part of learning a tool is experimenting as well. Try left click + ctrl (or left click + alt) to see what these shortcuts do when you drag and drop.

Freeze Panes (slides 22 – 27)

If you need more help understanding about freeze panes, watch this easily-understood video.

Customize Quick-Access Toolbar (slides 28 – 30)

I had four commands in my toolbar. After Bob’s talk I realized I often go to different Excel tabs (Formulas, Data, etc.) for just one command. So, I added Freeze Panes, Custom Sort, Sum and other commands that I use frequently to my Quick Access Toolbar. I include a screenshot of my toolbar below. It will help me a lot to perform these commands while in the Home tab.

Microsoft has a clear explanation of how to add your commands on their support page.

Your Quick Access Toolbar can be set for all Excel spreadsheets or for specific ones. (Why not add some commands in your other Office products’ toolbars as well?)

Paste Special (slides 31 – 34)

Bob’s slides show you how to access the Paste Special dropdown. Watch this HowCast video for a clear demonstration of these useful Paste Special options:

• Formulas
• Values
• Paste Link
• Transpose

Sum & Count (slides 35  –  36)

To learn more about Excel in general, try searching at support.office.com. Try searching for Sum, Count, and SumIf. Here’s an article and video for anything you might want to know about Countif. (I’ll be using this new tip.)

Text to Columns (slide 37)

So, who does Bob learn from? Mr. Excel. This detailed Mr. Excel video breaks down a text-to-column problem into several steps. This and the Macros section of Bob’s presentation are a bit more advanced.

Conditional Formatting (slide 38)

I’ve done formatting by hand for years. It’s going to be worth my time to learn how to do conditional formating. I found a how-to video which demonstrates it very well.

Macros (slides 39 – 48)

Bob only mentioned Macros at the end of his talk.  A Macro is a recorded instruction that performs a series of tasks for you.

For example, if you always do a similar header for your spreadsheets, a Macro could save you some time. Learn how to create a simple Macro for these steps and then save that Macro in your Quick Access Toolbar.

[UPDATE 05/01/2019: Bob learned that we can record macros in Google Sheets.]

Bonus

The following tips came up during the seminar.

Autofill

If you start a series, like the days of the week or the months of the year, Excel will autofill these when you drag the handle across the column or row. Enter two values, select them and then drag the handle.

This video explains several recurring dates you might want to autofill.

Filters

Besides conditional formatting, try filters. I’ve used them, but after watching this video, I see several new tricks that will help me.

Pin Excel or Star Google Sheets

You may know to click the star for any of your Google Sheets to save them as favorites. Here’s how to do this with pinning in Excel.

When you are in the Open menu, hover over a file name. Notice the thumbtack to the right of your file names. Click it to pin or unpin your files. (You see that I use spreadsheets a lot.)

 

As Bob likes to quote: “Finally then, brethren, we request and exhort you in the Lord Jesus … that you Excel still more.” (1 Thessalonians 4:1) Why not set aside time to learn some time-saving tricks and “Excel still more?”

What new tips and tricks are you going to start using?

 

 

NOTES:

• Bob presented part two, focusing on Google Sheets on April 25th. See the video in Google Sheets Video Training.
• Our giveaways:
  • In the first seminar, a book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time
    • MrExcel has another book coming out in September. I might order it for myself: MrExcel LX The Holy Grail of Excel Tips: Covers Excel Backwards and Forwards
  • In the second seminar, we gave away Google Sheets Cheat Sheets
    • You can also order an Excel 2016 Cheat Sheet
• Microsoft recommends this free and extensive YouTube course to help you learn Excel at Excel is Fun.
• See Flickr for photos from our seminar. My photographer was my husband, Mike Schmitt.