Excel Tips and Tricks

Summary

The video provides 11 Excel tips and tricks to work faster and more efficiently in Excel. It covers techniques like dragging columns to rearrange them easily, using keyboard shortcuts to quickly sum cells, multiplying a range by a number to update values, getting unique counts, dissecting complex formulas, creating custom lists, leveraging named ranges, displaying metrics in the status bar, extracting data with Flash Fill, using Ctrl + Enter to apply functions to multiple cells, and customizing the Quick Access toolbar.

Takeaways

• 😀 You can move columns by dragging and dropping instead of cut and paste
• 😮 Use alt+= as a shortcut to sum a range of cells
• 🤯 Multiply or divide values in a range without formulas using Paste Special
• 📈 Easily get a distinct count of values with the UNIQUE function
• 💡 Break down complex formulas using the Evaluate Formula tool
• 🍪 Create custom lists to auto-populate drop downs
• 🏷 Name cells/ranges to simplify formulas
• 📊 Quickly view count, sum, avg etc for a range in the status bar
• ⚡ Use Flash Fill to extract or combine data from columns
• ⛑️ Add common commands to the Quick Access toolbar for easy access

Q & A

How can you move a column in Excel by dragging and dropping?

You can highlight the entire column, hover over the edge of the selection until the cursor changes to an arrow icon, then press the Shift key along with the left mouse button and drag the column wherever you want.

What is the shortcut key to sum up a range of cells in Excel?

The shortcut key to sum up a range of cells in Excel is Alt +=. You can select the cells first or select them after pressing the keys.

How can you multiply or divide all values in a range in Excel without using formulas?

You can enter a number like 10 in a cell, copy that cell, select the range you want to apply the operation to, go to the Home tab, click Paste Special, select Multiply or Divide, and click OK. This will apply that operation to all the selected cells.

What is the Excel function to get a distinct count of values?

The UNIQUE function in Excel returns the distinct values from a range or array. You can combine it with the COUNTA function to get a count of those distinct values.

How can you evaluate parts of a complex nested function in Excel to understand how it works?

You can click on a part of the nested function in the formula bar and press the F9 key to show the result of just that portion. Or you can click the function helper icon next to each part to view details about that specific function.

How do you create a custom list to use with the fill handle in Excel?

Go to File > Options > Advanced, scroll down to the Edit Custom Lists section, click New List, enter your list items or select them from a sheet, and click Add. Then you can use that list with the fill handle to populate cells.

What are named ranges and how can they be useful when writing formulas?

Named ranges give names to cells or ranges of cells so you can reference them by name instead of by cell address. They make formulas easier to read and understand. You can create them by selecting cells and entering a name in the Name Box.

How can the status bar provide quick metrics when selecting a range?

When you select a range of cells, the Excel status bar shows the count, sum, average, numerical count, min and max of those cells. You can also right click the status bar to customize the metrics displayed.

What does the Flash Fill feature allow you to do?

Flash Fill lets you extract parts of text from one column into another with just a couple examples. It can also combine text from multiple columns. This automates tasks that would normally require tedious manual work.

How does control + enter work when entering a formula across multiple cells?

Instead of pressing just Enter after typing your formula, press Ctrl + Enter. This will apply that formula to all the selected cells instead of just the active cell.

Outlines

00:00

😊 Moving Columns Easily by Dragging and Dropping

This paragraph explains how you can easily move columns in Excel by dragging and dropping. Rather than inserting and deleting columns, you can simply select the column, hover over the edge until the mouse turns into an arrow icon, hold down shift and click the left mouse button, then drag the column wherever you want. You can do this with individual cells, multiple columns, or entire columns along with their formatting.

05:01

😃 Using Alt+= to Quickly Sum Cells

This paragraph introduces a useful Excel shortcut to instantly sum cells. Rather than manually selecting the cells and inserting the SUM formula, you can simply select the cells you want totaled, then press Alt+= (holding down the Alt key while pressing the Equals key). This will output the sum of the selected cells quickly and easily.

10:03

😲 Multiplying/Dividing Values without Formulas

This paragraph explains how you can easily multiply or divide cell values without formulas using paste special. Rather than manually changing each cell, select the cells, copy a multiplier/divisor, use CTRL+ALT+V to open paste special, select multiply/divide, and click ok to apply the operation to all selected cells.

15:04

🤓 Getting Unique Values with New Excel Function

This paragraph introduces Excel's new UNIQUE function to easily retrieve distinct values from a range. By passing the data range into UNIQUE, it will return only the unique values. You can combine with COUNTA to get a count of distinct values. This simplifies what previously required more complex formulas.

😎 Evaluating Complex Formulas Step-by-Step

This paragraph explains techniques to understand complicated, nested Excel formulas. You can click on individual nested functions in the formula bar to view info on arguments. You can also highlight portions and press F9 to evaluate just that part of the formula, seeing intermediate steps.

🤩 Creating Custom Lists for Quick Data Entry

This paragraph shows how to make custom lists in Excel that allow quick autofill data entry. After defining your list in the options menu, you can start entering values and drag the fill handle down to populate based on your list. You can also sort other data by the custom list order.

😇 Using Named Ranges to Improve Formulas

This paragraph explains how giving cells or ranges friendly names can simplify formulas. Rather than using obscure cell references, you can define names that allow formulas to clearly refer to values like "tax_rate" and "Q1_profit". This enhances readability and maintenance.

🤑 Quick Metrics on Status Bar with CTRL+Shift+↓

This paragraph introduces using the Excel status bar to instantly view summary metrics on a data set without formulas. Select your range, use CTRL+Shift+down arrow to select, then metrics like count, sum, average, min, max appear on the status bar.

⚡️ Flash Fill to Extract/Combine Data

This paragraph explains Flash Fill, a powerful Excel capability to automatically extract or combine data without formulas based on examples you provide. Enter a few examples of extracting first names or concatenating fields, then use Flash Fill to apply across entire columns with one click.

🚀 Apply Formulas to Multiple Cells with CTRL+ENTER

This paragraph shows how CTRL+Enter allows applying a formula you've written or function you've inserted to multiple selected cells at once rather than just the active cell. This speeds up repetitive formula-based data population or analysis across rows/columns.

Keywords

💡Dragging and dropping

This refers to the ability to reorder columns in Excel simply by clicking and dragging them to a new position. This makes reorganizing data easier without complex steps. The video emphasizes keyboard shortcuts and techniques to streamline working in Excel.

💡Shortcut keys

The video introduces shortcut keys in Excel to perform common actions more efficiently, like ALT + = to quickly sum a range of cells. Using shortcuts speeds up workflows in Excel without needing the mouse.

💡Extract information

The video demonstrates how to pull or extract useful bits data or text from full columns in Excel, like names or domains from emails. This cleans up data and extracts only the most relevant parts.

💡Formulas

Formulas are used to perform calculations in Excel. The video shows shortcuts to enter formulas across multiple cells to save time.

💡Functions

Functions are preset formulas in Excel for common tasks like SUM(). The video explores Excel's expanding library of functions to streamline working with data.

💡Format data

The video shows techniques to quickly format or restyle data in Excel with paste special options, instead of manual edits.

💡Custom lists

The video demonstrates creating sorted custom lists in Excel to populate data, like lists of cookie types for the cookie company example.

💡Status bar

The status bar at the bottom of the Excel window provides quick metrics like sum, count, min, max etc when cells are selected, avoiding manual formulas.

💡Flash fill

Flash fill is an Excel feature that generates patterns from examples to fill columns, like extracting first names or concatenating names based on the first manually filled cell.

💡Quick access toolbar

The quick access toolbar allows you to customize Excel by adding commonly used commands for quick access. This further optimizes the workflow.

Highlights

You can move columns by dragging and dropping

Use Alt + = shortcut key to sum up cells

Paste Special menu allows calculations without formulas

UNIQUE function gives distinct count

Click inside formula bar to evaluate parts of complex functions

Create custom lists for auto-fill

Use named ranges to simplify formulas

Status bar gives quick metrics like count, sum, avg

Flash fill to extract or combine data

Ctrl + Enter enters formula into multiple cells

Creative way to use Ctrl + Enter to fill blanks

Add commonly used commands to Quick Access Toolbar

Can move columns by dragging and dropping

Sum cells easily with Alt + =

No need for formulas, use Paste Special for calculations

Transcripts

you can move a column simply by dragging

and dropping

you can sum up cells with a simple

shortcut key

you can extract information out of a

column with just the click of your mouse

hi everyone kevin here today

we're going to look at even more excel

tips and tricks just like these and i'm

sure you'll likely find a new one that

you haven't seen before

to follow along i've included a sample

workbook down below in the description

alright

let's check these out

this brings us to the first tip you can

move columns simply by dragging and

dropping here i have the customer name

and i want this to be the first column

in this table now i could insert a new

column then i could copy all these

contents cut it paste it and then i

could delete this column but

that's a lot of clicks and on the kevin

stratford youtube channel we don't like

clicks so instead we can highlight all

the contents of this column and here

i'll hover over the edge of the

selection and there you see that it

changes to an arrow icon

now i'll press the shift key together

with my left mouse button and now i can

drag this column wherever i want it i

don't place it all the way over on the

left

you can also do this with multiple

columns here for example i'll select

multiple columns and do the exact same

thing here i'll move all of these over

and that works just as well

you can also do it with just individual

cells here i'll select two cells hover

over the edge and then once again shift

and left click and here i can drag it up

or down

i can also use my right mouse button to

do the exact same thing but it requires

a few more clicks here for example i

will highlight the phone column i'll

hover over the edge now i'll press my

right mouse button and here i can drag

it to a new position and this opens up a

context menu and down here here i can

shift right and move and that once again

moves that column and if you notice with

that context menu i had a few other

options so let's see what those do

here this column header has no

formatting i could also move over the

formatting i'll select zip hover over

the edge and with my right mouse button

drag over to country and here i have the

option to move over just the formatting

that's exactly what i wanted

over here i have the customer names and

i've used various functions to clean

them up but i just want to keep the

customer name

here i could highlight all the customer

names i could hover over the edge right

click drag over drag back and here i

have the option to just copy over the

values and all of those functions now

disappear

tip number two i want to sum up various

cells in just an instant and here we

have cookie sales by customer at the

kevin cookie company and i want to know

how many chocolate chip cookies did we

sell across all of our customers and if

you know excel there's a function that

does that you can type in equal sum

open parentheses and i could highlight

all these cells close parentheses hit

enter and that gives me the sum but once

again we don't like clicks here so how

do we make this easier well instead i

can press alt and equals

and then i can hit enter and i have my

sum so alt equals is the shortcut key

for summing

now here i could also select all of the

cells ahead of time and then i could

press alt equals and that just gives me

the sum

i'll delete these two values now i could

also highlight three columns and here

i'll press alt equals and that gives me

the total across all of them so even

easier now you might be wondering well

this works top down but does it also

work left to right and it sure does here

i can press alt equals and that also

gives me the sum

now let's say i want to get the sum

across all of these columns and all of

these rows

you guessed it you can also use alt

equals here i'll select all of these

cells along with the column in the row

where i want the totals and once again

let's press alt equals and look at that

my job has just gotten so much easier

this brings us to tip number three you

can perform calculations

without formulas

here we have profit by customer and year

at the kevin cookie company

and hopefully you notice this almost

immediately but obviously this profit's

wrong i mean we made way more than this

i mean this this is in no way our profit

here it's it's way more it's probably at

least 10 times as big so let's say i

want this all to be 10 times bigger so

how would we do this well one way you

could do it is you could enter equals

take this value multiply by 10 and i

could basically recreate this table

multiply everything by 10 and do all

that but once again that's a lot of work

and i can copy and paste this in but it

takes a lot of clicks

so instead if we want to make everything

10 times bigger let's enter a 10 here

and then i'll click on this cell and

let's copy that

next let's highlight all of these cells

within the table then click on home and

here click on paste

paste special

and right here in this dialog you have

different operations so here i can add

10 to every single value that's

highlighted or here i can subtract 10

from every single value that's

highlighted i could also multiply and

divide

now i said our profit was 10 times

greater than what's shown here so i also

select multiply and then click on ok and

now it's multiplied everything by 10.

now instead of going through that menu

and clicking on paste special i want to

show you an even easier way to do it so

here once again i'll copy 10 highlight

all of these and a quick shortcut to get

to that paste special menu you can press

control alt and v

and that opens up that same dialog

and here i can click on multiply and

it still does look kind of low so maybe

i should go through and let's multiply

this by a way bigger number and that

will accurately reflect our profit

tip number four excel now makes it

really easy to get a unique or distinct

count

here at the cookie company we had lots

of orders and each order contained

cookies but i want to know how many

unique or distinct cookies do we sell

here at the cookie company

excel now has a function that does this

let's click on insert function right up

here and let's type in unique that's the

name of the function click on go and

let's select this function here it asks

me for the array or basically the range

or array from which to return unique

values so here i'll simply select this

entire list and then i have two other

parameters that i can pass into this

function but they're optional so i'll

just leave them as is and then click on

ok

so here now i can see all the unique

types of cookies that we sell here at

the kevin cookie company now i wanted a

count of how many unique cookies we sell

so i can combine this with another

function

there's a function called count a and

that counts all of the non-blank rows

then i can insert a parentheses and then

i can close the parentheses basically i

want a count of all the unique items

that we got back here

then i could hit enter and here i see

that we sell six types of cookies here

this brings us to tip number five and

this is especially helpful if you happen

to write complicated functions here for

example i wrote a function that pulls

out the domain name from an email

address and here down below you can see

what the function is to do that it's a

little complex i have a function and

then two other functions within it so

how does it work well we can use a very

easy technique to understand how it

comes together

when i have this cell selected right up

here in the formula bar we can see what

the formula is and let's say i want to

understand well how does this find

portion of the function work well here i

can click on find and here i can click

on the function helper and this will

open up the function arguments just for

the find function so here i can see what

it's looking for within what text and i

can make sense of how this portion works

here i'll click on ok then i can click

on len here i can click on the insert

function helper and then here i can

understand just how this function works

and what arguments i need to pass in and

here i can click on right click on

insert function and i can see just how

this portion works and also for this

function i can see what the result is

now another way to see the results of

just a portion of the function here i

can highlight just one portion and then

i can press the f9 or the function 9 key

to give me the results of just that

portion here i'll press control z or

here i can select the len portion i'll

press f9 and i could evaluate just that

portion of the function or here i could

select the entire thing and press f9 and

evaluate the entire thing so these are

some quick techniques that you can use

to understand complex and nested

functions

tip number six you can make your own

custom list in excel

so here we see the month of january and

here i can click on this fill handle

drag it down and i can fill out all the

months of the year that's pretty cool

also here we have the abbreviation for

sunday

here i can click on the fill handle and

i can very quickly populate all the days

of the week

and over here we have chocolate chip and

you might be wondering well this is

what does this do well we sell cookies

here and here if i drag down i can

populate all the different cookies that

we sell here at the kevin cookie company

so you can create your own custom list

and it's actually pretty easy to do

click on the file menu then go down to

options

within options let's click on advanced

and then go all the way down to the

bottom and here you have the option to

edit custom lists

here you'll see some of the lists that

we already looked at you have sunday

monday tuesday you have all the months

of the year and down below here's my

list that i created with different

cookie types you can click on new list

and here you can even select values from

an existing sheet to make a new list and

then click on ok

then click on ok again and now you have

your own custom list that you can use

and the neat thing is you could also

sort by your custom list so here we have

a whole bunch of orders and they're

currently sorted alphabetically i could

click on data

and then here i could sort

alphabetically in ascending order or i

could sort in descending order but let's

say i want it sorted in this specific

order i can do that i'll click on data

then let's click on sort

and over here i want to sort based on

the cookie name but instead of z to a or

a to z i can select custom list and

here's my list that i created i'll click

on ok and okay and now it's sorted in

this exact format

tip number seven you can make it easier

to write formulas by using named ranges

here for example i want to calculate the

tax that we had to pay on the profit for

all of these different cookie sales now

here i could enter a formula i could

type in equals and let's take the profit

here and then multiply it by the tax now

i probably also want to make this an

absolute reference so here when i copy

over the formula to these other columns

it'll continue to work and that works

but the formula is not really that clear

and it takes a bit of effort

so instead here i'll click into the tax

rate cell

and then i can click up here and i can

call this tax rate so i'm giving this

cell a name now i can click on enter now

instead i can click equals

select 14 and then i can multiply it by

the tax rate look at that

now let's say i forget what the name is

here i could enter equals select this

cell multiply and then i can press the

f3 key that pulls up the name manager

and here i can see that i've defined one

name the tax rate i can double click on

that that inserts it into my formula i

can hit enter and then it calculates the

profit

i can also add several items to the name

manager here for example let's say i

want to add each quarter's profit to the

name manager so i could reference this

anywhere in my workbook

here i can select the header and also

the values then i can click on formulas

and here i can create from selection

and here the name is in the top row

then i can click on ok and now if i want

to let's say reference the first quarter

profit i can enter equals and then i'll

press f3 and here's the q1 profit i can

click on ok hit enter and then i can

reference this anywhere in my workbook

if i want to manage all of these

different names that i've created i can

click on formulas and there's something

called the name manager when i click on

that i can see all of the different

defined names i could double click in to

edit it or i could delete the different

items here

tip number eight you can rely on the

excel status bar for some quick metrics

here we have orders at the kevin cookie

company with the order totals and i want

to know how much total revenue did we

collect how many orders were there what

was the average order revenue all that

kind of stuff now i could go to the

bottom and i could enter in formulas but

that's going to take a little bit of

time instead here i'll select this cell

and press ctrl shift and up arrow

that way i could select just all of

these cells

now if i look down on the status bar i

can see that we had about sixty six

thousand dollars of revenue we had about

fifty orders and the average order size

was about thirteen hundred dollars

i can right click on this and if i

scroll down a little bit here i could

also add the min i could add the max the

numerical count and i can add all of

these metrics

now when i click back here you'll see

them all in the status bar and this is a

new one now i can click on the status

bar to copy this value now i can click

into a cell and paste and this is now

pasted in the sum so once again

a neat way to not even use formulas to

get some quick metrics

tip number nine is flash fill so what

can you do with flash fill well you can

extract information from a column or you

can combine various columns together

let's see how you can do this

here in this first column i want to pull

out the first name

so here let me type in the first name i

want to give excel an example then i'll

hit enter now i can select this cell go

up to the data tab and then you'll see

an icon with a flash or lightning icon

this is called flash fill

there's also a shortcut key control e so

here let me go back and let's try the

shortcut key i'll press ctrl e and there

it takes the model of what i've entered

and it applies it to all of these other

rows and there i just get the first name

out now what's cool is you can also

format it in different ways so let's say

i want the last name comma first name

then i can hit enter and it follows that

same format for all of the other entries

you'll see that it's not always perfect

here for example it has the fifth and it

includes that as a last name so you'll

have to watch it just to make sure that

it's working how you expect it to

i could also use flash fill to combine

different columns so here i want to pull

the names together so i'll type in kevin

stratford now you could use a formula to

do this but that's a lot of work instead

i'll select this cell press ctrl e and

check that out it couldn't get any

easier than this

this brings us to tip number ten you can

very quickly enter a formula or function

into multiple cells at once

here i have an investor meeting coming

up and i need to tell them how many

cookies we sold by market

and

i don't have that data so let's see if i

could use a function to do this here

i'll highlight all of these different

cells and we can use a function called

randbetween i just want a random number

and here for the bottom let's enter in

500 and for the top value i'll enter a

thousand then i'll close the parentheses

now if i press enter right now the

function will only show up in the cell

that i'm currently in but if i press

control enter that automatically applies

it to all of these selected cells and

look at that i'm now ready for this

investor meeting

next i want to show you a really

creative way you can use control enter

down below i have a list of customers

and all of their different orders so

here tray delicious they had a number of

orders abc groceries had a number of

orders but if we look over here we

didn't enter the customer name for every

single row and i want to fill that in

now of course i could select this

customer i could use the fill handle and

i could fill that in i could do the same

for abc groceries and i could go through

this whole list but now imagine we have

thousands of customers which of course

we have at the kevin cookie company this

is of course just a subset of our data

now we could use that same control enter

to fill in all these blanks

here i'll select all of these different

cells

let's click on the home tab

go over to find and let's go to special

here i can select all blanks then click

on ok and now all of the blank cells are

selected and i want this first blank

cell i just want it to equal the cell

above it

so here for the formula i can enter

equals and then i'll select this cell

now it'll apply this formula relatively

so here the next cell will look at the

one above it

now i can press ctrl enter and look at

that it filled in the name for every

single row and here once again you can

see it looks at the cell above here it

also looks at the cell above so that

works exactly how i want it to

now let's say i wanted to go back to the

original state well once again i can

highlight all of these cells

then i'll click on home here i can click

on find once again and this time i can

select all formulas

and here now we see that all of the

formulas are selected so these are all

the cells that look at the cell above it

and then i can press the delete key and

we're right back to where we started

this brings us to the very last tip of

today and it's the quick access toolbar

here i have a table and let's say i want

to sort it in maybe ascending order now

of course i could click into the table i

could right click i could go to sort and

here i can now sort it alternatively i

could also click into data and here too

i could also sort this table but either

technique requires me to either right

click or i have to click into a certain

tab and then i can find my command and

if i end up sorting all the time maybe i

want to pin the command so it's easier

to access in the future and i could very

easily do that here for example i could

go up to this command which i use all

the time i could right click on it and

then there's the option to add to quick

access toolbar i will select that and

here now you see a toolbar appear and i

could go through any action on the

ribbon and i can add it to my toolbar

let's say i want the name manager i can

add it to the quick access toolbar and

there it is

i can click on this drop-down list and

here i can add some of the most common

actions to my toolbar and if i go down i

have some other settings for example i

could show it above the ribbon if i

don't want it to use extra screen real

estate and i have a few other options as

well

right here i can also view more commands

and here

and here i have pretty much any command

in excel i can add it to my quick access

toolbar so this is a really easy way so

if i want to sort this data it doesn't

matter what tab i happen to be on right

up here i have my sort controls and i

can do this with any control in excel

alright well those are the excel tips

and tricks

let me know down below in the comments

did you learn any new ones

to watch more videos like this one

please consider subscribing and as

always

i'll see you in the next video

[Music]

you