20 Excel Shortcuts to Save You HOURS of Work

Kenji Explains

18 Dec 202213:00

EducationalLearning

32 Likes 10 Comments

TLDRThis video tutorial introduces 20 essential Excel shortcuts designed to save time and improve productivity. It begins with freezing panes for easier data navigation, followed by using control and arrow keys for quick data set traversal. The video covers various shortcuts for copying, pasting values, adding comments, and navigating large datasets. It also highlights the efficiency of using templates provided by HubSpot and demonstrates how to fill in missing data, sum totals, and format cells. Advanced techniques such as using the 'Find & Select' feature, filtering data with slicers, and identifying top values with functions like MAX and LARGE are also explained. The video concludes with tips on verifying formulas and tracing precedents, ensuring data accuracy.

Takeaways

😎 Freeze the top row of your spreadsheet for easy reference with the shortcut Alt + W + F + R.
⏭️ Navigate large datasets quickly with Control + Arrow keys and select specific areas using Control + Shift + Arrow keys.
🔄 Use Ctrl + R and Ctrl + D to copy cells to the right and down, respectively, and Alt + Down Arrow to fill cells with existing data.
📏 Select entire rows or columns with Shift + Space and Control + Space, and manage them with Ctrl + Shift + Plus and Ctrl + Minus.
🔄 Move rows easily by selecting the row with Shift + Space and dragging it to the desired location.
✅ Split text within a cell across multiple lines using Alt + Enter.
💬 Add comments to cells for notes or clarifications with Shift + F2.
🔍 Quickly jump to a specific cell or row with Ctrl + G.
📈 Utilize templates from sponsors like HubSpot to save time and visualize data effectively.
🔢 Fill down or across cells with Ctrl + E and sum up totals with Alt + Equals.
📋 Copy entire columns with Control + Space, and paste values instead of formulas using Control + Alt + V.
💡 Change number formats and apply styles using Control + 1 and F4 to replicate formatting.
🔍 Find and replace text efficiently with Control + H, and filter data with right-click and filter options.
📊 Convert data into a table for easier management and use slicers for dynamic filtering.
🏆 Use functions like MAX and LARGE to find top values in datasets.
🔍 Validate formulas by viewing them with Ctrl + ~ (tilde) and trace precedents with Alt + T + U + T.

Q & A

What is the first Excel shortcut discussed in the video?
-The first shortcut discussed is freezing the top row to keep headers visible while scrolling. This is done by pressing Alt + W + F + R.
How can you navigate quickly across a large dataset in Excel?
-You can use the Control key with arrow keys to navigate quickly. Control + down arrow takes you to the bottom, Control + up arrow to the top, and Control + right/left arrow to move horizontally.
What does the shortcut Ctrl + R do in Excel?
-Ctrl + R copies the content from the left cell to the selected cell.
How can you select an entire row or column in Excel using shortcuts?
-To select an entire row, use Shift + Space. To select an entire column, use Ctrl + Space.
How do you insert new rows or columns using shortcuts in Excel?
-After selecting a row or column with Shift + Space or Ctrl + Space, use Ctrl + Shift + Plus to insert a new row or column. To remove them, use Ctrl + Minus.
What is the shortcut to add a comment to a cell in Excel?
-You can add a comment to a cell by pressing Shift + F2.
How can you quickly go to a specific row in Excel?
-You can use the Ctrl + G shortcut to bring up the 'Go To' dialog box, where you can enter the row number you want to go to.
What shortcut allows you to quickly fill in missing data based on a pattern?
-Ctrl + E is the shortcut for Flash Fill, which fills in missing data based on a detected pattern.
How can you copy and paste only the values of a cell range in Excel?
-After copying the cell range, use Ctrl + Alt + V to bring up the 'Paste Special' dialog box, and then select 'Values' to paste only the values.
What shortcut helps you to replace text or values in Excel?
-You can use Ctrl + H to bring up the 'Find and Replace' dialog box to replace text or values.

Outlines

00:00

🔒 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 the video's sponsor, HubSpot, and provides a downloadable Excel file for practice. The first shortcut discussed is 'Alt + W + F + R' to freeze the top row for easy reference while scrolling. The paragraph continues with various navigation shortcuts like 'Control + Arrow Keys' and 'Ctrl + Shift + Arrow Keys' for selecting specific areas. It also covers shortcuts for filling data across rows or down columns with 'Ctrl + R' and 'Ctrl + D', inserting new rows with 'Alt + Down Arrow', and manipulating rows and columns with 'Shift + Space', 'Ctrl + Shift + Plus', and 'Ctrl + Minus'. The paragraph concludes with tips on moving rows and undoing actions with 'Ctrl + Z'.

05:04

📊 Utilizing Templates and Excel Features for Efficiency

The second paragraph focuses on enhancing Excel productivity with templates provided by HubSpot, which can be downloaded for free. These templates include various chart types that automatically update when data is modified. The speaker finds this useful for comparing different data visualizations. The paragraph then returns to the main topic of Excel shortcuts, discussing 'Ctrl + Page Down' for navigating tabs, 'Ctrl + E' for filling in missing data, 'Alt + =' for summing values, and 'Ctrl + Space' for copying entire columns. It also addresses the issue of pasting values instead of formulas with 'Ctrl + Alt + V' and 'Ctrl + 1' for changing number formats. The F4 key is highlighted for replicating formatting changes, and the paragraph ends with a discussion on using 'Ctrl + H' for finding and replacing text within a dataset.

10:05

🛠 Advanced Excel Techniques for Data Analysis

The final paragraph delves into more advanced Excel techniques. It starts by discussing the use of 'Ctrl + T' to convert a dataset into a table, which simplifies the process of inserting slicers for filtering data by categories like states. The paragraph then moves on to addressing common issues such as fixing typos with 'Ctrl + H' and filtering data to show only specific states. It also covers how to find the top three units sold using the 'MAX' function and the 'LARGE' function for ranking values. The number one shortcut highlighted is 'Ctrl + ~', which allows users to view the underlying formulas in a spreadsheet. Additionally, 'Alt + T + U + T' is introduced for tracing the precedents of formulas to understand their dependencies. The paragraph concludes with a call to action to watch another video for more Excel tips and to take an Excel course for further learning.

Mindmap

Keywords

💡Excel shortcuts

Excel shortcuts refer to quick key combinations that users can employ to perform tasks more efficiently within Microsoft Excel. In the video's context, these shortcuts are presented as time-saving techniques for managing and manipulating data in spreadsheets. For example, 'Alt WFR' is used to freeze the top row for easier reference while scrolling through data.

💡Control arrow keys

The term 'control arrow keys' describes a method of navigating through a dataset in Excel by using the control key in combination with the arrow keys. This allows for quick vertical or horizontal movement to the edges of the data set. In the script, it is mentioned as a way to swiftly reach the bottom or sides of a long dataset.

💡Ctrl Shift shortcuts

Ctrl Shift shortcuts are combinations of the control and shift keys with arrow keys used for selecting large blocks of data in Excel. These shortcuts enable users to select entire rows or columns or specific areas without clicking and dragging with the mouse. In the video, 'Ctrl Shift down arrow' and 'Ctrl Shift right arrow' are used to select columns B and C.

💡Freeze panes

Freeze panes is a feature in Excel that allows users to lock certain rows or columns in place so that they remain visible while scrolling through a spreadsheet. This is particularly useful for keeping headers or key data in view. The script demonstrates using 'Alt WFR' to freeze the top row and 'Unfreeze panes' to remove this setting.

💡Ctrl R and Ctrl D

Ctrl R and Ctrl D are Excel shortcuts for filling data horizontally and vertically. Ctrl R fills data to the right, copying values from left to right, while Ctrl D fills data downwards, copying values from top to bottom. The script uses these shortcuts to extend existing data patterns into new rows or columns.

💡Alt Down Arrow

The 'Alt Down Arrow' shortcut in Excel is used to auto-fill a cell with the pattern or value from the cell directly above it. This is showcased in the script as a quicker way to populate new rows with existing data without manual entry.

💡Shift Space

The 'Shift Space' command in Excel is used to select an entire row. When combined with other keys, such as 'Control Space', it allows for the selection of an entire column. These shortcuts are highlighted in the script as efficient methods for row and column selection.

💡Ctrl Shift Plus and Ctrl Minus

The 'Ctrl Shift Plus' and 'Ctrl Minus' shortcuts are used for adding or removing rows or columns in Excel. 'Ctrl Shift Plus' inserts a new row or column, while 'Ctrl Minus' deletes the selected row or column. The script illustrates these shortcuts as part of efficient data manipulation.

💡Alt Enter

The 'Alt Enter' shortcut in Excel is used to create a line break within a cell, allowing text to be split over multiple lines without altering the cell size. In the script, it is used to adjust the length of text within a cell to fit better on the screen.

💡Shift F2

Shift F2 is an Excel shortcut that opens the comment box for a selected cell. It is used to add notes or questions about the data, which can be particularly useful for collaborative work or reminders. The script mentions using this shortcut to leave comments for an intern's work.

💡Ctrl G

Ctrl G is a shortcut in Excel that opens the 'Go To' dialog box, allowing users to quickly navigate to a specific cell or range by entering its address. In the video script, it is used to jump directly to row 100 as an efficient way to reach distant parts of a spreadsheet.

💡Control E

Control E is a shortcut in Excel that fills down the value or pattern from the cell above the selected cell into the current cell. This is demonstrated in the script for quickly populating names or other data across multiple cells.

💡Alt Equals

The 'Alt Equals' shortcut in Excel is a quick way to sum up a range of numbers. It automatically inserts the SUM function and calculates the total for the selected range. The script uses this shortcut to find the total sales by summing up the values in a column.

💡Paste Special

Paste Special is a feature in Excel that allows users to control how data is pasted, including options to paste values, formulas, formats, etc. In the script, 'Control Alt V' is used to open the Paste Special dialog box, where the decision is made to paste values instead of formulas.

💡Control One

Control One is a shortcut in Excel that opens the Format Cells dialog box, which is used to change the number formatting, including currency symbols. The script demonstrates changing a cell's format from dollars to pounds using this shortcut.

💡F4 Key

The F4 key in Excel is used to repeat the last command or apply the same formatting to another cell or range. In the script, it is used to replicate the currency format change to other cells without having to reopen the Format Cells dialog box.

💡Control H

Control H opens the Find and Replace dialog box in Excel, which is used to search for specific text and replace it with different text. The script uses this feature to correct a typo in the state names, replacing 'Texxas' with 'Texas'.

💡Filter

Filtering in Excel allows users to sort and organize data by specific criteria, such as showing only rows that match a certain value. The script mentions using the filter feature to display only the rows for the state of Texas and also introduces the use of slicers for a more visual filtering method.

💡Ctrl T

Ctrl T is a shortcut in Excel used to convert a range of data into a table, which provides additional functionalities like automatic sorting and filtering. The script uses this shortcut to prepare the data for the insertion of a slicer.

💡MAX function

The MAX function in Excel returns the largest value from a set of numbers. In the script, it is used to find the top unit sold by entering the function and selecting the range of sales data.

💡LARGE function

The LARGE function in Excel is used to find the nth largest value in a range. The script demonstrates using the LARGE function to determine the second and third highest units sold, after the MAX function is used for the top seller.

💡Control tilde

The control tilde (~) shortcut in Excel toggles the display of formulas in a worksheet, allowing users to view the underlying formulas rather than their results. This is used in the script to verify that the formulas in the spreadsheet are correct and as expected.

💡Alt TUt

Alt TUt is a shortcut in Excel that traces the precedents of a formula, showing the sources of the data that contribute to the formula's result. This helps in understanding data dependencies within the spreadsheet, as illustrated in the script.

Highlights

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

Control + Arrow keys to navigate through long datasets in Excel.

Ctrl + Shift + Arrow keys to select specific areas in a dataset.

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

Alt + Down Arrow to autofill cells with existing data.

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

Ctrl + Shift + Plus and Ctrl + Minus to add or remove rows and columns.

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

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

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

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

Ctrl + E to quickly fill in missing data in a column.

Alt + Equals to sum up totals in a column.

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

Ctrl + 1 to open the format cell dialog box for changing number formats.

F4 key to replicate the previous formatting step.

Ctrl + H to use the replace feature for fixing typos in datasets.

Filtering data by right-clicking and selecting filter by selected cells' value.

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

Inserting slicers for filtering data by categories like states.

Max function to find the highest value in a dataset.

Large function to find the second and third highest values.

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

Alt + T + U + T to trace precedents and see where formulas are coming from.

Transcripts

Browse More Related Video

15 POWERFUL GOOGLE SHEETS TIPS & TRICKS (Insanely Useful Productivity Tips For Beginners)

Master Pivot Tables in 10 Minutes (Using Real Examples)

Excel for Intermediate Users - The Complete Course

Top 30 Excel 2016 Tips, Tricks, Shortcuts, Functions & Formulas

Excel Formulas and Functions | Full Course

10 BEST Google Sheets HACKS for Teachers