20 Excel Shortcuts to Save You HOURS of Work

Summary

In this video, Kenji Explains shares 20 essential Excel shortcuts aimed at streamlining your workflow and saving hours of time. He guides viewers through various shortcuts including freezing panes, navigating data sets swiftly, adding and removing rows, and using slicers for filtering. Throughout the video, Kenji emphasizes the practical application of these shortcuts in everyday work and highlights free Excel templates from HubSpot as additional resources. Each tip is accompanied by a demonstration, making it easier for viewers to follow and incorporate into their Excel practices.

Highlights

• Learn how to freeze panes for better data visibility 🧊
• Navigate large data sets using control arrow keys with speed ⚡
• Easily add or remove rows and columns with simple shortcuts ➕
• Utilize slicers for effective data filtering and analysis 🔍
• Enhance data visualization with free templates from HubSpot 📈

Key Takeaways

• Master essential Excel shortcuts to work more efficiently 🚀
• Quickly navigate and manage large data sets with ease 📊
• Simplify data entry and formatting with smart shortcuts 🔢
• Enhance your Excel experience with free templates from HubSpot 📋
• Avoid common Excel frustrations by using these practical tips 😌

Overview

Excel is an essential tool for managing data, but it can also be quite tedious if you're not aware of the shortcuts available. Kenji Explains dives deep into 20 must-know Excel shortcuts that are designed to save you hours of work. These tips range from simple navigation techniques to more advanced formatting and filtering methods, ensuring that you can tackle any spreadsheet with confidence.

One of the standout features of this tutorial is the introduction of various shortcuts like 'freeze panes', which allows users to keep headers visible while scrolling. This simple trick makes it much easier to understand your data at a glance. Additionally, the video details how to perform bulk actions like adding or removing multiple rows and columns, and using slicers for filtering data in tables.

Kenji also highlights the value of aesthetics in data presentation by introducing free templates from HubSpot. These templates are a fantastic resource for anyone looking to improve their data visualization and presentation. By combining these shortcuts with pre-designed templates, users can significantly streamline their Excel workflow, making data management not only easier but also more visually appealing.

Chapters

• 00:00 - 00:30: Introduction to Excel Shortcuts The chapter introduces the importance of Excel shortcuts for frequent users dealing with spreadsheets. It highlights that learning these shortcuts can save valuable time. The author acknowledges sponsorship from HubSpot and gives an overview of a sample Excel file provided for practice. The chapter begins by mentioning a problem of disappearing headers when scrolling through data.
• 00:30 - 01:00: Freeze Top Row Shortcut This chapter introduces the shortcut for freezing the top row in a spreadsheet. It explains that instead of manually setting this option, users can press 'Alt + WFR' to quickly freeze the top row, which remains visible while scrolling down through the data. If users want to unfreeze the row, they can navigate to the 'View' option, select 'Freeze', and then hit 'Unfreeze'. This feature is presented as a way to streamline navigation and improve ease of use when handling spreadsheets.
• 01:00 - 01:30: Control Arrow Keys for Navigation The chapter titled 'Control Arrow Keys for Navigation' covers a method for efficiently navigating through data sets using keyboard shortcuts. It explains the functionality of using 'Control' in conjunction with arrow keys to move quickly through data. By pressing 'Control' along with the down arrow, users can reach the bottom of a data set, while the up arrow takes them to the top. The left and right arrows are used to move horizontally across the data. Additionally, the combination of 'Control', 'Shift', and arrow keys is introduced for selecting data ranges. This technique, such as using 'Control + Shift + Down' or 'Control + Shift + Right', facilitates quick selection of data within columns, making data manipulation faster and more efficient.
• 01:30 - 02:00: Copying with Ctrl R and Ctrl D The chapter titled 'Copying with Ctrl R and Ctrl D' explains how to efficiently copy data in a dataset using keyboard shortcuts. Specifically, it guides readers on using 'Ctrl R' to copy content from the left and 'Ctrl D' to drag and copy content from above. The chapter emphasizes optimizing navigation through large datasets using these shortcuts, starting from the top of the dataset by using 'Ctrl up Arrow' to quickly review and manage data.
• 02:00 - 02:30: Using the Alt Down Arrow This chapter discusses the use of the Alt + Down Arrow key combination to enhance efficiency when working with data lists. It highlights how this shortcut allows users to access and select from a dropdown list of previously entered data, making the process of adding new rows quicker. Additionally, the chapter explains other useful shortcuts for selecting rows and columns, specifically using Shift + Space for rows and Control + Space for columns.
• 02:30 - 03:00: Selecting Rows and Columns The chapter covers techniques for selecting rows and columns in a spreadsheet. It introduces useful keyboard shortcuts that enable users to quickly add or remove rows and columns. To select rows, the Shift + Space key combination is used. To add a row, Ctrl + Shift + Plus is applied, while to remove a row, Ctrl + Minus is utilized. Similarly, for columns, the combination of Control + Space is used for selection. Adding a column is done using Ctrl + Shift + Plus, and removal is done using Control + Minus.
• 03:00 - 03:30: Moving and Undoing Actions This chapter explains how to move a specific row up or down within a table or sheet by using the shift key and dragging the row to the desired position. It also briefly mentions the undo action.
• 03:30 - 04:00: Adding Comments with Shift F2 The chapter titled 'Adding Comments with Shift F2' discusses how to add comments to a document. Initially, it demonstrates undoing actions using Ctrl Z and navigating using Ctrl + Up Arrow. The content is adjusted by splitting text into multiple lines using Alt + Enter to improve readability while keeping it within the same cell. The section transitions into discussing adding comments, which can be useful for providing context or feedback, especially when multiple individuals like interns are involved in working on a file.
• 04:00 - 04:30: Jump to Specific Row with Ctrl G This chapter explains how to jump to a specific row in an Excel sheet using 'Ctrl + G'. It further delves into how to add a comment to a cell by hovering over the cell in question and pressing 'Shift + F2'. This action adds a comment with your name that appears as a red indicator, which displays the comment when hovered over. The chapter uses a scenario where you communicate a query or suggestion within a large dataset using these Excel functionalities.
• 04:30 - 05:00: HubSpot Free Templates The chapter discusses the utility of using HubSpot's free templates to save time and enhance productivity. It begins with a practical example involving spreadsheet navigation, where the speaker explains a shortcut for quickly jumping to the 100th row by using Ctrl+G, demonstrating the importance of efficiency in workflows. This method of using shortcuts is paralleled with the use of templates, highlighting their usefulness in simplifying and speeding up processes.
• 05:00 - 05:30: Auto-filling with Ctrl E In this chapter, the sponsor HubSpot offers free Excel graph templates to visualize data efficiently. The templates come with instructions and support various chart types. Users can modify data, and the charts will automatically update. These templates handle either single or multiple columns of data.
• 05:30 - 06:00: Summing with Alt Equals The chapter discusses using the 'Alt Equals' feature in Excel for sum calculations. This functionality allows users to sum data quickly and efficiently, making it useful for comparing and visualizing data across different chart types. The narrator mentions using templates from HubSpot to enhance Excel skills, with a link provided in the video description for downloading these templates. The chapter ends by transitioning to a list of top 10 Excel tips, accessible by navigating to the next tab using a keyboard shortcut.
• 06:00 - 06:30: Copy and Paste Special Values This chapter explains how to efficiently fill missing data in a spreadsheet using the 'Copy and Paste Special Values' technique. It specifically demonstrates filling in missing first and last names for salespersons. Instead of manually entering each name, a shortcut using 'Ctrl + E' is presented to automatically fill in these details quickly. This method is also applied to fill in last names, showing a practical example of streamlining data entry processes. Additionally, a brief mention is made of another data point, 'total sales', as a part of the data set under examination.
• 06:30 - 07:00: Formatting Shortcuts with Ctrl 1 This chapter covers various formatting shortcuts using Ctrl 1 in Excel. The transcript describes how to find the sum of a column by navigating to the bottom and using Alt + Equals for an automatic total sum calculation. It continues by explaining how to copy a whole column using Ctrl + Space followed by Ctrl + C to copy the selected column.
• 07:00 - 07:30: Using F4 to Replicate Actions In this chapter, the issue of pasting formulas is addressed. When attempting to paste a formula from one column to another and finding that the values all default to zero, it is because the formula's references are missing. Originally, the formula calculated total sales by multiplying price by units sold, but without the source data reference, it fails to function. The solution provided is to use 'Control Alt V' instead of a regular 'Control V' paste to properly replicate the formula across the desired cells.
• 07:30 - 08:00: Find and Replace with Ctrl H In this chapter titled 'Find and Replace with Ctrl H', the focus is on demonstrating how to use a feature to apply specific changes in a spreadsheet effectively. The excerpt begins with explaining how to use the 'paste special' dialog box for copying values instead of formulas. The user is instructed to select the 'values' option to avoid copying the underlying formula, which results in pasting just the numerical values. Following that, the chapter briefly touches on formatting shortcuts, exemplifying by converting currency symbols from a dollar sign to a British pound, using Ctrl+1 for formatting options.
• 08:00 - 08:30: Filtering by Right-click This chapter focuses on the process of using the right-click option to open the format cell dialog box in a spreadsheet program. It guides on how to set the currency to pounds and adjust decimal places. The chapter also covers additional formatting options available in the dialog, such as changing fonts and fill colors, with a demonstration of selecting an orange fill color to highlight cells with pound currency.
• 08:30 - 09:00: Using Slicers for Filtering This chapter focuses on using slicers to filter data more effectively. It begins with a tutorial on how to reformat data and introduces the F4 key as a shortcut to replicate formatting steps. Additionally, there is a brief mention of correcting a typo in the data, specifically in the state names where 'Texas' is misspelled with two x's. The chapter emphasizes the importance of data accuracy and efficient use of shortcuts for data manipulation.
• 09:00 - 09:30: Finding Top Units Sold In this chapter, the focus is on efficiently correcting data errors using the replace feature. Instead of manually rectifying each entry, the chapter demonstrates how to utilize the 'Control + H' command to find and replace errors in bulk. Specifically, it illustrates the correction of a misspelling ('textures' with two x's) to the correct term 'Texas', significantly streamlining the data cleaning process. The chapter concludes with the confirmation of six corrections made at once, showcasing the time-saving aspect of this method.
• 09:30 - 10:00: Viewing and Tracing Formulas The chapter titled 'Viewing and Tracing Formulas' explains how to filter data in a spreadsheet program by right-clicking on a cell value and selecting a filter option to display only the rows with the selected value, in this case for Texas. It also mentions the traditional method of applying a filter by accessing the Home tab and navigating to the sort options.

20 Excel Shortcuts to Save You HOURS of Work Transcription

• 00:00 - 00:30 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
• 00:30 - 01:00 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
• 01:00 - 01:30 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
• 01:30 - 02:00 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
• 02:00 - 02:30 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
• 02:30 - 03:00 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
• 03:00 - 03:30 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
• 03:30 - 04:00 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
• 04:00 - 04:30 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
• 04:30 - 05:00 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
• 05:00 - 05:30 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
• 05:30 - 06:00 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
• 06:00 - 06:30 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
• 06:30 - 07:00 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
• 07:00 - 07:30 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
• 07:30 - 08:00 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
• 08:00 - 08:30 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
• 08:30 - 09:00 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
• 09:00 - 09:30 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
• 09:30 - 10:00 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
• 10:00 - 10:30 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
• 10:30 - 11:00 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
• 11:00 - 11:30 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
• 11:30 - 12:00 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
• 12:00 - 12:30 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
• 12:30 - 13:00 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