What the experts wish they had known earlier

If there is one thing I am certain of, it is this: There is always something new to learn in Excel.  Some of those things will be small little tips and tricks, which might help you a bit, but other things you learn will be game changers.  These game changers are the things which save hours of time every week, which turn something complex into something easy, which make us say to ourselves “I wish I had known that earlier”.  The problem is, we don’t know what we don’t know.  So, I asked some experts: “What is the one thing about Excel you wish you had known earlier“.  Through their answers I hope we can find those game changers and focus our learning in areas which we know has worked for them.

Sumit Bansal – Trump Excel

Sumit Bansal

“One feature I wish I has used more often is Excel Tables.

Converting tabular data into an Excel table can really simplify its further usage. It makes it so easy for you to refer to the data points by name instead of references.

It can account for new data points, which is useful when creating dashboards or data models. Now, whenever I create an Excel Dashboard, the first thing I do is convert the data into an Excel Table.”

If you want to know more about Tables then read Sumit’s article here: https://trumpexcel.com/excel-table/

Dave Bruns – Excel Jet

Dave Bruns

“I wish I had known about SUMPRODUCT earlier. The SUMPRODUCT function is elegant, versatile, and logical. But SUMPRODUCT may seem scary, or even boring, at first glance. Don’t let this put you off – it’s worth your time, and get you out of many tough spots.

You can use SUMPRODUCT to count and sum like COUNTIFS and SUMIFS, but with far more flexibility. And SUMPRODUCT can handle arrays natively, without control + shift + enter. This means you can easily do things like this:

=SUMPRODUCT(LEN(A1:A10))

to count all characters in A1:A10.

Or this:

=SUMPRODUCT((LEN(A1:A10)>5)*(B1:B10=”red”))

to count rows with more than 5 characters in column A and “red” in column B.

Or this:

=SUMPRODUCT((MONTH(dates)=3)*(WEEKDAY(dates)=2)*sales)

to sum total sales on Mondays in March.

There are many uses for SUMPRODUCT, and it’s a great way to start learning basic array formulas in Excel.”

Discover more about SUMPRODUCT in Dave’s article here:  https://exceljet.net/excel-functions/excel-sumproduct-function

David Hager – Excel For You

David Hager

“I was one of the original MVPs selected when the program was created. 1995 – 2003

I cut my Excel teeth with array formulas and the xlm macro language. So, I was not ready to learn VBA when it came out in 1995. That reluctance slowed my development as a professional Excel developer. I have seen recently the same reluctance with Excel pros to spend the time to learn about DAX and Power Query and I encourage them to jump right in.”

You can read David’s articles at his website: https://dhexcel1.wordpress.com/

Kasper Langmann – Spreadsheeto

Kasper Langmann

“When I first started out with Excel I was working with VLOOKUP a lot – as most people do. I used VLOOKUP in enormous spreadsheets.
In cases where the return column was left of the lookup column, I spent some serious hours rearranging data.

If I had just known about INDEX + MATCH at the time, it could’ve saved me some many hours.

So, if you find yourself using VLOOKUP a couple of times a week, then you owe it to yourself to learn INDEX + MATCH.”

Want to learn INDEX/MATCH?  Check out Kasper’s article: http://spreadsheeto.com/index-match/

I also have a post about the advanced uses for INDEX MATCH and why it is better and VLOOKUP.

Sam McIntire – Deskbright

Sam McIntire

“Of all of Excel’s features, there’s one that I learned extremely late and wish I’d picked up earlier: Tables.

Tables are a fantastic way to format data in Excel quickly and easily — and they have several advantages:

  • They automatically format your data beautifully, with nicely-striped rows and clear headers;
  • They automatically name ranges within your data set, so you can reference them by name rather than by cell number; and
  • They automatically populate formulas throughout columns when changed — saving you a ton of “Copy and Paste” time.”

For more information on Tables, you can check out Deskbright’s article at https://www.deskbright.com/excel/using-tables-in-excel/.

John Michaloudis – My Excel Online

John Michaloudis

“My answer is Pivot Tables!  I used to use lots of formulas to analyse my data set.

Learning formulas is not easy as there is lots of trial, error and frustration.  Formulas are great once you know them but the learning curve can be slow and steady.

Fast forward to Pivot Tables.

With its one click set up and drag and drop ease, you will forget the pain of using formulas to analyse your data.

Pivot Tables allow you to analyse your data in a quick summary report, giving you key metrics in a dashboard that lead to insightful business decisions.”

You can check out John’s free webinar on Pivot Tables to find out how you can easily create Pivot Tables: http://www.myexcelonline.com/pivottable-webinar

Alan Murray – Computer Gaga

Alan Murray

“My biggest Excel ‘wow’ moment was understanding the power of SUMPRODUCT. It is such a powerful and versatile function that I have written and spoken about it many times.

Its purpose (as its name implies) is to find the product from ranges of values and then to sum them.  However, this can be manipulated to get SUMPRODUCT achieving some impressive tasks.

It is commonly used as an alternative to array formulas. It can sum, count or lookup values dependent upon any number of conditions. You can see some of these examples in my 5 groovy SUMPRODUCT examples tutorial.

I have also used it to create my automated sports league tables because of its ability to handle the complex criteria used in league rankings.  The potential is so vast; I think that it is a life changing Excel moment.”

New to this wonderful function?  Learn about the SUMPRODUCT function here: https://www.computergaga.com/blog/5-groovy-sumproduct-examples-advanced-excel-formula-tutorials/

Chris Newman – The Spreadsheet Guru

Chris Newman

“My initial thought was to venture off into keyboard shortcuts. The F4 and Alt + Tab shortcuts were a couple that came to mind as I literally remember my jaw dropping to the floor when I learned what they could do.  But I decided not to go that route, because keyboard shortcuts, in general, can save anyone an enormous amount of time, but the power of keyboard shortcuts are dependent on how an individual user uses Excel. 

The one thing about Excel I wish I would have known earlier was how to write Custom Number Formats.  Custom Number Formats are awesome because they can visually mask values allowing your formulas to do the math behind the scenes and your audience to easily comprehend how the data is organized.

Picture this, you have column headers called “Jan”, “Feb”, “Mar” but you need to perform a SUMIFS() function on date ranges encompassing those months. Instead of adding helper cells or creating a complex formula, you could simply change your headers to dates (ie 1/1/2017, 2/1/2017, 3/1/2017) and use a custom number format to make it appear as though the original header text (ie “Jan”, “Feb”, “Mar”) still remained.

I could go into a bunch more examples of how custom number formats can improve the setup of your spreadsheets from inserting up/down arrow symbols for variances to making your numbers display in millions. The bottom line is if you understand how to write custom number format rules the possibilities to how you can mask your values is almost endless. This is definitely an Excel feature I currently use quite often and I definitely wish I would have mastered it years ago.”

Check out Chris’ tutorials and time-saving Add-ins at his website: https://www.thespreadsheetguru.com/

Oz du Soleil – Excel On Fire

Oz du Soleil

“What’s one thing I would like to have known earlier about Excel? Tables!

Had I known about tables I could have skipped years of writing VBA code and building complicated, clumsy dashboards.

Tables. Beautiful Tables. 

Rather than having a formula refer to a static range, the formula can reference a specific column or row in a table. Tables automatically absorb new data, reducing the chance of having calculations that don’t update with changes. A pivot table that’s tied to a Table will easily flex as the source data in the Table changes.

But how did I miss Tables for so long? Because the Tables icon in Excel looks exactly like the Table icon in Word.

A Table in Word is just a grid. It doesn’t do much. Thus, I assumed the Table in Excel was a feckless grid, and I couldn’t understand why anyone would put a grid on a grid.

And one day … one day I was made aware of the majesty. “

Oz is the master of data analysis and manipulation.  Check out Oz’s You Tube Channel, Excel on Fire: https://www.youtube.com/c/OzduSoleilDATA

Ryan Wells – WellsR.com

Ryan Wells

“One feature of Excel I wish I had known earlier was that you can create your own powerful user-defined functions to shorten complex formulas that you find yourself repeatedly typing.

For example, I often have to compare output from different sources to see if they match, so I made this Compare Function to avoid having to type a complicated =IF() statement each time.  I also created one to make Linear Interpolation easier.

The best part is, you can save your custom functions as an Excel Add-in so they’re always there when you need them! All you have to do is go to File > Save-As and select “Excel Add-In” from the dropdown menu. Install the add-in and you’re in business!”

Ryan’s blog is filled with excellent VBA articles: http://wellsr.com/

There are many user-defined functions here on Excel Off The Grid which you might find interesting:

Niels Weterings – Excel Easy

Niels Weterings

“I wish I would have known earlier how to create array formulas in Excel. Single cell array formulas perform multiple calculations in one cell.

Most beginners don’t know that you need to finish an array formula by pressing CTRL + SHIFT + ENTER. The formula bar indicates that you’ve entered an array formula by surrounding your formula with curly braces {}. Do not type these yourself. For example:

To count all characters in A1:A5 use

{=SUM(LEN(A1:A5))}

Without this array formula, you would have to create an extra range (B1:B5) with the number of characters in each cell. Next, =SUM(B1:B5) gives the exact same result.

To sum a range (A1:A7) with errors use

{=SUM(IFERROR(A1:A7,0))}

To sum the 4 largest numbers in A1:A11 use

{=SUM(LARGE(A1:A11,{1,2,3,4}))}

The sky is the limit! Array formulas eliminate intermediate calculations and keep your worksheet clean.”

To learn much more about array formulas in Excel, visit: http://www.excel-easy.com/functions/array-formulas.html

Find my article about using basic array formulas here.

What about me?

When dealing with Excel, I am constantly saying “I wish I had known that earlier”.  But, if I had to pick one thing myself, it would be understanding the real power of the INDEX function.

INDEX is such a simple function that it doesn’t really stand out as being anything special.  But, I hadn’t realized one significant thing . . . INDEX does not return a value!  Let me say that again, just to make in sink in . . . INDEX does not return a value!  If you’re confused by this I am not surprised.  When we use the INDEX function, instead of returning a value it returns a cell address.

=INDEX(A1:A10,4)    is exactly the same a writing    =A4.

Separate two INDEX functions by a colon and you’ve got yourself a dynamic range

=SUM(INDEX(A1:A10,4):INDEX(B1:B10,9))    is exactly the same as    =SUM(A4:B9)

This opened up the door to a whole new world of dynamics and advanced formulas which I had no idea was even possible.  Check out these posts for examples of using INDEX in new ways:

Conclusion

Is there anything in there which you haven’t mastered yet?  I mean, truly mastered.  If so take the time to learn these Excel features and techniques.  You may just discover something amazing which you didn’t even know about.

For each problem we face in Excel we have to create solutions, and each of those solutions teaches us something new.  It can be frustrating to finally discover something which would have made our lives easier, had we known about it.  However, I would like to finish this post with the answer from Jon Peltier, because as frustrating as it can be, it is a learning journey.

Jon Peltier – Peltiertech

Jon Peliter

“Certainly there have been things about Excel that I’ve found out long after it would have been useful, but there has usually been another way to get what I’ve needed. When I learn about the particular thing that I didn’t know, my response is usually “Hmm, something else” rather than “I wish I knew”.”

Jon has the best Excel charts website out there, including some time saving Add-ins.  Check it out here: http://peltiertech.com/


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

4 thoughts on “What the experts wish they had known earlier”

    • Tables are amazing, also I use the Create relationships in Diagram View in Power Pivot. I create a table for a category. That allows me to change the category description, to display it in a Dashboard.

      Reply
  1. Ever wonder how large corporations ran their accounts before the days of accounting software? If you guessed Spreadsheet Software, give yourself a pat on the back. It wasn’t called MS-Excel back then (Bill Gates was still in his nappies… or just growing facial hair) – it had other names; like VisiCalc (for those who were around at that time) Lotus (I was surely around for this one).

    Check out AM’s tutorials: https://www.am18.co.uk/

    Reply

Leave a Comment