Mandar Oak
by Mandar Oak
3 min read

Categories

10 can’t miss Excel skills every single user should know: 📊

Custom Lists:

Stop wasting time entering the same customer names, product types, and generally repetitive data.

Creating custom lists is easy.

• Excel • Preferences • Custom Lists • Create and click “Add”

Type the first item, then drag, and the rest of your list appears! <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913308833660928/photo/1’</p></div>

Data Types:

Data Types is a relatively new feature that was added to Excel in 2020.

It allows you to enter text, select data type, then extracts information based on the criteria you select.

Here’s a pretty cool example using states where we can pull population data instantly: <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913325841502209/photo/1’</p></div>

SORTBY:

I’m a fan of organized data.

What I’m not a fan of?

Manually updating my sort every time something changes or new information is added.

Here’s an easy fix.

With the SORTBY function, we can create a table that dynamically adjusts along with our data in real-time. <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913351242211329/photo/1’</p></div>

Unique:

Back in the day, Pivot Tables were my go-to for identifying unique items within large data sets.

Not anymore.

Now I use the “Unique” function in MS365 to easily identify and separate repeated items.

So simple.

Don’t blink or you’ll miss it: <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913364752187393/photo/1’</p></div>

XLOOKUP: (MS365)

If you’re an old-school Excel user, there’s no doubt you’re familiar with V-Lookup.

X is the new & improved version, here’s why:

•Lookup array does NOT have to be only on the left-hand side.

•Allows you to return multiple values at the same time. <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913381537755138/photo/1’</p></div>

Combine Text using “&”

Let’s say you have a list of First & Last names you need to combine.

• Select the destination cell • For our example the function will be: 👉🏻 =A3&” “&B3 • Press “Enter”

It’s that easy. <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913401099960322/photo/1’</p></div>

Did you know you can paste data from Excel into Word or PowerPoint and…

It will update automatically?

• Copy from Excel • “Paste Special” in Word • Select “Paste link” • MS Excel Worksheet Object

The picture in Word updates as data changes in Excel! <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913436608860161/photo/1’</p></div>

Automatic Abbreviations:

If you’re like me, autocorrect is more of an annoyance than a help.

Let’s change that.

Set up your own custom list of abbreviations, and bend its powers to your will.

Type ‘TBD’ Press ‘Enter” Output = ‘To be determined’ <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913471128023040/photo/1’</p></div>

Custom Ribbon Tabs:

The top ribbon in Excel is the home for all of the most commonly used commands.

But what if something you use regularly is missing?

Or what if you want to have a custom group containing only YOUR favorite tools?

Create a Custom Tab, like this: <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913496574844928/photo/1’</p></div>

Camera Snapshot

Here’s a little-known trick to move data between workbooks.

First, add “camera” to your quick access toolbar.

•Select your data ⇢ click 📷 •Paste to new workbook

The cool part: When you update the main workbook, the data in the picture updates as well! 🤯 <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913536794009600/photo/1’</p></div>

Bonus: Flash Fill:

Let’s say you’ve got all the data you need––it just isn’t in the right place.

“Flash Fill” is your friend.

Quickly extract pieces of text from one cell and move them to another OR combine text from multiple cells into a single place. <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913558856069120/photo/1’</p></div>

Need to get better at Excel?

Don’t have much time?

Let me help.

10 Days to Master the Basics:

Link here 👉🏻 https://t.co/xk80SJzWHA <div class='jekyll-twitter-plugin'><p>There was a ‘Not Found’ error fetching URL: ‘https://twitter.com/blakeaburge/status/1555913565420171264/photo/1’</p></div>

@threadreaderapp unroll <div class='jekyll-twitter-plugin'><blockquote class="twitter-tweet"><p lang="en" dir="ltr">Need to get better at Excel?

Don't have much time?

Let me help.

10 Days to Master the Basics:

Link here 👉🏻 https://t.co/xk80SJzWHA pic.twitter.com/oxUeKSOcLG</p>— Blake Burge (@blakeaburge) August 6, 2022</blockquote>

</div>