Excel Shortcuts, Hacks & Tricks: 100+ Tips for Excel 2016
4.7 (309 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,532 students enrolled

Excel Shortcuts, Hacks & Tricks: 100+ Tips for Excel 2016

Learn Excel Fast using Functions & Formulas, Keyboard Shortcuts, Charts, Pivot Tables & Dates. With Microsoft Excel 2016
Highest Rated
4.7 (309 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,532 students enrolled
Last updated 3/2020
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 3 hours on-demand video
  • 2 articles
  • 3 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Maximize the power of Excel with 100 ready-to-use tips hacks on Excel Interface, Workbooks, Worksheets, Formatting Secrets, Organizing Data, Formulas, Dates, Numbers, Pivot Tables and Charts
  • Accomplish and Master all of your Excel Tasks
  • Increase your Productivity and be more Efficient than you ever imagined
  • Save precious minutes from the very first Tips
  • Fully understand the Excel Hacks using the Custom-Build Templates that I have in every Lecture
  • Improve your Excel Skills using the latest version of Excel 2016
  • Microsoft Excel Installed On Your Computer (2013 or 2016 is Preferable)
  • A willingness to learn new Tips and reinvent your work in Excel

If you’ve ever thought to yourself, “There has to be a better way to do this,” while using Excel, then know you're probably right. With over 100 HD Lectures + Excel Templates, I’m going to teach you the best 100 time-saving tips and tricks in Excel to make Excel more efficient than you ever imagined.

I created this course to help Excel users avoid common spreadsheet stumbling blocks and reveal ways to maximize the power of Excel. I started with no knowledge of Excel and when I started to use it on a daily basis, I said to myself, “There has to be a better way to do this.” In this course, I compile all my notes and my knowledge and I want to teach you how to improve your skills in short order.

Are you ready to fire up your skills and become a super-efficient Excel user?

First, you’re going learn my top 10 favorite Excel Hacks that can save you a ton of time.

Then I’ll show you some tips that will help you to customize your Excel Interface.

You’re gonna learn several new tips to organize your data, to work with formulas or Dates, to hack your charts and Pivot tables, to complete lightning your tasks in Workbooks and Worksheets, and a bunch of other cool stuff.

Do you want to enter data to multiple worksheets at a time? No problem. Do you also want to block a user from interacting outside a specific range? Piece of cake. Do you want to get rid of duplicate values in seconds? Easy! These and 97 more tips and hacks can add significant productivity and save money across your organization, too.

The course is entirely project based and requires no Excel experience. It has been recorded with the brand-new Excel 2016 in full HD 1080p.

If you get stuck, don’t worry. I have fast and full support through the discussion board.

And if you don’t like the course, simply return it! There is a 30-day money back guarantee.

But first, you probably want to know who I am. What real world experience do I have? My name is Andreas and I have been a teacher for over 16 years. I have my own computer learning school and I have taught Excel to over 3,000 students. I use Excel daily and because of that, I had to find hacks and tips to increase my productivity and efficiency. So I made this collection of powerful tips. At the end of this course, you’ll accomplish and master all of your spreadsheet tasks at least half the time that you do now.

Thanks for your interest in the course, I’m really excited to share it with you. Check out the free previews and I’ll see you in class.

Who this course is for:
  • Anybody, from a Novice to an Expert can benefit with this collection of powerful Excel Hacks and Tricks
  • Ideal for Managers, Entrepreneurs and Students
Course content
Expand all 107 lectures 03:15:52
+ Introduction to the Course
3 lectures 08:24

All Excel project files are the same I use and they have descriptive names (so you can find them easy)

DOWNLOAD: Project Files & Course Study Tracker & Important Notes for New Student
+ My Top Hacks and Tips
11 lectures 22:14

In this video tutorial we will learn how to protect an entire workbook , sheets or cells. There are different methods and level of protection. For example:

  • You can protect all the contents of your workbook
  • You can allow others to open your workbook as read-only
  • You can protect the structure of a workbook
  • You can protect a sheet or a specific part of a sheet 

Let’s get started!

4 Ways To Protect Excel Workbooks, Worksheets And Cells
There are some workbooks that have many worksheets. In this lecture I’ll show you the best ways to navigate between worksheets quicky
TIP #1 - Navigate between Worksheets Quickly
Learn how to hide a worksheet with this top hack and a bit of visual basic
Preview 02:02
An extremely useful technique is to limit a specific scroll area so if a user wants to click outside this area he will not be able to.
Preview 01:08
A time-saver technique if you have similar structure for your worksheets.
TIP #4 - Entering Data to Multiple Worksheets at a Time

Although there are many keyboard shortcuts to navigate in a worksheet, we don’t use them. We use most of the time the mouse which is a time-waster. In this lecture you’ll find more faster and useful ways to navigate within a worksheet.

TIP #5 - Learning Different Techniques for Navigating within a Given Worksheet
Learn how to enter data very fast with these hacks.
TIP #6 - Entering Data Quickly and Efficiently
FlashFill is a powerful new feature of Excel 2013 and 2016 that can help you when you need to enter a lot of repetitive information. Flash Fill recognizes a pattern in your data and works best when your data has some consistency.
TIP #7 - How to Use FlashFill to Fill Values in a Range Rapidly
Often we want to increase or decrease the values of a list of numbers by adding or subtracting a fixed number. Other times we want multiply them by a percentage to calculate a discount or an increment. These tasks can be done without writing formulas. Learn how in this video tutorial.
TIP #8 - Changing Values without Formulas

Sometimes you need a list of unique random decimal numbers, between 0 and 1, just to try some functions or some features in Excel. There is a very useful Function called RAND. In this Lecture I’ll show you how it works.

TIP #9 - How to Generate Random Decimal Numbers between 0 and 1

You can quickly create a chart with keyboard shortcuts. Learn how to do it!

TIP #10 - Creating a Chart with Keyboard Shortcuts
Questions about the top Excel tips
4 questions
+ Excel Interface
4 lectures 06:46
A really valuable tool is the Quick Access Toolbar. Learn how to completely customize it.
TIP #11 - Customizing the Quick Access Toolbar

The new Ribbon interface that Microsoft added to Office 2007 was a huge change for the users. Learn the best tips to customize it.

TIP #12 - Customizing the Ribbon

Many times, keyboard shortcuts are a faster way to manipulate the ribbon without using the mouse. Learn how to use keyboard to access Ribbon Commands.

TIP #13 - Using Keyboard to Access Ribbon

The best hacks for Undo, Redo and Repeat features.

TIP #14 - Undo, Redo and Repeat
Did your learn the Excel Interface?
3 questions
+ Workbooks and Worksheets
15 lectures 23:10
Fast and easy to follow tips on how to Navigate between workbooks quickly.
TIP #15 - Navigate between Workbooks Quickly

Many times we have a lot of data in a worksheet. And usually, it all might not fit on one screen. If I want to see my data more clearly, I need to adjust the zoom level. I’ll teach you the best methods.

TIP #16 - Zooming In and Out Quickly
Document themes are an easy way to effectively change the colors, fonts and graphical effects in a document.
TIP #17 - Applying Document Themes in a Workbook
Use keyboard shortcuts to copy and move worksheets.
TIP #18 - Copying and Moving Worksheets in Various Ways
Learn the best techniques for inserting, deleting, hiding and unhiding columns and rows.
TIP #19 - The Best Techniques for Inserting, Deleting & Hiding Columns and Rows

If you have a worksheet that splits to two pages or more, you might want to reduce it so it will fit on one page. Learn how.

TIP #20 - How to Print a 2-page Worksheet on 1 page

A user can concentrate in the data of a worksheet without moving around, using the horizontal and vertical scrollbars. So it would be useful to hide these scrollbars and also the sheet tabs.

Preview 01:11
If you remember from Excel 2007 or 2010 when you created a new workbook it came with 3 worksheets. In Excel 2013 there is only 1 worksheet in a new workbook. You can change the number of worksheets that appear by default.
TIP #22 - How to Set the Number of Worksheets
Instead of using functions to calculate and summarize your data, you can use the status bar at the bottom of the screen. This technique is a fast way to get a summary of your data.
TIP #23 - Using Status Bar to Get a Quick Summary of your Data
If you prefer a less cluttered look of Excel, it is useful to hide various parts of it to make more room for displaying your data. So it would be useful to hide the status bar, at the bottom of the Excel window. This change however requires a small Visual Basic Code. Don’t be afraid, it’s only a small statement.
TIP #24 - How to Hide the Status Bar

It’s important to preview our data before we print them, so we can prevent printing unnecessary data or blank pages.Learn the two fastest ways.

TIP #25 - How to Display a Print Preview using Keyboard Shortcuts
A data table with many rows and columns is I think very frequent. And most of the time the first row or first column contains the headers. So we want the first row to be still and to keep it visible while we scroll through the rest of the sheet. Learn how.
TIP #26 - How to Freeze Titles and Split Screens

Sometimes we have the need to send an Excel Workbook with a read-only format. If you need to share information in a workbook with someone and be assured that the information remains intact you can save your Excel workbook as an HTML file.A fast and secure way to send your precious Excel data.

TIP #27 - Saving Excel as a Read-only Webpage File

Inquire add-in is an auditing tool that enables you to provide a very detailed workbook analysis. It gives you a report in many categories.

TIP #28 - How to Analyze an Active Workbook using the Inquire Add-in

PDF files are extremely popular, especially on the internet. Excel gives you the ability to export a workbook into a pdf file, from File tab and save as option. But what about the opposite process?

TIP #29 - Getting Data from a PDF File
Questions about Workbooks and Worksheets Hacks
4 questions
+ Formatting
19 lectures 33:18
Most of us had difficulties when we tried to enter a number that began with a leading zero, like tax registration number or ZIP codes. The problem is that Excel removes the leading zero and leaves only the other digits. In this Lecture I’ll teach you a hack that the leading zero remains intact.
TIP #30 - Solving the Problem with Leading Zero
If we have a cell with the result of a function or a formula, it would be useful sometimes to add an explanatory text besides the value. In this Lecture we discuss an easy trick to add text to a number.
TIP #31 - How to Add Text to a Number

Line break is more known in Word, and it is used very often. But what if we had data that didn’t fit into the cells that we’re typing in?. There is a keyboard shortcut that adds a line break.

TIP #32 - How to Enter a Line Break into One of Your Cell’s Data
Excel has a rather odd way to display time data. Watch this video tutorial to learn a nice tip on how to show hours greater than 24.
TIP #33 - How to Show Hours Greater than 24
Extremely useful tools that you can use to have the size of the cells right.
TIP #34 - Using Wrap Text and Shrink Cell Tools to Have the Size of the Cells
Sometimes you’ll need to compare many cells, for example the monthly sales of a year, with a value like your monthly sales goal. If the sales of a particular month exceed the monthly sales goal, then it will fill with a specific color. So you want to create a conditional formatting rule. Learn the best method to do that.
Preview 01:50

Identifying the errors in your formulas is not an easy task, but with simple steps you can make it.

TIP #36 - Identifying and Fixing Error inside of an Excel Formula
How many times we have data calculated from a formula and we want to take these results, not the formula, and paste them into another column or worksheet for example? Learn an extremely efficient and fast technique in this Lecture.
TIP #37 - Converting Formulas to Values Easily
Some Formulas in a worksheet is fairly difficult to understand. So when a user looks at the result of a formula and then click on formula bar to read the steps of the exact formula, it’s difficult to understand.A nice way to give information about the formula is to add comments inside them.
TIP #38 - How to Add Comments Inside Formulas

Have you ever had a worksheet with many formulas and wanted to identify them easy? I bet you had. I use two different techniques to identify these formulas and you can do the same.

TIP #39 - How to Highlight Formula Cells using Conditional Formatting

Adding a color background in our data is sometimes essential for a user to read the information easier. In this lecture we will discuss a different way, using conditional formatting, for alternate row shading.

TIP #40 - How to Color Rows using Conditional Formatting - Alternate Row Shading
To tell the truth, keystroke shortcuts are not so popular for the majority of the Excel users. Perhaps because we accustomed to using the mouse or because it’s difficult to remember so many shortcuts.But the reality is that keystroke shortcuts can help us to do the work with Excel much faster.
TIP #41 - How to Format Numbers using Keystroke Shortcuts
A style can change the look of a number, the alignment, the background color, the borders or the font.Learn all the tips in this video tutorial.
TIP #42 - How to Modify or Create a Cell Style

You can use conditional formatting to quickly identify duplicate values in a range. An extremely useful hack!

TIP #43 - Duplicate Records and How to Get Rid of These
A Bulleted list is used in Word most of the times. Searching Excel features, you can’t find how to create a bulleted list of items. But there is a trick that you can insert bullet character in Excel.
TIP #44 - Creating Bulleted List
With Format Painter you can copy cell formatting, including number formats, borders, cell merging, and conditional formatting, and paste them over another range. Learn the best tips using the Format Painter.
TIP #45 - Format Painter Tips
Most of the times a watermark is an image or a text that appears on a printed page, like a company logo. The problem is that Excel doesn’t have a command to insert a watermark, like Word. But there is a tip that you can add a watermark by inserting a picture in the page header or footer, with simple steps.
TIP #46 - How to Insert a Watermark
Change shapes, fill with colors, add an image to a comment and many more in this lecture.
TIP #47 - Making Your Comments Stand Out
How to compare two lists of items and identify differences between them, using conditional formatting and formulas.
TIP #48 - How to Compare Two Lists of Items and Identify Differences between the
Questions about Formatting Tips
4 questions
+ Organizing and Working with Data
20 lectures 35:53
Keystroke shortcuts are way faster when we select data in Excel. In this lecture we will learn some useful shortcuts to make our work with Excel easier and faster.
TIP #49 - How to Select Data using Various Keystroke Shortcuts

Why to use the 2-step process copy & paste and instead use a different and faster way? Learn how!

TIP #50 - Copying and Moving Data with Various Ways
The Paste special feature is very powerful and yet a small amount of Excel users uses it. Be a clever user and learn all the tricks of Paste Special feature.
TIP #52 - The Paste Special Feature
A powerful new feature in Excel 2016 is Quick Analysis tool. It can make your work with Excel more useful and faster.
TIP #53 - Using Quick Analysis Tool to find Information about Your Data
Excel worksheets can have a lot of data, like logo image, shapes, many data tables etc. If we want to print out a specific area of our sheet, we have to define a print area. This tip is ideal for large worksheets that you want to print a specific part of the data.
TIP #54 - How to Set a Specific Print Area
The best options on how to create a hyperlink to other worksheets.
TIP #55 - How to Create a Hyperlink to Other Worksheets
Let’s see an extremely useful feature which is Custom View. A custom view focus on a small subset of your data and it is ideal for presentations.
TIP #56 - How to Create and Save Custom Views of a Worksheet
It’s always better to present our information in a more compact way and not displaying all this data. Learn how!
TIP #57 - Presenting Information in a More Compact Way, using Group Tool
If you are a business owner or working in the financial department of a company, you already know how important is to set goals or apply different scenarios with different goals. There is a tool in Excel called Goal Seek.
TIP #58 - Applying Goal Seek Tool to Find a Target
To enter names in Excel is very common but very time consuming, especially repeating names. There is one technique called Pick from Drop-down List that you can apply and enter data quickly and efficiently.
TIP #59 - Using Pick from Drop-down List and Enter Data Quickly

What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. Master what-if analysis quickly and easily with the hacks of this lecture.

TIP #60 - Forecasting Scenarios with Scenario Manager
Autocorrect can save you a tone of time and it’s a really powerful shortcut.
Preview 01:33
One of our goal when we work in Excel is to do our job fast and easy. A time-saver feature is Custom List. If you use a list frequently and you don’t want to waste time typing them it, you can create a custom list.
TIP #62 - Creating a Custom List
In this video tutorial I’ll show you how to create a Data Form Entry in Excel. If you have a large dataset, many rows and columns, then creating a Data Form allows you to look at one row at a time.
TIP #63 - How to Create a Data Entry Form

Duplicate values is a big problem if you have a large data list. You understand that to find duplicate values in hundreds rows or columns is difficult. In this video tutorial I’ll show you 2 ways to find easy duplicates.

TIP #64 - Eliminating Duplicate Values from a Data List
Sometimes a worksheet, a dashboard for example, summarizes data from other worksheets. So in one worksheet we have the data table or the original data source If you prefer, and in another worksheet we have summaries of data, formulas and so on. With Watch Window feature it’s easy to watch as many cells as you want from others worksheets.
TIP #65 - Monitoring the Value in a Worksheet using Watch Window Tool

I can’t remember how many times I have used the copy paste feature, entering the same data in different cells, before I find out the following trick that I’ll show you. It was a really time-waster.

TIP #66 - Filling non-adjacent Cells with the Current Entry
Learn an easy way to select only the blank cells from this column, in a matter of second.
TIP #67 - Highlighting Blank Cells and Sorting Columns with Them
A quick way to make the rows columns and the columns rows.
TIP #68 - Making the Rows Columns and the Columns Rows
Questions about Organizing and Working with Data
3 questions
+ Numbers, Dates and Formulas
19 lectures 31:19

Is there a way to fill in dummy values in a worksheet so you can make up your own datasets? Watch this lecture!

TIP #69 - Filling Random Values into Every Cell in a Selection
Use the combination of COUNTIF and data validation and get rid of duplicate values.
Preview 02:26
Entering series of Data and especially Dates is one of the most common tasks when we prepare our worksheet. There are many ways to enter series of dates and in this video tutorial I’ll show you the best solutions.
TIP #71 - How to Enter a Series of Dates using Auto Fill Options
The Autosum button is probably one of the most used of Excel .Autosum has some tricks that makes our work easier and faster. Learn all the secrets here.
Preview 01:51
In this video tutorial I’ll show you how to enter the current date and time
TIP #73 - How to Enter the Current Date or Time
Sometimes for complex worksheets, it’s really chaotic to search which cells are affected by the value of a selected cell. In this lecture I’ll show you hacks that they’ll save you a ton of time.
TIP #74 - Finding Related Formulas Easy
If you want to calculate the difference between two dates there is a new function in Excel 2013 and 2016 versions. Watch the lecture and learn all the steps.
TIP #75 - How to Calculate the Number of Days between Two Date values
In this lecture I’ll show you a different approach to look up values without using build-in functions.
Preview 02:23
PDURATION is a new function that help us calculate how long it takes to reach a specific goal.
TIP #77 - Using the new-in-Excel-2016 Function PDURATION to Calculate Time
Excel with the help of ROUND function can round a number up or down. This is very useful especially if you want to round a bunch of values within seconds.
TIP #78 - How to Round Values with Various Ways
A really time-saver feature that you must use.
TIP #79 - Calculating Running Totals
To create named ranges is not only very easy but also very useful especially if we want to write formulas. Watch how we can use them in Formulas
TIP #80 - Using Named Ranges in Formulas
Sometimes we want formulas to appear in our worksheet for teaching purposes or to simply see some complex formulas as a text. Learn an easy way to display the text of a formula.
TIP #81 - Displaying the Formula Text
There are some constants like VAT that it would be better to store them using a name and use them in your formulas. In this lecture I’ll teach you a feature called Define Name.
TIP #82 - Defining a Name that Refers to a Constant
When I have a large worksheet with many complex formulas and searching for an error figuring out what’s going on, I use a tip that appears me all the formulas in the worksheet. In this lecture I’ll show you this time-saver tip.
TIP #83 - Displaying All Worksheet Formulas in a Second
Leading or unwanted spaces can cause us big trouble. TRIM function is the easiest way to get free of unwanted spaces, especially in large worksheets.
TIP #84 - Using TRIM Function to Get Rid of Unwanted Spaces
To calculate a person’s age is a really brain teaser. The calculation depends on the current day and also from leap years. In this lecture you will learn how to manage this using an unknown function.
TIP #85 - How to Calculate a Person's Age
Did you ever wondered how to convert miles to kilometers, using Excel? Learn this easy hack to do any conversion you want.
TIP #86 - How to Convert Values between Measurement Systems
If you want to hide all the formula cells on the worksheet watch this lecture.
TIP #87 - Locking and Hiding Formula Cells
Quiz about Numbers, Dates and Formulas
2 questions
+ Tables and Pivot Tables
6 lectures 06:56
Quick ways to Create a table using keyboard shortcuts.
TIP #88 - Create a Table using Keyboard Shortcuts

Create easily with the help of ROW function, a column that contains a unique value for each row in your table.

TIP #89 - Adding a Unique Identification Number in Your Table
You don’t have to worry for the format of your data before you convert them into a table. Now it’s easy to get rid of this with a trick that I’ll show you.
TIP #90 - Formatting Data as Table with Table Styles
If you use Excel tables then I’ll show you an excellent tip for creating formulas easy and without the need to update them when data changes.
TIP #91 - Using the Table Reference to Create a Formula
In newer versions of Excel there is a new capability to create very quickly a pivot table, called recommended Pivot Table. Learn the steps and tips for this useful feature.
TIP #92 - Create a Recommended PivotTable Quickly
Sometimes we need to create separate worksheets for a specific fieldI’ll show you how to do that in two simple steps.
TIP #93 - How to Create Separate Worksheets from PivotTable using Filters Area
Quiz about Tables and Pivot Tables
1 question
+ Charts
8 lectures 13:31
A new powerful feature of Excel is Recommended Charts. Watch in this lecture, the details and how we can use it.
TIP #94 - Let Excel Decide Which Chart is Ideal for Your Data
The fastest methods to to move or resize a Chart
TIP #95 - How to Move or Resize a Chart using Special Keys
A nice feature of Excel is Sparklines. It’s a chart inside a cell. And it’s not as small as you think. In this lecture we will learn tips and tricks of Sparklines.
TIP #96 - Create Mini Charts using Sparklines
If you want to learn how every time we change titles from data the changes appear on the chart, watch this lecture
TIP #97 - Linking Chart Titles with Cell Content
If you have several embedded charts on a worksheet, you might want to make them all exactly the same size. Let’s see how we can do this.
TIP #98 - How to Make all the Charts Exactly the Same Size
How do we fill missing data on the chart? Watch this video tutorial to learn ste steps.
TIP #99 - How to Deal with Missing Data for a Chart

Minimalistic charts are sometimes better than complex charts, if you want a cleaner look of your data. In this lecture I’ll show you how to create minimalistic column and pie charts.

TIP #100 - Creating Simple Column and Pie Charts
Quiz about Charts
1 question
My Final Thoughts & Thank you!
+ Bonus Section
2 lectures 14:21
Excel 2019 Tutorial for Beginners
Amazing discount for my other courses!