20 Excel Shortcuts to Save You HOURS of Work

Summary

This video script offers a comprehensive guide to 20 essential Excel shortcuts designed to save time and increase productivity. From freezing headers for easy reference while scrolling to using control keys for quick navigation and data manipulation, the tutorial covers a range of shortcuts. It also explains how to use the 'Alt' key for filling data, inserting comments, and accessing specific cells rapidly. The script highlights the efficiency of using templates provided by HubSpot and concludes with advanced techniques like using the 'Max' function for top values, 'Large' function for additional rankings, and tracing formulas for accuracy. The aim is to empower viewers to master Excel for better data visualization and analysis.

Takeaways

• 🔒 'Alt + W + F + R' is a shortcut to freeze the top row in Excel, making it visible while scrolling through data.
• ⏭️ Using 'Control + Arrow Keys' allows for quick navigation across lengthy datasets in Excel.
• 📋 'Ctrl + Shift + Arrow Keys' can be used to select specific areas in a spreadsheet, such as columns B and C.
• 🔄 'Ctrl + R' and 'Ctrl + D' are shortcuts to copy data horizontally and vertically, respectively.
• 📉 'Alt + Down Arrow' can be used to quickly fill in new rows with existing data from above.
• 🔑 'Shift + Space' and 'Control + Space' are shortcuts for selecting entire rows and columns, respectively.
• 🔄 'Ctrl + Shift + Plus' and 'Ctrl + Minus' are used to insert and delete rows or columns, enhancing efficiency.
• 🔄 'Shift + Click and Drag' allows for moving rows or columns to different positions within a spreadsheet.
• ✅ 'Alt + Enter' can be used to split text within a single cell across multiple lines.
• 💬 'Shift + F2' is a shortcut to add comments to specific cells, which can be useful for annotating data points.
• 🔍 'Ctrl + G' opens the 'Go To' dialog box, allowing for quick navigation to a specific cell or location in a spreadsheet.
• 📈 HubSpot offers free Excel templates that can be customized to visualize data effectively.
• 🔢 'Ctrl + E' is a powerful shortcut for filling in data based on existing patterns or sequences.
• 📊 'Alt + '=' signs is a shortcut to sum up values in a column, providing a quick total calculation.
• 📋 'Ctrl + Alt + V' followed by selecting 'Values' in the 'Paste Special' dialog box allows for pasting data without formulas.
• 🛠️ 'Ctrl + 1' opens the 'Format Cells' dialog box, enabling changes to number formats, such as switching to a pound sterling.
• 🔄 'F4' replicates the last formatting action, making it easy to apply consistent styles across the spreadsheet.
• 🔍 'Ctrl + H' opens the 'Find and Replace' dialog, which is useful for correcting typos or standardizing data entries.
• 📊 'Filter' and 'Slicers' are features that help in organizing and visualizing data based on specific criteria.
• 🏆 'LARGE' function is used to find the nth largest value in a range, useful for top sales rankings.
• 🔍 'Ctrl + ~' toggles the display of formulas in a spreadsheet, which can be helpful for verifying calculations.
• 🔄 'Alt + T + U + T' traces the precedents of a formula, showing the sources of its data, aiding in formula validation.

Q & A

What is the primary purpose of the video script?

The primary purpose of the video script is to teach viewers 20 essential Excel shortcuts that can save them time while working with spreadsheets.

Who sponsored the video?

HubSpot sponsored the video, providing free Excel templates for viewers.

How can viewers access the Excel file used in the video?

Viewers can download the Excel file for free from the video description below.

What is the shortcut to freeze the top row in Excel?

The shortcut to freeze the top row in Excel is Alt + W + F + R.

How can you navigate through a long dataset in Excel using keyboard shortcuts?

You can use the Control + Arrow keys to navigate through a long dataset in Excel. For example, Control + Down Arrow to reach the bottom and Control + Up Arrow to go back to the top.

What is the shortcut to copy the content from the left to the right in Excel?

The shortcut to copy the content from the left to the right in Excel is Ctrl + R.

How can you quickly add or remove rows or columns in Excel?

You can quickly add rows or columns by selecting the row or column, pressing Shift + Space, and then using Ctrl + Shift + Plus. To remove, select and use Ctrl + Minus.

What is the shortcut to move a specific row in Excel?

To move a specific row, select the row by pressing Shift + Space, then click and drag the row to the desired location.

How can you split text within a single cell into two lines?

You can split text within a single cell into two lines by pressing Alt + Enter within the cell.

What is the shortcut to add comments to a cell in Excel?

The shortcut to add comments to a cell in Excel is Shift + F2.

How can you quickly navigate to a specific row in Excel?

You can quickly navigate to a specific row in Excel by using the Ctrl + G shortcut, which opens the 'Go To' pop-up where you can enter the row number.

What is the purpose of the Ctrl + E shortcut in Excel?

The Ctrl + E shortcut in Excel is used for filling in data based on examples provided in adjacent cells, making it faster to complete data entry tasks.

How can you sum up a column of numbers in Excel?

You can sum up a column of numbers in Excel by using the Alt + Equals shortcut, which automatically inserts a SUM function for the selected range.

What is the correct way to paste values from a formula in Excel?

To paste values from a formula in Excel, copy the formula first, then use Ctrl + Alt + V to open the Paste Special dialog box, and select 'Values' to paste.

How can you change the currency format in Excel?

You can change the currency format in Excel by using the Ctrl + 1 shortcut to open the Format Cells dialog box, then selecting the desired currency under the 'Currency' section.

What is the shortcut to replicate the previous formatting step in Excel?

The shortcut to replicate the previous formatting step in Excel is the F4 key.

How can you find and replace text in Excel?

You can find and replace text in Excel by using the Ctrl + H shortcut, which opens the 'Find and Replace' dialog box.

What is the shortcut to filter data in Excel?

To filter data in Excel, you can right-click on the cell or column header, go to 'Filter', and select 'Filter by Selected Cells' Value'.

How can you use slicers to filter data in Excel?

To use slicers to filter data in Excel, first convert your data into a table using Ctrl + T, then insert a slicer by going to 'Table Design' and selecting 'Insert Slicer'.

What is the function used to find the top three units sold in Excel?

The function used to find the top three units sold in Excel is a combination of the MAX function for the highest value and the LARGE function for the second and third highest values.

How can you view and trace the formulas in Excel?

You can view the formulas in Excel by pressing Ctrl + ` (the accent key). To trace the precedents or dependencies of a formula, use Alt + T + U + T.

Outlines

00:00

🔒 Essential Excel Shortcuts for Data Management

This paragraph introduces 20 essential Excel shortcuts aimed at saving time when working with spreadsheets. It begins with a mention of a downloadable Excel file and a sponsor, HubSpot. The shortcuts include freezing the top row for visibility while scrolling (Alt + W, F, R), using Control and arrow keys for navigation, and shortcuts for copying and pasting data (Ctrl + R, D, and Alt + Down Arrow). It also covers selecting rows or columns (Shift + Space for rows, Ctrl + Space for columns), adding or removing rows/columns (Ctrl + Shift + Plus, Ctrl + Minus), and moving rows with the Shift key and drag feature. The paragraph concludes with a note on undoing actions (Ctrl + Z).

05:04

📊 Utilizing Excel Templates and Advanced Shortcuts

The second paragraph discusses the benefits of using Excel templates provided by HubSpot for free, which can be downloaded from a link in the video description. These templates are customizable and automatically update charts when data is modified. The paragraph then continues with a demonstration of advanced Excel shortcuts, such as filling in data quickly with Ctrl + E, summing totals with Alt + Equals, and pasting values instead of formulas using Ctrl + Alt + V. It also covers formatting cells with Ctrl + 1, using F4 to replicate formatting, and correcting typos with Ctrl + H. The paragraph concludes with filtering data using the right-click context menu and the Home tab's filter feature.

10:05

📈 Advanced Filtering Techniques and Formula Auditing

The third paragraph focuses on advanced filtering techniques in Excel, such as using slicers to filter data by state, and converting data into a table for easier management (Ctrl + T). It also addresses common issues like finding the top three units sold using the MAX and LARGE functions. The paragraph concludes with tips on auditing formulas for accuracy, including viewing formulas directly in the worksheet (Ctrl + `) and tracing precedents with Alt + T, U, T. The video ends with a call to action to learn more Excel tricks and to subscribe for more content.

Keywords

💡Excel shortcuts

Excel shortcuts refer to a set of keyboard combinations that allow users to perform common tasks more efficiently within Microsoft Excel. In the video's context, these shortcuts are presented as time-saving tools for managing and analyzing data in spreadsheets. For example, 'Alt wfr' is used to freeze the top row of a spreadsheet, making it visible while scrolling through data.

💡Freeze panes

Freeze panes is a feature in Excel that allows certain rows or columns to remain visible while scrolling through a large dataset. The video demonstrates how to use 'Alt wfr' to freeze the top row, which is crucial for maintaining visibility of headers when dealing with extensive data sets.

💡Control arrow keys

The term 'control arrow keys' refers to a combination of keyboard shortcuts in Excel that enable quick navigation through data. In the script, it is mentioned that pressing 'Control' and an arrow key allows the user to move to the beginning or end of a row or column, which is particularly useful for datasets that extend beyond the initial view.

💡Ctrl Shift shortcuts

Ctrl Shift shortcuts in Excel are used for selecting or extending a selection in a spreadsheet. The video script explains how 'Ctrl Shift down arrow' and 'Ctrl Shift right arrow' can be used to select a range of cells, which is essential for performing operations on specific areas of a dataset.

💡AutoFill

AutoFill is an Excel feature that automatically fills a series of cells with data based on the pattern it detects. The script mentions 'Ctrl R' and 'Ctrl D' as shortcuts to copy data to the right and downwards, respectively, which is a form of AutoFill that replicates the data from the left or above.

💡Alt Down Arrow

The 'Alt Down Arrow' shortcut in Excel is used to quickly fill a cell with data from the cell directly above it, based on the recognized pattern. The video script illustrates this by showing how to use the shortcut to paste data into a new row without having to retype it.

💡Shift Space

The 'Shift Space' keyboard combination in Excel is used to select an entire row. The script explains that after pressing 'Shift Space', additional rows or columns can be added or removed using 'Ctrl Shift Plus' and 'Ctrl Minus', which is a quick way to modify the selection area.

💡Add comments

Adding comments in Excel is a way to insert notes or annotations within cells. The video script describes using 'Shift F2' to add a comment to a cell, which is useful for leaving messages or queries for other users working on the same spreadsheet, such as questioning a number entered by an intern.

💡Go to

The 'Go to' feature in Excel, accessed via 'Ctrl G', allows users to jump to a specific cell or range within a spreadsheet. The script uses this shortcut to quickly navigate to row 100, demonstrating its utility in managing large datasets where manual scrolling would be impractical.

💡Templates

Templates in Excel are pre-formatted files that provide a starting point for various tasks, such as creating graphs or reports. The video script mentions HubSpot's free Excel graph templates, which can be customized to visualize data effectively, showcasing the convenience of templates for enhancing productivity.

💡Paste Special

Paste Special is an Excel function that allows users to control what is pasted into a cell, such as values, formulas, or formats. The script explains the use of 'Ctrl Alt V' to open the Paste Special dialog box, which is essential when you want to paste data without the original formulas, ensuring the data's integrity is maintained.

💡Format Cells

Format Cells is a feature in Excel that enables users to customize the appearance of cells, including changing number formats, fonts, and colors. The video script demonstrates changing currency symbols and adding a fill color as examples of how to use this feature to enhance data presentation.

💡F4 key

The F4 key in Excel is used to repeat the last command or action performed, such as formatting changes. The script illustrates using F4 to replicate a previous formatting step, which is a quick way to apply consistent formatting across multiple cells.

💡Replace feature

The Replace feature in Excel, accessed via 'Control H', is used to find and replace text within a spreadsheet. The script uses this feature to correct a typo in the state names, demonstrating its utility in quickly fixing errors in large datasets.

💡Filter

Filtering in Excel allows users to display only the data that meets certain criteria. The script explains how to use the filter feature to isolate data for the state of Texas, showcasing how filters can be applied to analyze specific subsets of data.

💡Slicers

Slicers in Excel are interactive tools that provide a visual way to filter data. The video script describes inserting a slicer for states, which enables users to select multiple states to filter the dataset, illustrating the ease of use and visual appeal of slicers for data analysis.

💡LARGE function

The LARGE function in Excel returns the nth largest value in a set of values. The script uses this function to find the top three units sold, demonstrating its use in ranking data and identifying high-performing items.

💡Formula auditing

Formula auditing in Excel involves checking the accuracy and logic of formulas within a spreadsheet. The script mentions using 'Control ~' and 'Alt TUt' to view and trace the formulas, which is crucial for verifying calculations, especially when the data's reliability is in question.

Highlights

Alt + W, F, R shortcut to freeze the top row in Excel for easier data navigation.

Control + arrow keys to quickly navigate through long datasets in Excel.

Ctrl + Shift + arrow keys for selecting specific areas in a spreadsheet.

Ctrl + R and Ctrl + D for filling cells with data from adjacent or above cells.

Alt + Down Arrow to quickly fill down data based on previous entries.

Shift + Space and Ctrl + Space to select entire rows or columns.

Ctrl + Shift + Plus and Ctrl + Minus for adding or removing rows and columns.

Shift + drag to move rows up or down without adding new ones.

Alt + Enter to split text across two lines within a single cell.

Shift + F2 to add comments to cells for review or clarification.

Ctrl + G to quickly navigate to a specific cell or row.

Ctrl + E to quickly fill in cells with the same value as the adjacent cell.

Alt + Equals (=) to sum up totals in a column with a single keystroke.

Ctrl + Alt + V and Paste Special to paste values instead of formulas.

Ctrl + 1 to open the format cell dialog box for quick formatting changes.

F4 key to replicate the previous formatting action.

Ctrl + H to find and replace text in a dataset.

Filtering data by right-clicking and selecting 'Filter by selected cell's value'.

Using Ctrl + T to convert a dataset into a table for easier management.

Inserting a slicer for filtering data by categories such as states.

LARGE function to find the nth largest value in a range.

Ctrl + ~ to display all formulas in a spreadsheet for verification.

Alt + T, U, T to trace the precedents of a formula to see its data sources.

Use of HubSpot's free Excel templates for time-saving visualizations.

Transcripts

if you find yourself in front of a computer  looking at spreadsheets often these 20 must know  

Excel shortcuts I will hopefully save you hours  of time let's go and thank you to HubSpot for  

sponsoring this video more on them later alright  so here's the Excel file that we'll be working  

with which you can download for free in the video  description below so let's get started with number  

20. as you can see over here in the table we have  all of this data down below which when you start  

to scroll down you can actually no longer see the  header so we're not really quite sure what this is  

referring to and same thing with all of these  other ones and so what we can do here instead  

is actually just do a shortcut which is going to  freeze that top row now to do this you're gonna  

press alt wfr and now when we start to scroll  down you'll notice that the top line is frozen  

which makes things a lot easier for us if you  ever want to remove that you can always go over  

here under free screens in the view option just  click on it and for from here just hit unfreeze  

panes and that's going to remove that for us next  up at number 19 we have the control arrow keys so  

as you can see over here to navigate across this  data set because it's very long you can actually  

press the shortcut control and then just use the  arrows so control down arrow to get all the way  

to the bottom control up arrow and right and left  to go to the sides similarly you can do Ctrl shift  

down arrow and Ctrl shift right that basically  allows you to select certain areas if you only  

want to select column B and C just stop pressing  the control and just go shift right arrow and you  

can see that allows you to do that let's go back  up to the top of the data set to look at number  

18 so control up Arrow all the way to the top  and over here you can see that we've got talk  

about sales and let's say we want to continue this  on now to do so you can just press Ctrl R that's  

basically going to copy what you had towards the  left and similarly you can go to control D that's  

going to drag everything that you had above down  one going back down towards the bottom so Ctrl  

down arrow let's suppose over here that we want  to add some new rows and so instead of actually  

going going ahead and typing everything in again  what you can do is hit the alt down arrow that's  

basically going to show you the drop down or down  arrow of all of the things that you already had  

above and so it's somewhat faster for you to just  paste it in there at number 16 we have selecting a  

row or a column now to do so all you need to do is  hover over a specific area and go to shift space  

and similarly for the column it's just going to be  controlled space like so now that on its own just  

isn't very useful so that's when the next trick  comes in and combined it can be quite handy so  

again we'll do the shift space and now if you want  to add the row you just need to go to Ctrl shift  

Plus again Ctrl shift plus and to remove some  you have to go to control minus and Ctrl minus  

similarly for columns you can go control space and  then Ctrl shift Plus and control minus now what if  

we just want to move a specific row up a bit now  to do so we'll go to shift space again and instead  

of having to add new rows Etc we can just press  the shift key and when you hover over the area  

you'll see that the cursor changes to these arrows  and from there you just want to click and drag it  

let's say we drag it up over here and you can see  how that's moved it all up similarly we can take  

it back down so shift and then just drag it all  the way down over here say to undo that we're just  

going to press Ctrl Z and Ctrl Z again now let's  go all the way back up so control up arrow and you  

can see that beverage brand here let's say that  we think it's a bit too long and so we want to  

space it out a bit maybe put it into a different  line now to do so we're just gonna get inside of  

it over here now what we're gonna press is the Alt  Enter and hit enter again and now you can see how  

it's spaced it out across two lines but it stayed  within that same cell coming in at number 12 we've  

got adding comments so let's suppose that the  interns worked on this file but we don't really  

trust him so we just want to make sure that this  number does make sense now one way to let him know  

is to go hover over the cell you're not sure about  and just hit the shift F2 now you can see that  

this pop-up is going to show up with your name  and then over here let's say can you check this

awesome now you can see that it's left this red  sign which when you hover over it's gonna show  

you the actual comment as we've seen here this  data set is not so small so suppose we want to  

reach a row 100 we can hover over there which  might be a bit tedious but the shortcut there  

is just clicking the Ctrl G that's going to show  you the go to pop-up So within this pop-up the  

reference that we want is we want to go to row 100  right so it's we're just going to put a 100 that's  

going to take us to that specific cell so we'll  hit OK there and you'll notice that it's taken us  

exactly there right here and speaking of shortcuts  a great way to save time is with templates like  

the ones HubSpot the sponsor of this video is  kindly providing Us for free using the link in  

the description below you can get multiple free  Excel graph templates within the download you'll  

find an Excel file with instructions on using  the template alongside all of the chart types  

you might need to visualize your data on top of  that you can easily modify the data and the charts  

will automatically change these templates have  either one column of data or multiple depending  

on your needs I personally find this useful when  deciding which chart showcases my data best as I  

can see multiple graphs at the same time and  see which one looks better so if you want to  

check these out go to the link in the description  below where you can download these free templates  

from HubSpot to level up your Excel game alright  back to the video great that's the first part done  

and now let's get into the top 10 so we'll just  go to the next tab by going to control page down  

from here you can see that we've got the  salesperson alongside some missing data  

here for the first name and the last name now to  fill this in we can go one by one so hit Max there  

and then Charles and so on or the shortcut here is  just to go to control e and then you can see that  

it's going to fill all of these in in a heartbeat  same thing goes with the last name see verstappen  

and again Ctrl e if we look over to the side  where it says total sales here let's say that  

we want to find out the total of that now to do  so let's just go up towards the bottom here and  

all we need to do is set the alt equals that's  going to sum up all of the totals and just hit  

enter just like that we have the full number next  up at number eight suppose we want to copy the  

whole sale amount and paste it somewhere else so  we'll go to control up here and all we're going  

to do is control space that's going to copy that  whole column for us then we'll go Ctrl C let's say  

we want to paste it over here under column O say  Ctrl V but the problem is that it all goes to zero  

and that's because it was actually a formula so  beforehand it was actually calculating the total  

sales which was the price times the unit sold  but now because it's not referencing that anymore  

it no longer works so instead what we're gonna  do is copy it again so go to control space and  

then Ctrl C and this time instead of just Ctrl  V to paste we're gonna press the control alt V  

and you can see that this is going to show this  paste special dialog box and within it you can  

just paste the formulas the formats Etc in our  case we want to paste the values that's what's  

going to give us the actual numbers so we just  click on that and hit OK and now you can see  

that it's based at the values and if we look  inside of them they no longer have the formula  

either at number seven we've got formatting  shortcuts so suppose over here that we want  

to change this from a dollar sign to a pound so  a British pound to do so we'll go to control one  

and this is going to open the format cell dialog  box and within it we want to go under currencies  

and we want to change this to a pound so  this one is the one that works for us and  

let's say we also want to add one decimal place  and basically that would do it for us but as you  

can see within format cells you can also change  a lot of other things like the font The Fill  

Etc and so we go for an orange fill color as  well to identify the pound signs hit OK there  

awesome now you can see that it's all reformatted  and now if you want to replicate this this  

um this step what you can do is actually press  the F4 key which is another shortcut which is  

basically going to replicate the previous step  so it's going to keep that same um the number  

that was there originally but it's just going to  change the formatting all right now moving into  

the top five and over here let's just go back to  this side of the data set and within the state of  

all of these here you'll notice that Texas has a  typo where it's got two x's now to get rid of that  

instead of going one by one which could be quite  tedious especially if you have a long data set you  

just gotta go to control h which is the replace  feature defined and replace so we wanna find the  

textures with two x's so Texas there with the two  x's and we want to replace that with a regular  

Texas as it should be spelled correctly then  from here we're just going to go to replace all  

and it's gonna say okay we made six Replacements  hit okay there and we're gonna close out of that  

now that we've fixed that let's say that we just  want to filter by Texas so instead of going ahead  

and having to apply a full filter all we need to  do is just right click on Texas go under filter  

there and we want to filter by the selected cells  value so just click on that and from here you can  

see that we have all of the Texas State's ones  that's been filtered like so now if you want to  

go to the filter the usual way you would just  have to go to the Home tab from here under sort  

and filter and go to filter there let's just  remove the filter for now another great way to  

filter is using slicers now to do so first we're  going to convert this into a table so go to Ctrl t  

and then it's going to select the data set  make sure it's all correct there and hit OK  

now from here what we're gonna do is under table  design we're gonna go to insert slicer so just  

click on that then we want a slicer specifically  for the states this is going to allow us to select  

different states so hit OK there and so if we  want to select Texas again all we need to do  

is click on Texas and that's going to filter  it for us same thing goes with any other state  

similarly if you want to select more than  one you've got to go to the stick sign here  

and from there let's say I want to  select Florida and California as well  

in number two we have a common issue that  people encounter which let's say first let's  

delete the state here and let's suppose that  we want to find out the top three units sold  

so we'll just type that here top three units  sold and so for the top one it's simply going  

to be the max function which is going to take the  highest one right so we're just going to go Max  

hit the top key and we're just going to select  all of these here so go to control shift and  

down arrow to the very bottom and hit enter  there but for the second and third we can  

no longer use the max function so instead  there's what's known as the large function  

hit the top key there and the array  is that same area so Ctrl shift down  

comma and the key here is the actual ranking so  we want the second highest so it's going to be the  

two close those brackets and hit enter and for the  third we're just gonna press the F4 key which is  

gonna copy that same action and then within it we  just got to change the number from A3 from a 2 to  

a three sorry and just like that we have the top  three and finally as the number one let's suppose  

that it was our intern that sent us this file and  we don't really trust him so we just want to make  

sure that the formulas all make sense now to do so  what we can do is actually hit the control in this  

accent key of sorts it's basically going to allow  us to see all of the formulas and where they're  

going now if you want to do a bit more than just  that and see where exactly they're going to what  

we can do is hit the alt t u t which is going  to allow us to trace the Precedence so basically  

allows us to see where the formula is actually  coming from same thing if you hover over any other  

formula and go to alt Tut that's going to allow us  to do that same thing if you ever want to remove  

these arrows you just need to go to the formulas  tab up over here and then go to remove arrows for  

more on Excel check out this video over here to  learn some awesome Excel tricks for this link over  

here to take our Excel course hit the like and  that subscribe and I'll catch you in the next one