How to create NEAT Excel spreadsheets - that other people can understand and use!

[Andy Black]

I created this 6 minute video last night because I watched the top ranked Excel video on YouTube (that has nearly 7 million views!) - and learning NOTHING in the first 6 minutes (as I complained here).

I hope my video helps!

What's your takeaways?

What will you do different going forward?

View: https://youtu.be/MzJ5MCcDyd4



EDIT: I replaced the video with a new version that has space for call-to-actions on the end.




(Video originally posted here.)

 

Don't like ads? Remove them while supporting the forum: Subscribe to Fastlane Insiders.

[John Clancy]

Two immediate takeaways:

1. Put totals at the top of your sheet when you're dealing with a lot of data (simple, but powerful!)
2. Learn how to use vlookups --> beats trying to manually adjust conversion rates or other variables your formulas might depend on

 

[JAJT]

Great little video.

Excel is one of those tools that I constantly learn new things with - I really should look into finding a nice Excel course or similar to learn many of the basics.

For example - I just found out after YEARS of using it, that absolute references were not only "a thing", but a thing that has a shortcut tied to F4 on your keyboard. I could try to explain it but I found a 1 minute video that does it better:

View: https://www.youtube.com/watch?v=SVPlRHK-XbI

 

[Andy Black]

I really should look into finding a nice Excel course or similar to learn many of the basics.

Funny you say this.

I started (yet another) new progress thread last night:

• An Excel Course?

F2 is good too @JAJT.

 

Don't like ads? Remove them while supporting the forum: Subscribe to Fastlane Insiders.

[AllenCrawley]

You lost me at Excel.

 

[Andy Black]

You lost me at Excel.

Nooooo!!!! It's simple when you know how, and you can do sooo much with it.

You're now my challenge Mr @AllenCrawley. I'll create a quick video and show you some of what you can do with Excel.

 

[cmor16]

Learn how to use vlookups --> beats trying to manually adjust conversion rates or other variables your formulas might depend on

Index and Match functions are more versatile and use less computing power than vlookups, especially on large ranges. They take longer to grasp but worth learning.

 

Don't like ads? Remove them while supporting the forum: Subscribe to Fastlane Insiders.

[Jon L]

I like this a lot, but I think it got into the details too quickly.

I'd like to see something that introduces the concept of simplicity and why its important in Excel. Explain how to approach a problem by looking at the fundamental goals first, and then figuring out how best to achieve those goals.

For example, "I was having a hard time understanding how my business was doing. I had all these numbers provided by my accountant, but with those reports, I had to spend way too long figuring out how things were going. I ended up not looking at my numbers regularly enough and that was becoming a problem."

"My goal was not to create a quick approximation of where things stood. If the resulting numbers were off by 1%, that doesn't matter to me for what I needed."

"So, I took everything and boiled it down to this. Here's how I set it up so that it was easy to update."

...

And here are the reports from my accountant that I never look at...

 

[Andy Black]

I like this a lot, but I think it got into the details too quickly.

I'd like to see something that introduces the concept of simplicity and why its important in Excel. Explain how to approach a problem by looking at the fundamental goals first, and then figuring out how best to achieve those goals.

For example, "I was having a hard time understanding how my business was doing. I had all these numbers provided by my accountant, but with those reports, I had to spend way too long figuring out how things were going. I ended up not looking at my numbers regularly enough and that was becoming a problem."

"My goal was not to create a quick approximation of where things stood. If the resulting numbers were off by 1%, that doesn't matter to me for what I needed."

"So, I took everything and boiled it down to this. Here's how I set it up so that it was easy to update."

...

And here are the reports from my accountant that I never look at...

I'll take that onboard @Jon L.

I actually knocked this up quickly last night after watching the first 6 minutes of a video on YouTube and learning NOTHING practical in those first 6 minutes. I complain about it here:

• An Excel Course?

I *want* to get into the details quickly. I'm of the belief that the video title and thumbnail should set the scene enough that the video doesn't need introduced. It's how I've done all the videos in my Google Ads course and people thanked me for it. I suspect it's a breath of fresh air for the people similar to me. We'll see though!

 

[Jon L]

I'll take that onboard @Jon L.

I actually knocked this up quickly last night after trying to watch the first 6 minutes of a video on YouTube and learning NOTHING practical in the first 6 minutes. I complain about it here:

• An Excel Course?

I *want* to get into the details quickly. I'm of the belief that the video title and thumbnail should set the scene enough that the video doesn't need introduced. It's how I've done all the videos in my Google Ads course and people thanked me for it. I suspect it's a breath of fresh air for the people similar to me. We'll see though!

ok I changed my mind. I like your idea better.

You could create a separate video then about how to approach a problem in Excel - how do you think though what you want your Excel spreadsheet to do, whats the value of simplicity, etc.

 

Don't like ads? Remove them while supporting the forum: Subscribe to Fastlane Insiders.

[Andy Black]

ok I changed my mind. I like your idea better.

You could create a separate video then about how to approach a problem in Excel - how do you think though what you want your Excel spreadsheet to do, whats the value of simplicity, etc.

Totally. I'm aiming to create a series of no-fluff videos that total ONE hour only, that take beginners to competent and that *also* take competent folks to another level. I think I managed to achieve that with The AdWords Jumpstart, so now I want to do the same with Excel.

Simplicity, best practices, "maintainability", etc.

How to *think* neater in the first place as well!

 

[Jon L]

Totally. I'm aiming to create a series of videos that total ONE hour only, that take beginners to competent and that take competent folks to where they're creating simpler but more powerful tools for themselves. Simplicity, best practices, maintainability, etc. How to *think* neater in the first place as well!

you have to do one on pivot charts and tables ... too few people know about them or know how cool they are.

 

[JAJT]

F2 is good too @JAJT.

I DID know that one!

I think it was the first shortcut I ever looked up as I'm 100% a keyboard guy at heart - I'm far more quicker and efficient with it than a mouse.

An Excel Course?

Following!

 

Don't like ads? Remove them while supporting the forum: Subscribe to Fastlane Insiders.

[Andy Black]

I DID know that one!

I think it was the first shortcut I ever looked up as I'm 100% a keyboard guy at heart - I'm far more quicker and efficient with it than a mouse.



Following!

Ok. Let me see.

What about Ctrl and ' and Enter to populate the range below a cell with the same contents as that cell (so long as the cell with focus is the one directly below the one with the data you want to copy)?

 

[The-J]

People who can do Excel well are magic in my opinion. This is an excellent introduction, call it 'Beginner Magic Spells w/ Excel'

With that said, I've found simple pivot tables to be really, really cool tools for making sense of large amounts of categorized data.

Also, I've mainly used index(match() to do what vlookup does, although I have no idea why one would be better than the other.

 

[edgefinder]

Totally. I'm aiming to create a series of no-fluff videos that total ONE hour only, that take beginners to competent and that *also* take competent folks to another level. I think I managed to achieve that with The AdWords Jumpstart, so now I want to do the same with Excel.

Simplicity, best practices, "maintainability", etc.

How to *think* neater in the first place as well!

Thank you @Andy Black - your advice is golden.

I’m listening, and have put together a quick video for lower back pain in my case.

As you are doing with the excel video’s here.

 

Don't like ads? Remove them while supporting the forum: Subscribe to Fastlane Insiders.

[VTK]

Cheers Andy, these are awesome.

In terms of feedback, getting the keyboard short-cut annotations is a huge help for Excel/Sheets novices. Watching a pro working away, you hear the clack of keys and see columns populating with data and formulas out of nowhere and are left a bit like:



Seems like pure magic tricks.

Same goes for formulas if the video definition isn't high. Everything can be looked up on Google (and I understand that is part of the process) but busy entrepreneurs will definitely love a channel that puts it all on a plate for them.

 

[Andy Black]

Cheers Andy, these are awesome.

In terms of feedback, getting the keyboard short-cut annotations is a huge help for Excel/Sheets novices. Watching a pro working away, you hear the clack of keys and see columns populating with data and formulas out of nowhere and are left a bit like:

View attachment 32390

Seems like pure magic tricks.

Same goes for formulas if the video definition isn't high. Everything can be looked up on Google (and I understand that is part of the process) but busy entrepreneurs will definitely love a channel that puts it all on a plate for them.

I hear you.

The memes though... I literally choked.