Quick Excel Tips and Shortcuts with Video Tutorials

Learn Excel Tricks, Tips and Shortcuts with Exercise Files

Quick Excel Tips and Tricks with Video Tutorials

Pages: 58.

Formats: Pdf & Epub.

Guarantee: 45 days.

Download Ebook: Download here.

Order Paperback: Contact us.

This Excel guide teaches you excel tricks, tips and shortcuts. The guide and its short video tutorials are a life safer! Now you can learn how to use Excel more efficiently with many useful tips and tricks in this book and its quick videos. I’ll show you how to get your Excel work done faster on either Windows or Mac platform.

There are numerous useful shortcuts, tips, tricks and exercise files inside for you to practice along. All these will help you increase your productivity so that you can produce reports in minutes instead of hours. In Chapter 14, I provide a hassle-free download link for the 56 tutorial videos and exercise files.

It is widely agreed that close to 60 percent of Excel users leave 80 percent of Excel untouched. That is, most users do not tap into the full potential of Excel’s built-in utilities. Excel remains one of the most underutilized tools in the entire Microsoft Office Suite.

The 14 chapters of this book and its videos serve as an exhaustive collection of quick tutorials on Excel shortcuts, tips and tricks. It’s very good for beginners and advanced learners alike because it’s accompanied with 56 quick HD demo videos. In these videos, I use a practical approach to simplify everything you need to understand to help you speed up your learning of Microsoft Excel.

Microsoft Excel is a tool is used in virtually all careers and is valuable in both personal and professional settings. For example, you can use it to keep track of medications in a hospital inventory, create a financial plan for retirement, or to do other similar activities accurately and efficiently.

This book and its demo videos are very valuable because they introduce the fundamental and advanced skills necessary to get you started quickly in using Excel. You will discover that the first few chapters or videos alone will make you very productive in a short period of time.

Finally, if you have questions or need further help, you can use the support link I provide in Chapter 14. I will get back to you very quickly.

Table of Contents

How This Book Can Help You

How to Use the Demo Videos & Exercise Files

1. Seven Significant Shortcuts

  • 1.1. How to Enter Data or Formulas in Non-adjacent Cells Simultaneously
  • 1.2. How to Copy Data or Formulas Down a Column
  • 1.3. How to Instantly Enter Today’s Date or Time
  • 1.4. How to Convert Formulas to Values with a Simple Drag
  • 1.5. How to Display All Worksheet Formulas
  • 1.6. How to Create Charts with these Keystroke Shortcuts
  • 1.7. How to Zoom In and Out Quickly

2. Ribbon & Quick Access Toolbar Tips

  • 2.1. How to Access Ribbon Commands with Alt Key Sequences
  • 2.2. How to Expand/Collapse the Ribbon and Full-screen Views

3. Display Shortcuts

  • 3.1. How to Create Split Screens and Frozen Titles in a Flash
  • 3.2. How to Restore Missing Labels and Hide Repeating Labels
  • 3.3. How to Customize the Display of Status Bar Totals

4. Navigation & Selection Shortcuts

  • 4.1. How to Navigate Between Workbooks and Worksheets Efficiently
  • 4.2. How to Navigate Within Worksheets Efficiently
  • 4.3. How to Open, Close, Save and Create New Workbooks with Keystrokes
  • 4.4. How to Select an Entire Row, Column, Region or Worksheet
  • 4.5. How to Select Non-contiguous Ranges and Visible Cells Only

5. Data Entry and Editing Shortcuts

  • 5.1. How to Accelerate Data Entry
  • 5.2. How to Enter Dates and Date Series Efficiently
  • 5.3. How to Enter Times and Time Series Efficiently
  • 5.4. How to Use Custom Lists for Data Entry and List-based Sorting
  • 5.5. How to Enhance Editing Tools

6. Drag & Drop Techniques

  • 6.1. How to Accelerate Copy and Move Tasks Within Cells and Worksheets
  • 6.2. How to Drag and Insert Cells with the Shift Key
  • 6.3. How to Display the Paste Special Options

7. Formula Shortcuts

  • 7.1. How to Create Formulas Rapidly
  • 7.2. How to Select All Dependent or Precedent Cells
  • 7.3. How to Use AutoSum Shortcuts
  • 7.4. How to Count the Number of Unique Entries
  • 7.5. How to Use Conditional Formatting to Highlight Formula Cells
  • 7.6. How to Perform Calculations Without Formulas

8. Operational Shortcuts

  • 8.1. How to Insert, Delete, Hide and Unhide Columns and Rows
  • 8.2. How to Realign Imported Text
  • 8.3. How to Select and Manipulate Blank Cells
  • 8.4. How to Collapse and Expand Detail with Group and Ungroup Buttons
  • 8.5. How to Create Double-spaced or Triple-spaced Printouts

9. Formatting Shortcuts

  • 9.1. How to Use Keystroke Shortcuts for Frequently Needed Numeric Formats
  • 9.2. How to Accentuate Data with Alignment Tools
  • 9.3. How to Add a Color Background to Every 5th Row in a Range
  • 9.4. How to Use Conditional Formatting Based on Comparison Criteria
  • 9.5. How to Use Special Formats for Times Over 24 Hours
  • 9.6. How to Add and Remove Strikethrough and Borders with Keystroke Shortcuts
  • 9.7. How to Display Values in Thousands or Millions without Formulas
  • 9.8. How to Format Phone and Social Security Numbers

10. Data Management Techniques

  • 10.1. How to Clean up Spaces with the TRIM Function
  • 10.2. How to Split Column Data Using Text to Columns and Flash Fill
  • 10.3. How to Join Data with the TEXTJOIN Function and Flash Fill
  • 10.4. How to Ensure Unique Entries with Data Validation Rules
  • 10.5. How to Display Unique Items from Large Lists

11. Charting and Visual Object Tips

  • 11.1. How to Manipulate Chart Placement and Sizing with Dragging Techniques
  • 11.2. How to Create Chart Titles from Cell Content
  • 11.3. How to Create and Manipulate Shapes with the Shift, Ctrl and Alt Keys
  • 11.4. How to Create Linked Dynamic and Linked Static Images

12. Pivot Tips

  • 12.1. How to Adjust Report Layouts to Allow Totals on Top or Bottom
  • 12.2. How to Revisit Relevant Source Data with the Drill Down Feature

13. Ten Tiny Tips (Cheat Sheet)

  • 13.1. How to Become a More Proficient Excel User with Ten Tiny Tips

14. How to Download the Demo Videos and Exercise Files

  • 14.1 How to Get Further Help & Answers to Your Questions
  • 14.2 More Helpful Resources

Download free sample now!

Book Preview

How This Book Can Help You

This Excel guide and its short video tutorials are a life safer! Now you can learn how to use Excel more efficiently with many useful Microsoft Excel tips and tricks in this book and its quick videos. I’ll show you how to get your Excel work done faster on either Windows or Mac platform.

Most Excel users are under utilizing Excel because they’re not aware of the many keystrokes, shortcuts and techniques that they could be using. Whether you need to create formulas quickly, split data into columns, apply numeric formats, create charts in a flash, or accelerate data entry chores, you can save time by learning some of the many shortcuts, tips and tricks in this book.

There are numerous useful shortcuts, tips, tricks and exercise files inside. All these will help you increase your productivity so that you can produce reports in minutes instead of hours. In Chapter 14, I provide a hassle-free download link for the demo videos and exercise files.

It is widely agreed that close to 60 percent of Excel users leave 80 percent of Excel untouched. That is, most users do not tap into the full potential of Excel’s built-in utilities. Excel remains one of the most underutilized tools in the entire Microsoft Office Suite.

The 14 chapters of this book and its demo videos serve as an exhaustive collection of quick tutorials on Excel shortcuts, tips and tricks. It’s very good for beginners and advanced learners alike because it’s accompanied with 56 quick HD demo videos. In these videos, I use a practical approach to simplify everything you need to understand to help you speed up your learning of Microsoft Excel.

Microsoft Excel is a tool is used in virtually all careers and is valuable in both personal and professional settings. For example, you can use it to keep track of medications in a hospital inventory, create a financial plan for retirement, or to do other similar activities accurately and efficiently.

This book and its demo videos are very valuable because they introduce the fundamental and advanced skills necessary to get you started quickly in using Excel. You will discover that the first few chapters or videos alone will make you very productive in a short period of time.

Finally, if you have questions or need further help, you can use the support link I provide in Chapter 14. I will get back to you very quickly.

How to Use the Demo Videos & Exercise Files

You will find the link to download all the 56 demo videos and exercise files in Chapter 14 of this book. The serial/section number of the video you should watch is mentioned in the appropriate section of each chapter. The exercise file you should use for practice is also mentioned at the beginning of each section and video.

For effective learning and to get good practice, I strongly advise you attempt all the exercises in this book on your own. Practice along as you study each section or watch each demo video on.

1. Seven Significant Shortcuts

1.1. How to Enter Data or Formulas in Non-adjacent Cells Simultaneously

– Video 1.1 & Exercise File Ch01.xlsx (NewData worksheet)

Fig. 1.1.1 shows a worksheet called NewData. I’m going to enter some city names in column A.

Excel tips and shortcuts
Fig. 1.1.1: NewData worksheet 

But I’m going to repeat the names. For example, in cells A2 and A3, I’m going to put in the city name Boston. I also need to put in Boston in cells A7 and A11. So, rather than typing this once and then copying it multiple times, I’m going to select non-adjacent cells. This is a real time saver at different times when you use Excel. Follow these 5 steps:

  1. With the Ctrl key held down, highlight cells A2 and A3
  2. Click cell A7
  3. Either let go of Ctrl key or keep it held down and click cell A11
  4. Let go of Ctrl key and type Boston
  5. Press Ctrl + Enter

The same information (Boston) goes into all the four cells at the same time as shown in Fig. 1.1.2.

Excel tips and shortcuts
Fig. 1.1.2: NewData worksheet updated with city name Boston in Cells A2, A3, A7 and A11

The same thing happens with numbers. Repeat the above 5 steps to enter number 10 in cells C3, C4, D6, E9 and E10. See Fig. 1.1.3.

Excel tips and shortcuts
Fig. 1.1.3: NewData worksheet updated with number 10 in Cells C3, C4, D6, E9 and E10

This approach also works with formulas when you’ve got parallel-type situations. For example, let’s say we want the totals on the cells in columns H through L (see Fig. 1.1.1), and also want the totals on the cells in columns O through S.

First, scroll the worksheet to the left using the scroll bar at the bottom of the page. Then follow these steps as illustrated in Fig. 1.1.4:

  1. Hold down the Ctrl key and highlight cells H8, I8, J8, K8 and L8 (1)
  2. Repeat step 1 for cells O8, P8, Q8, R8 and S8 (2)
  3. Click the AutoSum button (3) on the top right side of the Home tab, or use the keystroke shortcut Alt + =
Excel tips and shortcuts
Fig. 1.1.4: NewData worksheet showing cells H8, I8, J8, K8, L8 and O8, P8, Q8, R8, S8 highlighted

The result is shown in Fig. 1.1.5.

Excel tips and shortcuts
Fig. 1.1.5: NewData worksheet showing totals updated in cells H8, I8, J8, K8, L8 and O8, P8, Q8, R8, S8

Supposing that instead, I want to know what the median values are on the cells in columns H through L, and on the cells in columns O through S.

First, I will get rid of these totals in Fig. 1.1.5 by simply pressing Ctrl + Z to undo my last action. Feel free to press Ctrl + Z as many times as you want to undo your previous steps. Now, if we want to highlight these two groups of columns again, it makes no difference which group we highlight first.

Supposing I want to know what the median value is in the active cell O8. See Fig. 1.1.6.

Fig. 1.1.6: NewData worksheet showing how to get the medians in cells H8, I8, J8, K8, L8 and O8, P8, Q8, R8, S8

Follow these steps:

  1. Acting as if you only want to add up the data in column O8 alone, type = median( in cell O8. This step is indicated by 1
  2. With your left mouse button held down, highlight cells O2 through O7 to select them. This step is indicated by Your selection is accepted when O2:O7 appears right after median( inside cell O8
  3. Press Ctrl + Enter

If I had pressed only Enter, I’d only have one total in cell O8, but as I pressed Ctrl + Enter, I have the medians in all of these cells. That’s really handy. See Fig. 1.1.7.

Fig. 1.1.7: NewData worksheet showing the medians in cells H8, I8, J8, K8, L8 and O8, P8, Q8, R8, S8

You may see ##### in cells in Excel when a column is not wide enough to show all of the cell contents. Formulas that return dates and times as negative values may also show as #####. See Fig. 1.1.8.

Fig. 1.1.8: NewData worksheet showing #### in cells H8, I8, J8, K8, L8 and O8, P8, Q8, R8, S8 because of insufficient space

To widen your columns and to show cell contents in full, double-click the right edge of the column header, or drag it slightly to the right side up to the width you want, as shown in Fig. 1.1.9.

Fig. 1.1.9: Cell A1 is being widened by dragging the right edge of the column header (A)

Alternatively, you can make the contents of Excel cells smaller by doing the following:

  1. Click Home and then click  next to Alignment (1)
  2. Check the Shrink to fit box in the Format Cells dialog box (2).

Read more Excel tips and shortcuts…

Leave a Comment