Excel Tips and Tricks
Summary
TLDRThe 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
😊 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.
😃 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.
😲 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.
🤓 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.
Mindmap
Keywords
💡Dragging and dropping
💡Shortcut keys
💡Extract information
💡Formulas
💡Functions
💡Format data
💡Custom lists
💡Status bar
💡Flash fill
💡Quick access toolbar
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
Parcourir plus de vidéos associées
5.0 / 5 (0 votes)