Introduction to the Course
2
Course Resources & Important Notes for New Student
Please click on the name of the file and DOWNLOAD the PROJECT_FILES.zip File. It has all the Excel Project Files . They are the same I use and they have descriptive names (so you can find them easy). If resources are available elsewhere, you will see a folder icon in the curriculum shown to the right of the video player. Simply click to expand the folder, and click each file to download.
Also, download the Course Study Tracker : Excel_Hacks_Study_Plan.xlsx. It will help you track your progress
Certificate of Completion: Once the progress bar above your video player reaches 100%, simply click the trophy icon to download your certificate of completion. If you still have problems to get your Certificate of Completion, please check out this Video
Ratings & Reviews: Udemy may prompt you very early in the course to leave a Review; please feel free to discard the prompt until you are ready to leave a rating. You may submit or update your rating at any point from the course dashboard, by clicking the stars next to the progress bar, in the top right corner of your screen
Q&A Forum: If you get stuck, please search the Q&A forum first, as many questions have already been answered there. For tips on using the Q&A tool, check out this video. If you have any questions I'll be more than happy to help you.
Platform-related issues: Blurry video or missing audio are out of our control. Please check your Internet Connection. All videos should be crystal clear! To change the resolution simply click the Gear Icon at the bottom right corner, to access settings, where you can adjust resolution or report technical issues. Try selecting Auto setting (for 1080p) instead of using the manual selection. If you continue to experience problems, click "Report an Issue" (using the gear icon) or e-mail support@udemy.com
Captions: Click "Transcipt" or "Captions" in the lower right to view transcripts or captions
Enjoy!
3
Excel 2019 Useful keyboard Shortcuts and Tips
Excel Interface Hacks
1
Customizing the Quick Access Toolbar
A really valuable tool is the Quick Access Toolbar. Learn how to completely customize it.
2
The new Ribbon interface that Microsoft added to Office 2007 was a huge change for the users. Learn the best tips to customize it.
3
Using Keyboard to Access 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.
4
The Best Techniques for Inserting, Deleting & Hiding Columns and Rows
Learn the best techniques for inserting, deleting, hiding and unhiding columns and rows.
5
The best hacks for Undo, Redo and Repeat features.
Excel Workbooks Hacks
1
4 Ways To Protect Excel Workbooks, Worksheets And Cells
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!
2
Navigate between Workbooks Quickly
Fast and easy to follow tips on how to Navigate between workbooks quickly.
3
Applying Document Themes in a Workbook
Document themes are an easy way to effectively change the colors, fonts and graphical effects in a document.
4
Saving Excel as a Read-only Webpage File
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.
5
Zooming In and Out 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.
Excel Worksheets Hacks
1
Navigate between Worksheets Quickly
There are some workbooks that have many worksheets. In this lecture I’ll show you the best ways to navigate between worksheets quicky
2
How to Create a Hyperlink to Other Worksheets
The best options on how to create a hyperlink to other worksheets.
3
How to Prevent Someone from Unhiding a Worksheet – Very Hidden Property
Learn how to hide a worksheet with this top hack and a bit of visual basic
4
Copying and Moving Worksheets in Various Ways
Use keyboard shortcuts to copy and move worksheets.
5
Entering Data to Multiple Worksheets at a Time
A time-saver technique if you have similar structure for your worksheets.
6
How to Print a 2-page Worksheet on 1 page
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.
7
Hiding Scrollbars in a Worksheet
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.
8
How to Set the Number of Worksheets
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.
9
Learning Different Techniques for Navigating within a Given Worksheet
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.
10
How to Create and Save Custom Views of a Worksheet
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.
Excel Formatting Hacks
1
Solving the Problem with Leading Zero
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.
2
How to Add Text to a Number
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.
3
How to Enter a Line Break into One of Your Cell’s Data
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.
4
Using Wrap Text and Shrink Cell Tools to Have the Size of the Cells
Extremely useful tools that you can use to have the size of the cells right.
5
Creating a Conditional Format based on a value
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.
6
Blocking a User from Interacting Outside a Specific Range
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.
7
How to Hide the Status Bar
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.
8
Changing Values without Formulas
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.
9
How to Highlight Formula Cells using Conditional Formatting
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.
10
How to Color Rows using Conditional Formatting - Alternate Row Shading
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.
11
How to Format Numbers using Keystroke Shortcuts
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.
12
How to Modify or Create a Cell Style
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.
13
Duplicate Records and How to Get Rid of These
You can use conditional formatting to quickly identify duplicate values in a range. An extremely useful hack!
14
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.
15
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.
16
How to Insert a Watermark
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.
17
Making Your Comments Stand Out
Change shapes, fill with colors, add an image to a comment and many more in this lecture.
18
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.
Excel Productivity Hacks
1
The Paste Special Feature
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.
2
How to Display a Print Preview using Keyboard Shortcuts
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.
3
Using Fill Handle to Enter Sequences of Values
All the tips and tricks of Fill Handle.
4
How to Use FlashFill to Fill Values in a Range Rapidly
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.
5
How to Select Data using Various Keystroke Shortcuts
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.
6
How to Set a Specific Print Area
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.
7
Entering Data Quickly and Efficiently
Learn how to enter data very fast with these hacks.
8
Copying and Moving Data with Various Ways
Why to use the 2-step process copy & paste and instead use a different and faster way? Learn how!
9
Using Pick from Drop-down List and Enter Data Quickly
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.
10
Using AutoCorrect to Enter Data Fast
Autocorrect can save you a tone of time and it’s a really powerful shortcut.
11
How to Freeze Titles and Split Screens
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.
12
How to Create a Data Entry Form
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.
13
Filling non-adjacent Cells with the Current Entry
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.
14
Highlighting Blank Cells and Sorting Columns with Them
Learn an easy way to select only the blank cells from this column, in a matter of second.
Excel Formulas Hacks
1
Filling Random Values into Every Cell in a Selection
Is there a way to fill in dummy values in a worksheet so you can make up your own datasets? Watch this lecture!
2
How to Generate Random Decimal Numbers between 0 and 1
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.
3
How to use COUNTIF and Data Validation to Prevent Duplications
Use the combination of COUNTIF and data validation and get rid of duplicate values.
4
How to Add Comments Inside Formulas
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.
5
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.
6
Identifying and Fixing Error inside of an Excel Formula
Identifying the errors in your formulas is not an easy task, but with simple steps you can make it.
7
Finding Related Formulas Easy
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.
8
How to Enter the Current Date or Time
In this video tutorial I’ll show you how to enter the current date and time
9
Using the new-in-Excel-2016 Function PDURATION to Calculate Time
PDURATION is a new function that help us calculate how long it takes to reach a specific goal.
10
How to Round Values with Various Ways
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.
11
Converting Formulas to Values Easily
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.
12
Using Named Ranges in Formulas
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
13
Displaying All Worksheet Formulas in a Second
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.
14
Using TRIM Function to Get Rid of Unwanted Spaces
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.
15
How to Calculate a Person's Age
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.
16
How to Convert Values between Measurement Systems
Did you ever wondered how to convert miles to kilometers, using Excel? Learn this easy hack to do any conversion you want.
17
Locking and Hiding Formula Cells
If you want to hide all the formula cells on the worksheet watch this lecture.
Excel Tables & Pivot Tables Hacks
1
Create Table & Formatting Data as Table with Table Styles
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.
2
Using the Structure Reference to Create a Formula
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.
3
How to Create Separate Worksheets from PivotTable using Filters Area
Sometimes we need to create separate worksheets for a specific fieldI’ll show you how to do that in two simple steps.
5
Excel Tables & Pivot Tables Hacks
Excel Visualization Hacks
1
How to Move or Resize a Chart using Special Keys
The fastest methods to to move or resize a Chart
2
Create Mini Charts using Sparklines
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.
