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!

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:

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.

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:

This thread will make you better at Excel: 📊

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'

Consolidate:

Take data from multiple tabs.

Consolidate into one.

Sum, average, count, it's your choice.

By creating links to the source data, your consolidation tab will update automatically.

The best part? No formulas required.

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! 🤯

People Graph:

I'm a big fan of visual representations of data.

Using 'People Graph' is an easy way to liven up your spreadsheet, especially when tracking metrics related to, well, humans.

Create an infographic to visualize social media engagement, app users, customers, & more.

10 powerful Excel features every user should know: 📊

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.

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! 🤯

Analyze Data:

I was never very good at Pivot tables, graphs, & analyzing data on my own.

Lucky for me (and you) Excel will do the heavy lifting for us.

•Select the range of data
•"Home" ⇢ "Analyze Data"

Charts, tables, & analysis––premade.

Click "insert" & you're done!

X-Lookup

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.

Here's how: 👇🏻

The ultimate guide to Gmail, Gsheets, and Mac Productivity:🧵

📊 -Explore:

Need to do some number crunching?

Let Google's AI do the work for you.

•Highlight the range of data.

•Move your cursor to the starburst-shaped icon in the bottom right corner.

•Click "Explore" – Charts, analytics, & detailed info. generated automatically.

📩 -Look like you're working (when you're not)

Gmail offers scheduling... Who knew?

No need to be physically sitting at your desk, or hammering out emails on your phone after hours.

•Compose your message
•Click the dropdown next to "send"
•Click "schedule send"

💻 -Specific Screenshots:

SHIFT+CMD+3 will capture your entire screen.

Did you know you can also narrow your capture to a specific portion?

It's easy, & now that I know it exists, I use it every day.

A simple SHIFT+CMD+4 and you're on your way.

📊 -Finance:

Monitor stock prices, spot trends, & quickly review historical data using =GOOGLEFINANCE.

Want to see the last 60 days of closing prices for a specific ticker symbol?

Paste the formula below & voila!

=GOOGLEFINANCE("NASDAQ:TSLA","price",TODAY()-60,TODAY())

10 time-saving Excel functions you should be using: 📊

WORKDAY:

Customers need to know when the job will be done.

The Workday function allows you to easily calculate the time between your start date and a specified number of "business days" in the future.

Automatically exclude weekends & holidays of your choosing.

Like this:

3D FORMULAS:

Let's say you want to add up the values in Column 'D' on 4 different sheets & show a single total.

Quit wasting time on SUM formulas that reference each sheet individually.

3D lets you select the 1st + last sheet in a series and dynamically include any in between.

AGGREGATE:

This function returns the result of an aggregate calculation like Average, Count, Sum, Max, or Min.

What makes it special?

Aggregate performs these functions while allowing you to ignore errors, hidden rows, blanks, and more.

Here's how:

IFS FUNCTION:

The IFS function tests multiple conditions to find out if they are true based on the criteria you provide.

Here, we're going to test if our monthly sales increased, decreased, or remained static in relation to our average.

Use '$' to lock in the reference cell.

The 5-step system I use to automate my brain and never forget anything: 🧠

It's well documented that the amount of information we're bombarded with these days is unprecedented.

• Keeping track of it
• Capturing Ideas as they come
• Allowing yourself space to distill it later

None of this happens without a plan.

Here's how to get started:

We'll use a simple set of (3) software platforms to:

• Quickly Capture ideas as they come
– Google Tasks

• Automate sending them to a database
– Zapier

• Organize and categorize for action later
– Notion

All Free.

First things first.

You'll need to set up an account (if you don't have one already) with each of the platforms listed above.

Don't worry––for what we're going to build, the free plans for each should work just fine.

•Google
•Notion
•Zapier

OK, let's start building

1st––Google Tasks

• In your Gmail inbox, it's in the right-side panel

• Or download the app for quick access on your phone.

This is where I capture ideas "on the fly"

No structure, no organization, we'll worry about that later.

Just capture ideas!

You'll get better at Excel with these 8 simple tips: 📊

Print Titles:

Printing a worksheet with several pages?

Want the header to repeat at the top?

You could go to:

Page Layout-> Page Setup-> Sheet-> Rows to repeat at the top-> Select rows

Slooowww!

Instead, select the top row & type:

'print_titles' in the cell name box

Done!

New Window:

I bet you've had times when you wanted to compare items within the same sheet side by side...

There are a few different ways to do this, but my favorite is the 'New Window' feature.

Open 2 instances of the same file & review and edit as you like.

Fast Filters:

Most people know how to create a filter based on a cell's value (the slow way).

What if I told you that you can:

Apply a filter to an entire sheet & filter by a selected cell at the same time?

You can

Select the cell->right click->filter by selected cell value.

Center Across Multiple Cells:

Merged cells are notorious for creating problems.

Here's a quick trick I use to still get the look I want while retaining individual cells behind the scenes.

Select the range
Right-click->Format Cells
Horizontal Alignment
Center Across Selection

9 hidden Chrome features guaranteed to make your life easier: 🖥

Tab Groups:

I've got a bad habit and I'm guessing you do as well.

Too Many Open Tabs!

Tab Groups are an easy way to still keep them open and get organized at the same time.

• Select multiple tabs
• Right-click
• Add tabs to a new group

Expand & retract as needed.

Control Audio / Video playback:

If you're working in one tab while listening to music or watching a video in another, there's no need to navigate away to make adjustments.

Pause, skip to the next track, adjust volume, and more by clicking the music note icon in the menu bar.

Custom search engines:

Turn your address bar into a search engine for basically any site with a "search" function.

Go to a site of your choice
Search for a topic
Copy the URL
Head to settings>Search engine>Site search>Add

Here it is in action:

Keyboard Shortcuts for extensions:

Did you know you can set up Keyboard shortcuts for all of your Google Chrome extensions?

Just enter the URL below and you can do exactly that.

chrome://extensions/shortcuts

Here I'll set one up to quickly access Similarweb on any page:

How to quickly remove blank rows & duplicates in Excel: 📊

• Select Range → Find & Select
• Go To Special → Blanks → CTRL –
• Data Tab → Select Columns To Review
• Remove Duplicates

Done!

If you like these tips, you'd probably like my newsletter as well.

Just sayin'

Join 21K others and sign up for free here:

Want to learn the basics of Excel?

Not sure where to start?

Try

5 Excel Skills You Can Learn Today and Show Off on Monday: 📊

1. Tables + TEXTJOIN

• Select Range
• Format as table
• Combine Name Using TEXTJOIN

Note –– Changes to the formula will carry throughout the table.

2. Organize With Multiple Slicers

• Select Range
• Format as Table
• Insert Slicers of Your Choice

Select individual or multiple categories at the same time.

To select multiple click+drag or select the ✅✅✅ box.

Click the funnel to unfilter.

No need for a pivot table!

3. Quick Interactive Charts

• Select Range
• Format as Table
• Insert Slicer
• Insert Chart

Like this: 👇🏻

4. Custom Text & Icon Sets

• Create Custom Text
• Choose Icon Sets
• Apply Conditional Formatting

No one else in your office is doing this!

5. Pivot Table Grand Total Percentage

Convert numeric values to show as a percentage of the grand total.

• Select Range
• Create Pivot Table
• Right-Click On Data
• Select "Show Values As"
• Percentage of Grand Total