Top 100 Microsoft Excel Hacks with Excel 2016
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
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.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Introduction to the Course|
Welcome to the course! In this course you’ll learn the 100 best tips and hacks for Excel, saving you precious minutes every day, leading to a new, more efficient way to work with Excel! Have fun!
Before you start please click on the download link on the right hand side of your panel (the second icon on the top):
How to view in HD
|Section 2: My Top Ten (10) Hacks and Tips|
|There are some workbooks that have many worksheets. In this lecture I’ll show you the best ways to navigate between worksheets quicky|
|Learn how to hide a worksheet with this top hack and a bit of visual basic|
|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.|
|A time-saver technique if you have similar structure for your worksheets.|
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.
|Learn how to enter data very fast with these hacks.|
|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.|
|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.|
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.
You can quickly create a chart with keyboard shortcuts. Learn how to do it!
I need your help!
Questions about the top ten Excel tips
|Section 3: Excel Interface|
|A really valuable tool is the Quick Access Toolbar. Learn how to completely customize it.|
The new Ribbon interface that Microsoft added to Office 2007 was a huge change for the users. Learn the best tips to customize it.
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.
The best hacks for Undo, Redo and Repeat features.
Did your learn the Excel Interface?
|Section 4: Workbooks and Worksheets|
|Fast and easy to follow tips on how to 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.
|Document themes are an easy way to effectively change the colors, fonts and graphical effects in a document.|
|Use keyboard shortcuts to copy and move worksheets.|
|Learn the best techniques for inserting, deleting, hiding and unhiding 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.
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.
|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.|
|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.|
|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.|
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.
|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.|
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.
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.
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?
Questions about Workbooks and Worksheets Hacks
|Section 5: Formatting|
|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.|
|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.|
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.
|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.|
|Extremely useful tools that you can use to have the size of the cells right.|
|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.|
Identifying the errors in your formulas is not an easy task, but with simple steps you can make it.
|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.|
|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.|
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.
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.
|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.|
|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.|
You can use conditional formatting to quickly identify duplicate values in a range. An extremely useful hack!
|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.|
|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.|
|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.|
|Change shapes, fill with colors, add an image to a comment and many more in this lecture.|
|How to compare two lists of items and identify differences between them, using conditional formatting and formulas.|
How can I help you?
Questions about Formatting Tips
|Section 6: Organizing and Working with Data|
|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.|
Why to use the 2-step process copy & paste and instead use a different and faster way? Learn how!
|All the tips and tricks of Fill Handle.|
|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.|
|A powerful new feature in Excel 2016 is Quick Analysis tool. It can make your work with Excel more useful and faster.|
|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.|
|The best options on 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.|
|It’s always better to present our information in a more compact way and not displaying all this data. Learn how!|
|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.|
|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.|
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.
|Autocorrect can save you a tone of time and it’s a really powerful shortcut.|
|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.|
|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.|
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.
|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.|
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.
|Learn an easy way to select only the blank cells from this column, in a matter of second.|
|A quick way to make the rows columns and the columns rows.|
Questions about Organizing and Working with Data
|Section 7: Numbers, Dates and Formulas|
Is there a way to fill in dummy values in a worksheet so you can make up your own datasets? Watch this lecture!
|Use the combination of COUNTIF and data validation and get rid of duplicate values.|
|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.|
|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.|
|In this video tutorial I’ll show you how to enter the current date and 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.|
|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.|
|In this lecture I’ll show you a different approach to look up values without using build-in functions.|
|PDURATION is a new function that help us calculate how long it takes to reach a specific goal.|
|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.|
|A really time-saver feature that you must use.|
|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|
|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.|
|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.|
|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.|
Hi, I am Andreas and I'm a teacher. I really love learning and teaching whenever I can.
I have over 16 years of experience in teaching as an instructor, especially in Microsoft Excel. I have my own computer learning school, called Futurekids, from 2001 to now. I am the founder of Mellon Training, an online video tutorials learning school.
I have a Master's Degree in Computer Science and a Degree in Electronic Engineering.
I always wanted to create my own business. Being your own boss is an incredibly liberating experience but difficult as well.
I have a passion for software products that make people's lives easier. I teach Microsoft Excel and all Microsoft software products for over 17 years. I am a professional Forex Trader for over 5 years.
I love to create websites and optimize them with Search Engine Optimization techniques. I created my online e-shop, called Bite me not jewels, with my wife.
I live in Rodos, a beautiful Greek island. When I am not working, I love being with my family and my two kids. Travel, forex trading, and reading books are my favorite hobbies.