Excel Hacks & Shortcuts: 100+ Best Excel Tips | Andreas Exadaktylos | Skillshare
Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
86 Lessons (3h 18m)
    • 1. Introduction

      2:12
    • 2. Excel 2019 Useful keyboard Shortcuts and Tips

      9:31
    • 3. Customizing the Quick Access Toolbar

      2:20
    • 4. Customizing the Ribbon

      2:26
    • 5. Using Keyboard to Access Ribbon

      1:20
    • 6. The best techniques for inserting, deleting, hidin

      2:52
    • 7. Undo, Redo and Repeat

      2:29
    • 8. 4 Ways To Protect Excel Workbooks, Worksheets And

      7:48
    • 9. Navigate between workbooks quickly

      2:18
    • 10. Applying Document Themes in a Workbook

      1:25
    • 11. Saving Excel as a read only Web Page file

      1:45
    • 12. Navigate between worksheets quickly

      2:20
    • 13. How to create a hyperlink to other worksheets

      2:44
    • 14. How to prevent someone to unhide a worksheet

      2:53
    • 15. Copying and Moving Worksheets with various ways

      2:40
    • 16. Entering Data to Multiple Worksheets at a time

      2:13
    • 17. How to print a 2 page worksheet on 1 page

      1:48
    • 18. Hiding scrollbars in a worksheet

      1:27
    • 19. How to set the number of worksheets

      1:27
    • 20. Learning different techniques for navigating within a given worksheet

      2:21
    • 21. How to create and save custom views of a worksheet

      2:54
    • 22. Solving the problem with Leading Zero

      1:56
    • 23. How to add text to a number

      1:48
    • 24. How to enter a Line Break into one of your cells data

      3:26
    • 25. Using Wrap Text and Shrink Cell Tools to have the size of the cells right

      2:23
    • 26. Creating a Conditional Format based on a value

      2:50
    • 27. Blocking a user to interact outside a specific range

      2:10
    • 28. How to hide the Status Bar

      2:04
    • 29. Changing values without formulas

      2:11
    • 30. How to highlight formula cells using Conditional Formatting

      2:16
    • 31. How to color Rows using Conditional Formatting

      2:36
    • 32. How to Format Numbers using Keystroke Shortcuts

      1:34
    • 33. How to modify or create a cell style

      2:51
    • 34. Duplicate records and how to get rid of these

      1:37
    • 35. Creating Bulleted List

      2:37
    • 36. Format Painter Tips

      2:14
    • 37. How to insert a Watermark

      2:08
    • 38. Making your comments stand out

      2:14
    • 39. Creating a Custom List

      3:02
    • 40. The Paste Special feature

      2:33
    • 41. Using Fill Handle to enter Sequences of Values

      2:18
    • 42. How to use FlashFill to fill values in a range rapidly

      1:50
    • 43. How to select Data using various Keystroke Shortcuts

      2:14
    • 44. How to set a specific print area

      1:17
    • 45. Entering Data quickly and efficiently

      3:04
    • 46. Copying and Moving Data with various ways

      1:46
    • 47. Using Pick from Drop down List and enter Data quickly

      1:23
    • 48. Using AutoCorrect to enter Data fast

      1:33
    • 49. How to freeze titles and split screens

      2:34
    • 50. How to create a Data Entry Form

      2:22
    • 51. Filling non adjacent cells with the current entry

      2:06
    • 52. Highlighting Blank cells and Sorting columns with them

      2:54
    • 53. Filling Random Values into every cell in a selection

      1:46
    • 54. How to generate Random Decimal Numbers between 0 and 1

      1:10
    • 55. How to use COUNTIF and Data Validation to prevent duplications

      2:39
    • 56. How to add comments inside Formulas

      1:35
    • 57. The AutoSum magic

      2:00
    • 58. Identifying and fixing error inside of an Excel Formula

      2:00
    • 59. Finding related formulas easy

      1:59
    • 60. How to enter the current Date or Time

      1:21
    • 61. Using the new in Excel 2013 Function PDURATION

      1:37
    • 62. How to Round values with various ways

      2:42
    • 63. Converting formulas to values easy

      1:33
    • 64. Using Named Ranges in Formulas

      3:26
    • 65. Displaying all worksheet formulas in a second

      1:27
    • 66. Using TRIM Function to get rid of unwanted spaces

      2:01
    • 67. How to calculate a person's age

      1:52
    • 68. How to convert values between Measurement Systems

      1:43
    • 69. Locking and Hiding Formula cells

      2:28
    • 70. Formatting Data as Table with Table Styles

      2:19
    • 71. Using the table reference to create a formula

      1:55
    • 72. How to create separate worksheets from PivotTable

      1:20
    • 73. Create mini charts using Sparklines

      3:09
    • 74. Linking Chart Titles with Cell Content

      2:25
    • 75. How to make all the Charts exactly the same size

      1:16
    • 76. How to deal with missing data for a Chart

      1:28
    • 77. Creating a chart with keyboard Shortcuts

      1:41
    • 78. How to analyze an active Workbook using the Inquire add in

      2:41
    • 79. How to compare two lists of items and identify difference between them

      2:55
    • 80. Using Quick Analysis Tool to find information about data

      1:55
    • 81. Getting Data from a PDF File

      1:34
    • 82. Presenting Information in a more Compact way, using Group Tool

      1:35
    • 83. Applying Goal Seek Tool to find a target

      2:14
    • 84. Forecasting Scenarios with Scenario Manager

      3:21
    • 85. Monitoring the value in a worksheet using Watch Window

      1:58
    • 86. Calculating Running Totals

      1:30
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

126

Students

--

Projects

About This Class

In this class my goal is to help users avoid standard stumbling blocks and revealing ways to maximize the power of excel. My main goal in this training is to increase your awareness of the best tools in excel to help you solve complex problems.

We’ll cover more than 100 tools and techniques, grouped into nine categories: Interface, Workbooks, Worksheets, Formatting, Productivity, Formulas, Tables & Pivot Tables, Visualization, and Analysis Hacks.

First I will teach you how to customize your excel interface fully. We’ll then dive into powerful tools for workbooks and worksheets, like document themes, protecting them, custom views, etc.

Once we’ve mastered the basics, I’ll walk you through the best formatting methods with real-world examples, like alternate row shading, get rid of duplicate records, solve the problem with leading zero, and much more. Then we will learn top time-saving productivity tips like the paste special feature, the flash fill, autocorrect, data entry forms, and more! Also, I’ll walk you through the best formula hacks to supercharge excel. You will learn how to use pivot tables easily,  visualizing tips to become more effective, and analysis hacks for quick and dynamic analysis.

The class is entirely project based and it’s full of examples which are fully explained and easy to understand. It has been recorder with the brand new Excel 2019 in full HD 1080p.

By the end of this Class, you’ll accomplish and master all your spreadsheet tasks at least half the time you do now.

So let's get started!

Meet Your Teacher

Teacher Profile Image

Andreas Exadaktylos

Teacher | Expert Computer Scientist | Entrepreneur

Teacher

LinkedIn: https://www.linkedin.com/in/andreasexadaktylos

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. 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 Masters 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 love to create websites and optimizing them with Search Engine optimization techniques. I created my online e-sh... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. Introduction: If you've ever thought yourself, there has to be a better way to do something while using Excel, then you're probably right. I had the same frustration when I started to use Excel for the first time and tried to solve complex problems. This course is some of the most effective tools used by Excel professionals. I put together a 100 plus time-saving tips in hacks to make excel more efficient. Hello, and welcome to Excel powerful shortcuts, hacks in tips cores. My name is Andreas and I'll be your instructor through this trip of knowledge. I'm a full-time teacher and have my computer learning school for over 20 years. I am a Microsoft certified expert with more than two decades of Microsoft Excel training, and I now teach students in over a 150 countries. I use Excel daily. And because of that, I had to find hacks and tips to increase my productivity and efficiency. That's why I created this course to connect theory with practice will cover more than 100 tools and techniques grouped into nine categories. Interface workbooks, worksheets, formatting, productivity, formulas, tables, and pivot tables, visualization and analysis hacks. First, I will teach you how to customize your Excel interface fully. We'll then dive into powerful tools for workbooks and worksheets like Document Themes, protecting them, custom views, et cetera. Once we've mastered the basics, I'll walk you through the best formatting methods with real-world examples like alternate row shading. Get rid of duplicate records, solve the problem with leading 0 and much more. Then we will learn top time-saving productivity tips like the paste special feature, the flash fill, auto correct data entry forms and more. Also, I'll walk you through the best formula hacks to supercharge Excel. You will learn how to use pivot tables easily visualizing chips to become more effective and analysis hacks for quick and dynamic analysis. By the end of this course, you'll accomplishing master all of your spreadsheet tasks at least half of the time you do now. Thank you, and I'll see you inside. 2. Excel 2019 Useful keyboard Shortcuts and Tips: Hello students and welcome to the course. Before start demonstrating to you my collection of the greatest tips that I use an Excel. I'll start by showing you some keyboard shortcuts that I use very often and they will help you become faster. The right tip will help someone save an hour a week, almost 50 hours a year. Here we have a table with some tasks on your left that we want to accomplish, and the key combination of each task to your right. So let's start to explain each of them. If you want to insert a new line within a cell, you will press Alt plus enter. To give you an example, let's go to this Excel workbook and click on a cell. I already typed. Insert a line within a cell. Suppose you want to insert a new line after the word line. Your first step is to go to the formula bar where you can see the phrase that I typed and click right after the word line. Then I press Alt plus enter and a new line just inserted. All right, the second task is the undue. As you already know, the undo button in Excel, revert your worksheet to the state that it was in before you just performed the most recent action. I prefer to use the undo feature using a keyboard shortcut because it's much faster than the button itself. So to quickly reverse previous actions, you can press control plus z, which is the undo function. Keep in mind that Excel is unable to undo some actions including clicking menu items, saving files, and deleting sheets. Now, if you want to quickly insert a new row or column you can use to control and the plus keyboard shortcut. Take a look. This is super-simple. Press control plus, then use the arrow keys to choose from these options. Similarly, using control and minus keys, you can delete a row or column. Great. Now let's move on to the next task. There are times that I have many sheets and I want to move quickly to the next or previous sheet. So very efficient way to do that is by pressing the control page down or page up key combination. Let's move on to the next task with control and down arrow, I can see that the last line of a table, and with control and up arrow, the first to select the entire range of a table, I can use the control plus a. So let me give you an example of these keyboard shortcuts. Here I have a table with four columns. The first line of the table is in row 12 and the last line is in row 24. Let's click on a random cell of the table. Now, grab control and down arrow and look that the act of cell goes to the last line of the table. Try that again with control and up arrow. It goes to the first line. Think about how useful it is when you have a table with thousands of rows. Also with control plus a, I can select the entire range of the table like this. The table is a useful feature for us to do with some operations in Excel. So sometime we need to create a table based on a range of data. If I want to turn a data range like this into a table, I'll click anywhere inside the data range and press control plus t. Here is a little dialog box asking if the data range that Excel highlighted is correct and if your table has headers, so these are both correct and I will click okay, pretty straight forward right? After you create an Excel table, you may want to change the table style up here, but this is optional. Now let's move on to the next super simple keyboard shortcut, which is saving your Excel workbook. With control plus s, You can quickly save the workbook. You can avoid unexpected results in wasted paper by previewing your worksheet before printing. To preview the worksheet before printing it, press control plus F2. Let's try it now. Control plus F2. And it opened the Print Options which has a preview on your right if your worksheet is okay, the next step is to print it. But if not, you can change the margins, the orientation, the paper size, and much more from the settings section. Alright, let's demonstrate to you the next shortcut. Find and Replace feature in Excel is one of the most important tasks when you work with spreadsheets. Now the fastest way to find anything in your sheet, such as a particular number or text string, is to use the control plus F shortcut. When I press them, it will open rapidly the find and replace window. That's simple. Now we're going to learn some useful keyboard shortcuts that I often use when I make dashboards, charts, and design objects. Let's start with control plus a, which can also be used for objects and charts. Use this shortcut to select all objects, but you need to click on an object first. Otherwise it will select all the cells of the worksheet. So highlight this shape and press control plus a take a look. It highlights all the objects. As you can see, super symbol. Many times you might have faced a situation where we need to do a bunch of tasks repeatedly while working on an Excel worksheet. You may need to insert a blank row or column at a black border, read filled with cell, et cetera, to multiple cells and repeating the same task again and again can be quite time consuming. F4 repeats the last formatting action. How can I use it? In this table, we are going to fill with a gray color, these cells from A1 to F1. Now in a random cell, for example, a ten, I'll type my name and press F4. Excel repeats the last formatting Action, which was to grey fill color. Keep in mind that the F4 shortcut is also used when we want to change cell reference between absolute and relative. But when we are in the Edit formula mode to similar keyboard shortcuts that copy attributes and a format of an object are control plus Shift plus C and control plus Shift plus v. Let's take an example in this worksheet. Here, I have two objects. The first one has a different Phil and text color. I will use these shortcuts to copy the format of this object to this one. Highlight the first shape on the left. Press control plus Shift plus c. Now click on this rectangle on your right and control plus Shift plus v. How quick and easy was that? Let's move on to the control plus one shortcut. This is one of the most powerful shortcuts and I use it all the time. We already use it many times when we formatted charts or shapes, these keyboard shortcut opens the format dialog box. If you want to format a cell or a chart or anything else, highlighted and press control plus one. It's that simple. To do an example highlight discharge, press control plus one, and it opens a format chart area dialog box here. Next we'll learn some tips and shortcuts to save you time. Here we highlighted a chart. Chart tools are split into two tabs, design and format. If you want to get faster and if there are specific features that you constantly use when you change a chart, you can add those features to the Quick Access bar. Let's make an example. I often use the Change Chart Type button from the Design tab. So to add it, right-click on it and select the Add to Quick Access Toolbar. It's been added. As you can see, keep in mind that if a chart is not a highlighted, then the specific button is gray and I can't use it. For more commands. You can see all the available tabs. The last keyboard shortcut that I often use is if you hold down the Shift key and then click on the border of the chart. Then with your mouse, you can move the chart horizontally or vertically and it snaps into the column borders ideal for exact positioning. These are a list of the most useful keyboard shortcuts. These simple keyboard combinations can save you a huge amount of time in the long run. So please download the project file and practice them. You'll not regret it. Thank you for watching this video tutorial. Bye. 3. Customizing the Quick Access Toolbar: If you use an excel command frequently, you can add it to the Quick Access Toolbar. You can even add commands to the Quick Access Toolbar that is not in the ribbon. By default, the Quick Access Toolbar is located on the left side of the Excel title bar. It usually contains the Save button, the Undo button, and the redo button. You can easily add additional commands to the Quick Access Toolbar. For example, quick print or print preview. The number of icons that you add to your Quick Access Toolbar is limitless, but I recommend that you only add a few of these that you use. You can add a new command to the Quick Access Toolbar by simply right-clicking anywhere on the Quick Access Toolbar and then choose to customize the Quick Access Toolbar. Or by clicking this down pointing arrow. You see a list of commonly used items that you can add to the toolbar. Let's add the Print Preview and print command. Click on it and done. Look here. The New button added to the right of the tool bar. Now click again the down arrow and click on More Commands. This leads us to the dialog box, Excel options. Some of these items in the drop-down list or popular commands and are on this list. But some commands are appearing inside the All Commands list. Let's do an example. Select Save As from Popular commands and click the Add button. The Save As command added to this customization Quick Access Toolbar on your right, press OK. Here's the button. Keep in mind that if you put a button on the Quick Access Toolbar, it's always visible. If at a later time you don't need a command, right-click and remove it from the Quick Access Toolbar. Let's do it. If you add a macro to the tool bar, you can click the modify button to change the text and the icon of the macro. Finally, the easiest way to add command is to locate it in a tab. For example, right-click on this Fill Color button and choose to Add to Quick Access Toolbar. The bucket was added to the toolbar. I always have my toolbar commands. I tend to use the most frequently. Thanks for watching. 4. Customizing the Ribbon: The new ribbon interface that Microsoft added to office in 2007 was a huge change for the users and radically changes the way you work in Excel. Rubin always shows you the most commonly used options needed to perform a particular Excel task. But it catches a big part of the screen. So sometimes you want to hide it. I will demonstrate how you can hide the Ribbon. In the upper right-hand corner, there is a button just to the right of the question mark. Click on it. There are three options. As you can see, you can, for example, display just the tabs. So click on the Show tabs option and see the result. Only tabs are visible. Or if I press auto hide ribbon, you can hide the Ribbon entirely. The three dots will bring the Ribbon back. Pretty straightforward, right? If you want to hide the Ribbon using a keyboard shortcut, you can do it by pressing control plus F1. The control plus Shift plus F1 toggle will hide the Ribbon entirely and turn to full screen. Look at the result. Now let's return pressing the same keyboard shortcuts. So keyboard shortcuts are a lot faster way to hide the Ribbon as you can see. Well, I want to show you a quick, efficient way to customize the ribbon. Customizing the Ribbon is similar to customizing the Quick Access Toolbar. You can right-click on any part of the ribbon and then customize the ribbon. Click the down arrow on the left. If you want to display all the tabs, leave it as is for now. To the right, you can see from this list that all of the built-in ribbon tabs are displayed except for developer. If I check the box and then right-click, okay, you can see that the developer tab on the ribbon. I can also reorder items on the ribbon. Right-click anywhere on the ribbon, Customize Quick Access Toolbar, highlight the Developer tab and click on the Move up button to move it before the Formulas tab. Now press OK. The Developer tab moved exactly where you wanted it to be. You can try this out and arrange your Excel ribbon. Thanks for watching. 5. Using Keyboard to Access Ribbon: Hi everyone and welcome to this lesson. What I am going to demonstrate here is how you can use the keyboard shortcuts to access ribbon and run commands. So let's get started. Many times, keyboard shortcuts or a faster way to manipulate the ribbon without using the mouse. Our goal in this demo is to open the Insert Function dialog box. If you hold down the Alt key, you will see letters next to each of the commands. Try it out. Let's suppose that you want to activate the Formulas tab. As you can see, it's the letter m. So hold down the Alt key and press the m letter. Now, every feature of the Formulas tab has a letter or a double set of letters. For example, if you want to insert a function, you have to press the f Letter and the dialog box opens. If you want the letters to disappear, try the escape key. Don't forget that if you want to minimize the ribbon, you can press control plus F1. This is an overview of how to use the keyboard to access ribbon commands if you never use them, I highly recommend trying it. It will save you time in the long run. Thanks for watching. 6. The best techniques for inserting, deleting, hidin: All right, time to talk about the best techniques for inserting the leading hiding. And I'm hiding columns in rows. You can fill new data in Table and excels only by adding new roles or new columns. The easiest way to add a new row, for example, the role above row 12 is to point to rho 12 and right-click, then insert and add the new row inserted. Pretty straightforward, right? This technique is useful if we only have one data table, but in this worksheet, we have two tables. Notice that the new row was implemented in both data tables. We had to find another method to insert a new row only in table one. So the bottom line here is to be careful when you're using the answer roll method. Let's undo with control plus z. Select the specific data from the first table in row 12, from H1 to E 12, okay? Now right-click insert. And from this insert dialog box, select the option shift cells down at ok. Take a look. Now, the table with countries is intact. For the lovers of the keyboard, there is a shortcut, Control plus plus to activate, insert, and similar control plus minus to activate delete. Let's try them with control plus the insert dialog box opens. And with controlled minus the delete dialog box opens. That simple. Undo again. Sometimes we have to answer two or three new rows or columns. For example, before columns B and D, highlight these two columns with the Control key and then insert. We get a new column to the left of those two columns. To delete the new columns, we follow a similar way. Hold down control again, highlight them, right-click and delete. Now I want to show you a really quick, really efficient way to hide data. Usually I want to hide data if I don't want to print them, again, hold down the Control key, highlight the rows you want to hide, right-click and hide. To unhide a specific row. You highlight the rows above and below this right-click and unhide. Of course, if you have many hidden rows or columns, a nice trick is to click in the upper left corner, right-click and unhide. Thanks for watching. 7. Undo, Redo and Repeat: All right, time to talk about undo, redo, and repeat features in Excel. This is another one of our very basic one-star productivity tips, but one of the most important. Everyone can make mistakes during the use of Excel. The easiest way to undo a mistake is by clicking this button with the Quick Access Toolbar. This shortcut will allow multiple levels of undoing each time you use it, excel will step back one level. Here is an example. We taped an A1 cell, the phrase named customer. This is a mistake. We wanted to type only the word name in a one. So let's click on the Undo button and look that the word customer has been deleted. Usually I prefer another way to undo a mistake. It's the control plus z keystroke shortcut. As you already understood, I'm a big fan of keyboard shortcuts. I think it's much faster than the button, but control plus z has a drawback. You can only undo your last action and you can't go back multiple steps. Now let me give you a little bonus tip. But first, let's delete some data from this table. Let's delete, for example, B3, B6, B11, and d 14. Okay, click the drop down arrow to the right of the Undo button. Here are the last steps you did. You can go back many steps like this. Undo for actions that once, it's a quick, efficient way to undo multiple steps at once. In the same way, you could redo some of the actions either by pressing this button or using control plus y keystroke shortcut. See the result. The cell has been deleted. To repeat an action in Excel is very common and very clever and fast way is using the F4 key or control plus y shortcut to understand how it works, we'll do an example. You have this data table. You want to fill rows 145 with a yellow color. Later you decided to fill row eight with the same color. Highlight row eight and press F4 or control plus y. How cool is that? You repeated the same action without doing the same steps over and over. So here we have one of my favorite hacks. Thanks for watching. 8. 4 Ways To Protect Excel Workbooks, Worksheets And: Hey everyone, I hope you're fine. In this video tutorial, we will learn how to protect an entire workbook, sheets or cells. There are different methods and levels of protection. For example, you can protect all the contents of your workbook. You can allow others to open your workbook is 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. Here we have a workbook with a sheet called block. First of all, we will learn how to fully protect his workbook. Go to the File tab. Click the info selection. It's the first one on your left. Now click on the protect workbook button and it has six options. As you can see, for the best protection, you can encrypt the file with the password. So I will choose the second option, encrypt with a password. Whenever someone tries to open the document, Excel prompts them for a password first. So no one can open the Excel file unless they have the password and they can view the contents. Of course, some third-party tools or programs can crack your password, but it's very difficult, especially if your password is long with a combination of upper, lower characters, symbols and numbers, let's insert an easy password for our lesson. For example, 1234. Re-enter it and okay, great. Take a look at the protect workbook button and it turned to a yellow color. It means that protection is enabled. Now, save the file and let's try and open it. Entered the correct password, which is 1234 for our demo. And we'll allot. To cancel the protection. You can go to the File Info, protect a workbook button, click on encrypt with password, the lead the password. Okay, save and password protection has been deleted. The next tip is to learn how to allow others to open your workbook as read only. We can do that directly by clicking the save as option. Browse, choose a folder. And from the tools button at the bottom right corner, and click on general options. From this little dialog box, you can provide a password to open or to modify the file. If you want to recommend users to open the Excel file as a read only without protecting it. Don't enter a password and check read-only recommendation. Also, this feature does not encrypt your Excel file. Malicious users can edit the file and remove the password. To take away that security, you have to do the same steps and delete the password to prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets and renaming worksheets. You can protect the structure of your Excel workbook with a password. To do that, go to File Info. Protect workbook and protect workbook structure. As you can see, the password is optional. If you do not supply a password, any user can unprotected change the workbook. If you do enter a password, make sure that you choose a password that is easy to remember, right? Your passwords down and store them someplace safe. If you lose them, excel cannot recover them for you. Give a password. I'll leave it empty for now. Okay. As you can see, I can't add a new sheet or copy the current one. That's simple. Let's remove our protection and following the same steps and move on. Next, I'll show you how to protect a worksheet. But first let's copy this sheet because I want to worksheets one protected and one unprotected to make a copy of a worksheet, Right-click, move or copy and create a copy and okay, rename it to protect sheet and enter. Now go to Review tab from ribbon. Fine and click the protect cheat button and the dialog box is opened. You can optionally enter a password in the Password to unprotected sheet box. As you can see, all these options are selected by default. In the allow all users of this worksheet to list, select the element you want people to be able to change a, leave them as they are and click OK. selecting the protect sheet command makes it impossible to make further changes to the contents of any of the lot cells and out worksheet, except for those options that you specifically exempt in the allow all users of this worksheet to list box. You can click anywhere, but if you try and modify or delete a cell, for example, you'll see that the message that it says it's a protected sheet and to unperturbed, you will need a password, which I didn't put great unprotected sheets by clicking again to this button and click again. Now we're going to uncheck all of these and leave them empty. Press OK. Now I can't click anywhere. And in general, I can't do anything in my worksheet. These selections are very important if you want to send Excel templates to people and allow them to change specific formats of the Excel file. Usually, your intention in protecting a worksheet for an entire workbook is not to prevent all changes, but to prevent changes in certain areas of the worksheet. For example, in a budget worksheet, you may want to protect all cells that contain headings and formulas, but allow changes in all the cells where you enter a budgeted amounts. Click ones again and they protect Sheet button to disable the protection. A great way to allow specific ranges of your sheet unprotected. The notes column, for example, is to first highlight a range from D2D, D2D. In our example, click the button, allow users to edit ranges. Then click on new, give arrange title. For example, input here. The data that is allowed to edit. And finally put a password for people to modify this optional, of course. Okay, and okay, again, do not forget to protect the sheet. Now, it's very important. Let's do it from the protect cheat button. Check all the boxes. Click OK. As you can see in works, you can edit any of the cells of the worksheet except the cell ranges from D2 to D2D. All these protection methods work with shapes or charts as well. I hope that you will learn a lot of options to control the protection of your Excel files and minimize the mistakes of your coworkers are employees. Thanks for watching. 9. Navigate between workbooks quickly: Hello students and welcome to this new section of the Excel hacks course. During this lesson, I will demonstrate how you can navigate between workbooks quickly. So let's dive in. Right now we have a workbook opened called navigation. Let's create a new workbook from the file tab, a new and blank workbook. Great. Suppose you work in a company and have several workbook. It's open at the same time. So it's useful to know how to navigate between multiple workbooks or to copy data from one workbook to another. Or sometimes we need to see both workbook simultaneously. Let me show you first how to easily switch between open Excel workbooks. Go to View tab and click on the switch windows button. You can see a list with the open workbooks. So it's easy to switch between them. Right now, we have only to open workbooks, switched to navigation workbook. If you use this kind of switching between workbooks frequently, the best you can do is to add the button to the Quick Access Toolbar by right-clicking on it. That simple. Now keep in mind, like most things in Excel, there are many ways to accomplish the same thing. So a faster way that I prefer is with control plus Tab keyboard shortcuts. First, we will create two new workbooks and go to File New Blank Workbook. And we created a new workbook. Do the same steps one more time. Okay, lets now try the control plus Tab shortcut. Look how fast it is to move back and forth between open workbooks. Very useful, especially if you want to copy and paste Excel files. In alternative keyboard shortcut is control plus Shift plus tab. To do the same thing, there are several ways to switch between workbooks, and one of them is also control plus F6, fast and easy. You can choose whatever you want. Thanks for watching. 10. Applying Document Themes in a Workbook: A theme is a quick and easy way to give a professional in modern look to a Microsoft Office document. A document Diem is a set of formatting choices that include theme colors, a collection of theme fonts, including heading and body text fonts, and many theme effects, including lines and fill effects. This worksheet has the Office Theme enabled, the default theme with a white background and dark settled colors. To change the Excel default theme, you need to go to Page Layout tab and then click on themes. These are the built-in Excel theme choices with the live preview feature. Moved the mouse over a theme and see the results if you like it. Click two to apply to the theme of all the worksheets in the workbook. To use a document deem that is not listed. Click browse for themes to find it on your computer or a network location. Keep in mind that you cannot apply different themes. Do a different worksheet. Theme always applies to the entire workbook. Also, it's better to use a theme, then start filing data because the theme changes many things like font, page margins, et cetera. Finally, you can modify a theme and specify a set of theme colors, fonts, and affects. Thanks for watching. 11. Saving Excel as a read only Web Page file: I will demonstrate how you can save all or part of a workbook to a static webpage during this demo, usually if you share an Excel worksheet with someone, that person can freely edit the spreadsheet. In some cases, you may want to send someone a spreadsheet with data, but you will also want to ensure that he doesn't accidentally or intentionally edit it so that the numbers remain accurate. You can do this by making the spreadsheet read-only in Excel, or you can convert it from Excel to another format, such as PDF file or even a website. Similar options are available for other standard office software, including Microsoft Word. If you need to share information and a workbook with someone and be assured that the data remains intact. You can save your Excel workbook as a webpage file. An HTML renders in Excel workbook and a webpage file that can be opened with many browsers. We will save the browser workbook with this type of file. Go to File tab. Save as click browse and choose webpage. Select Desktop and OK. Find the file from the desktop and double-click to open. The file opens in your browser and it's read-only as you can see. So fast and secure way to send your previous Excel data. Thanks for watching. 12. Navigate between worksheets quickly: All right, let's talk about how to navigate between worksheets quickly. Most of the time we'll workbook has one or two worksheets, but some workbooks have many worksheets like this one. There are 12 worksheets, as you can see, one worksheet for each month. Take a look in the lower left corner and you'll see two arrows. These arrows help us navigate between the worksheets as you can see. But if you right-click on the panel, you will get a list of all worksheets. It looks like a table of contents. Now it's super easy to select the worksheet we want. For example, select July. Press OK. Pretty straightforward right? Now slide over the arrows without clicking them, you will see a prompt with control and left or right arrow. I can go very quickly to the first or last sheet. And with right-click, I can see all the sheets as we already learned. I will press control plus right arrow. And you'll notice that the Excel showed me the last sheet, which is December. Now while holding the Control key, I'll click the left arrow and Excel will move to the first sheet, which is January. How quick and easy was that? Now notice the three vertical dots. Double-click on these and I'll see that it expands the view. Now I can see all the months at once. A faster way to move to the next worksheet or move back is by using the control plus page up or control plus page down keyboard shortcuts. Let's try it. Control and page down. Move back and control plus page up, moved to the next sheet. Now to create a new worksheet, there are two ways that I prefer. Next to the last visible sheet, there is a circle with a plus in it. If you click on it, a new sheet appears. Excel created a new sheet next to the active sheet where we were, which was May. If you prefer to create a new sheet using a keyboard shortcut, you can press Shift plus F11. A new sheet is added to the left of sheet one appears. So if you're the type of user who prefers to use the keyboard than the second method is the one you want. Like most things in Excel, there are many ways to accomplish the same thing. Thanks for watching. 13. How to create a hyperlink to other worksheets: If you were a real internet surfer, You know firsthand about the bright signs of hyperlinks. Clicking on hyperlinks, you instantly get access to other information no matter where it is located. But do you know the benefits of spreadsheet hyperlinks in Excel workbooks. The time has come to discover them and start using this great Excel feature. You can put spreadsheet hyperlinks to fair use to create a table of contents for your workbook. Excel internal hyperlinks will help you quickly jumped from the workbooks necessary part without a hunting through multiple worksheets. This workbook has two worksheets, sales per company and total sales. The first worksheet has the amounts for each company and the second sheet has total sales. If we want to create a link to the first worksheet, I first click on the cell to link. So highlight cell a 21 and go to Insert Tab and click Hyperlink. Doing so displays the insert hyperlink dialogue box. Look how many options I have. I can link to existing files or even web pages by clicking existing file or web page option, I can click Place in the document. I can also create a new document to link to. Finally, I can mail to hyperlink, which allows people to click a link, open their mail program to send an e-mail address listed in the hyperlink. Select the option place in this document. Now I can select a place in the document. So I have two worksheets, sales per company and total sales. I'll click total sales, which is the name of the worksheet I want to link to. And I'll just leave the cell reference at a1, which is the top left corner of the worksheet data area. And I'll click okay. This al Qaeda becomes underlined and highlighted in blue. It means that the cell contains the hyperlink to check if the link works, just hover over the pointer of the underlying text and click to the unspecified location. If you want to change the hyperlink destination, you need to modify the cell reference or choose another sheet by right-clicking and choosing edit hyperlink from the pop-up menu. If I tried to click this link, I go to the second worksheet, which works fine. If you're an advanced Excel user, you could try a tricky method to create hyperlinks. And Excel, Excel has a function named hyperlink that you can use for creating links between spreadsheets in the workbook. In this lesson, I hope you saw the simplicity and effectiveness of using internal hyperlinks in a workbook. Just a few clicks to create, jump and discover the massive content of complex Excel documents. Thanks for watching. 14. How to prevent someone to unhide a worksheet: Hello Excel fans and welcome to another Excel hacks lesson. What I'm going to demonstrate here is how you can prevent someone to unhide a worksheet. So let's dive in. You can hide any worksheet in a workbook to remove it from view. Hiding a worksheet that contain sensitive data is very easy. In this workbook, There are two worksheets, hide one and hide to. Let's suppose you want to hide the first sheet. Simply right-click on this sheet and select Hide. Notice that it is removed from the tab bar. The problem is that this technique is not so secret and someone who was an experienced Excel user can right-click on any Tab and select the unhide option. It opened a small window with a list of the hidden sheets. I have only one hidden sheet, and this is height one highlighted and click OK. So the bottom line here is that this method is not so save. To unhide a worksheet with a secured method, you have to use Visual Basic. Don't be afraid there is no need for programming. As you can see, we will follow some simple and easy steps to accomplish this. First of all, press Alt plus F11 to open up Visual Basic Editor. Look at the project window, find the name of the workbook, and then look for the name of the sheet, which is Hyde one in our case. Click it and notice that it displays the properties in the Properties window. Find the visible property and click on it. A down arrow appears. Click and see the three property values. We will select the last one sheet, very hidden. Now press Alt plus F11 again to return to the Excel. The hide one sheet doesn't appear on the tab bar. Let's right-click and searched the unhide option. Look, it's colored gray, which means that we can't select it. Now how cool is that? Our job is done to unhide the sheet that we have to follow the reverse procedure. Go to Visual Basics editor by pressing old plus F11 from the Developer tab. Click on hide to Worksheet and set the property back to visible. Again, alt plus F11 and hide one sheet is back to its position. So we learn two different ways to hide a worksheet and easy one and a more advanced method. If you have any questions, I'll be more than happy to help you. Thank you for watching and I'll see you in the next Excel hacks lesson. Bye. 15. Copying and Moving Worksheets with various ways: Hi there, this is Andreas. And what I'm going to demonstrate here is how you can copy and move worksheets in various ways. So let's dive in. A copy of a worksheet can be done in various ways. If you have some data and simply want to copy them into an empty sheet, you can use keyboard shortcuts. For example, highlight the data from A1 to see ten. Press control plus c for copy. Go to a new sheet, click on cell A1 and press control v for paste. Now, there are some formatting problems with this method. Look, for example, the columns, they are narrower than on the first sheet. Another method is to convert the entire worksheet without the need to highlight the data. The technique is simple. Drag the sheet rightward and simultaneously hold down the Control key. We'll get the tiny plus sign inside the icon. Leave now the mouse and the control key and the sheet is copied without formatting problems. Of course, sometimes we want to copy or move an entire worksheet to a different workbook. Let's make a new workbook with control plus n. Go back to the first workbook and Right-click on the sheet you want to copy. Select the Move or Copy option. In the move or copy a dialog box due the following, check the box. Create a Copy, choose the book you created. Choose a move to the end. And done. It takes longer, but it's a method with a different approach. If axonal allows duplicating a sheet within the same workbook by dragging it. Why not use this method for copying machine to a different workbook? We just need to view both files at the same time. Here's how open the source and target workbooks on the View tab in the Windows group. Click View side-by-side. This will arrange the two workbooks horizontally in the same source workbook. Click the sheet tab you'd like to copy. Hold down the Control key, and drag the sheet to the target workbook. Incredible, isn't it? The next time you need to copy information from one Excel file to another. Forgettable copying and pasting and dragging the sheet. Thanks for watching. 16. Entering Data to Multiple Worksheets at a time: All right, the next if I want to cover is how to enter data into multiple worksheets at the same time. You can enter the same data into several Worksheets without retyping or copying and pasting the text into each one. For example, you want to put the same title text into all worksheets in your workbook. One way to do this is to type the text in one worksheet and then copy and paste the text into the other worksheets. If you have several worksheets, this can be very tedious. This technique is a timesaver if you want to have a similar structure for your worksheets. So let me give you an example of one of my favorite Excel hacks. I have opened a workbook with 12 worksheets, one for each month. Go to the first time sheet. We want to add two headers from c1 to D1, products and order date. First of all, you have to highlight all the sheets from January to December. To do that, hold down the Control key and click the worksheets tab like this. A faster way of you prefer is to highlight the first tab, then press Shift key and click on the last tab. All the months are highlighted. So now I start typing the text. Let's go to first to see one and type products. Then let's move to D1 cell and type Order Date. Well, I think it's better to do some formatting to the cells, like adjust the column width and add a color using the Format Painter feature. Okay, now it's time to move to different worksheets just to make sure the data have been enter into all months. It worked fine. Now, how cool is that? This technique can save you a lot of time. I want you to try it out so you will understand how this technique works. Thank you for watching, and I will see you in the next video tutorial. 17. How to print a 2 page worksheet on 1 page: If you have a worksheet that splits into two pages or more, you might want to reduce it to fit on one page. What I'm going to demonstrate here is how you can print a two-page worksheet on one page. So let's try it in this worksheet. The first step is to click on the File tab and click print. On the right of your screen, you can see a preview of your data. If you notice this little controls right here, you will see that this data table we'll print onto pages. We'll make some adjustments to the Page Layout tab. The easiest way to shrink the printout to fit on one page is to select fins screen on one page. It is the second option. Click on it. Take a look. All data fit on one page as you can see. Great. Now I'm going to show you another method from the Page Setup dialog box. Click on Page tab and from scaling section fit to option, set one page wide and one page tall. Now press okay. And you can see that it will print on one page instead of two. I demonstrated two methods to use. If you want to print a two-page worksheet on one page, if you have a workbook that includes several worksheets, each containing only a small amount of data. You may wonder if there's an easier way to print them multiple worksheets on a single sheet of paper. Usually you can do this if you choose the entire workbook option as I show you here. Of course, printing this way can lead to some tiny texts on the printout because the printer driver simply reduces each page to occupy a proportionate area of the printed page. If you need any questions, please let me know. Thanks for watching. 18. Hiding scrollbars in a worksheet: By default, a horizontal and vertical scroll bars displayed in an Excel workbook so that you can scroll through the data and it's worksheets. A user can concentrate on a worksheets data without moving around using the horizontal and vertical scroll bars. So it would be useful to hide these scroll bars and also the sheet tabs. During this demo, I will demonstrate how you can turn scroll bars on an off is needed. Let's get started. Go to the File tab and then click options. The Excel Options dialog box opens. Click advanced from the left side of this window. Now I'm just scrolling down until I find the display options for this workbook section. Look at these first three options. They are responsible for showing the scroll bars in your workbook. Uncheck these three checkboxes and press OK. The scroll bars and sheet tabs disappeared. Of course, if you want to scroll down, you can do it by pressing the down arrow key from your keyboard. You can also use control plus page up or control Plus Page Down to change worksheets. Finally, you can restore the scroll bars in sheet tabs by doing the same steps. Thank you. And if you have any questions, please let me know. 19. How to set the number of worksheets: If you remember from Excel 2007 or 2010, it came with three worksheets when you created a new workbook in Excel 2013 and later versions, there is only one worksheet and a new workbook. By default, new workbooks created in Excel contain one worksheet. If you typically use more than one worksheet, you can change the number of worksheets available by default in new workbooks with a simple setting, you can add worksheets easily using the plus icon to the right of the worksheets tab as you work. However, if you know you're going to want more than one worksheet in each new workbook you create will show you a setting that allows you to specify how many worksheets will be automatically created in new workbooks. How do we do that? Go to the File tab and then click options from the Excel Options dialog boxes, General page. Look at the when creating new workbooks section. Include this many sheets option. Change this number to whatever you want. For example, five and press OK. Now if I create a new workbook by pressing control plus N, that workbook contains five worksheets. This setting does not affect any existing Excel workbooks you open. It only affects the number of worksheets in new workbooks created after this setting is changed. Thanks for watching. 20. Learning different techniques for navigating within a given worksheet: All right, title and different techniques for navigating within a given worksheet. Like most things in Excel, there are many ways to accomplish the same thing. Although there are many keyboard shortcuts to navigate in a worksheet, we don't use them. Most of the time we use the mouse, which is a time waster. Take a look. This is super-simple. We see a sheet big enough with a lot of data. There are some tips and techniques for moving around the worksheet, perhaps faster than simply scrolling or using arrow keys. Try to press control plus n c0, the active cell moves the last lower right-hand corner of the active part of the worksheet. It's not strange to see no data. Sometimes at the end of a worksheet, maybe you had data there and later you cut and paste it somewhere else. By pressing control plus Home key, Excel takes us to the upper left corner. You can use to control plus arrow keys to navigate the ends of data easily. So let me use an example. The first step is to click on any cell in a block of data. Then press control plus down arrow. You will be immediately taken to the bottom cell in that specific column. If you want to include everything until the last cell containing data, click on the first cell which is A1, and then use a control plus Shift plus down arrow key. All the data from column a will have been selected. Usually I click on the first cell and then I press control shift down arrow and control plus Shift plus right arrow. Notice that all the active cells are being highlighted. Try this out. Finally, to useful in quick keyboard shortcuts are control plus spacebar and Shift plus spacebar. What does this really do for you? Click on a random cell inside the table. Now press control plus spacebar. It selects the whole column two where the active cell is located. Okay, now press Shift plus spacebar. It selects rows instead of columns. Pretty straightforward, right? I hope you understood how these simple tips can save you a huge amount of time. Thanks for watching, and I'll see you in the next lesson. 21. How to create and save custom views of a worksheet: When working on an Excel worksheet, you may find yourself sitting up different display settings and other times such a zoom level or window position in size. The custom views feature allows you to set up and save different views to switch among them quickly. For example, you may want to set up a temporary zoom in the worksheet to see more details are high parts of the Excel interface to maximize your workspace. In addition to hiding the Ribbon, you can set up a different view for each worksheet and save each view. We already opened the custom views workbook. As you can see, there's a data list with three columns, name, phone, and address. We want to filter that list so it will display only the persons whose name starts with the letter a. Click any cell within a list, go to the Home tab, click Sort and Filter, then filter. Click on a name column filter, arrow text filters and begins with N. In the Criteria box type in a letter. And okay, my list is ready and filtered. Let's zoom in on the View tab and zoom to selection. Too much Zoom. Let's decrease it. Okay, now I'm ready to create a custom view. Before setting up any custom views, it's a good idea to save the current normal view is custom view, so you can quickly revert to it from the View tab and workbook views section, click custom views. Click the Add button, type in the name of the view. I have filters settings in the sheets, so leave both of the boxes checked. And okay, of course, the next step is to reset my worksheet as it was previously. So set the zoom level back to normal and remove the filter. Now let's try our custom view. Go back to the View tab and click custom views. Select this and click the show. The view we made is ready. Of course, you can create as many custom views as you want. So the custom view is a great feature, especially for presentations. Custom views are only available in the workbook in which you create them, and they are saved as part of the workbook. There also worksheets Pacific, meaning that a custom view only applies to the act of worksheet when you created the custom view. When you choose a custom view for the show of the workbook that is not currently active, excel activates the worksheet and applies the view. So you must create custom views for each worksheet in each workbook separately. And make sure that you save your workbook with your custom views. 22. Solving the problem with Leading Zero: Have you ever tried to enter some data like 00, 1-2-3 into Excel, you'll probably quickly notice that Excel will automatically remove the leading zeros from any number. This can be really annoying if you want those leading zeros in your data and you don't know how to make Excel keep them. Most of us have had difficulties when we tried to enter a number that begins with a leading 0, like tax registration number or zip codes. So we want to find a solution that leaves with leading the 0 in tact. The quickest way to make Excel understand that I'm entering a number with a leading 0 is to add a single apostrophe before the value, or as you call it a single quote. Let's try it in cell E2. Tape a single quote, 012345 and press enter. It worked because accelerates the value as a txt. Notice that an error flag, a small green triangle, appeared in the cells upper left corner. Let's click on this sign and it says that a number is stored as a text. If we try to convert the value to a number, the leading 0 disappears. Of course, the error flag is not a problem and we can ignore it. If I have to enter many numbers with a leading 0, then I prefer first to highlight the cells that value will be entered. And from Number Format Cells option, click special, and then select the zip code. Now whatever number is entered, the leading 0 remains. Keep in mind that there are more ways to solve the leading 0 problems like formatting the numbers as text values. Add custom formatting to format numbers with leading zeros or use functions like the text function and write function. As you know, in Excel, there are many ways to accomplish the same thing. Thanks for watching. 23. How to add text to a number: Alright, the next if I want to cover is how to add text to a number. Sometimes you may need to add some specific tags to the beginning or end of all cells and a selection. If you added the text that each cell manually one-by-one, it must be time-consuming. Or there are easier ways. Yes, there are many easy tricks to try and add some text at the beginning or end of all cells at a selection. Look at this worksheet. It contains a data table with values. Some of them result from a function or from a formula. So it would be useful sometimes to add an explanatory text beside the value. Look at cell N2. It's the result of this function. We want to add a text. How can we do that? The first step is to highlight the cells that we want to add to the text. Then go to Home tab, click the down arrow from the bottom of the number selection and click More Number Formats. A dialog box appears, click Custom from category number and select 0 because we are interested in numbers. 0 is a digit place holder that displays insignificant zeros. Here we can add to our text to do that type of double quote and then space. Now type in the text, you Demi, and again with a double quote, click OK to apply the text. Look at the result. Excel Added Udemy word to the end of each cell. Makes sense, right? An easy trick to add text any number. If you want to learn the basics from the Excel number format, I've included a PDF file, a tutorial that provides detailed guidance to create custom formatting. Thanks for watching. 24. How to enter a Line Break into one of your cells data: Hello students. In his video tutorial, I'll show you how to make an entire line break into one of your cells data. The line break is known in word and is used very often. A line break and Excel can add the current line and start a new line in the same cell. You may want to insert a line break in Excel when you have multiple parts of a text string that you don't want to show in separate lines. An excellent example of this could be when you have an address and you want to show each part of the address in a separate line. There are a couple of ways to insert a line break in Excel. Inserting a line break using keyboard shortcut, inserting line breaks using formulas using finer replace the control plus j trick. This lesson will show you the first third method to answer a line break. I've already opened a workbook was an address is in the first four cells, A1 to A4. If you need to add a couple of line breaks, you can do this manually by using a keyboard shortcut. The first step is to double-click on the cell in which you want to insert the line break or press F2. Let's do it in a one. This will get you into the Edit mode and the cell. Now place the cursor where you want to do the line break, for example, here. Okay? Now use the keyboard shortcut. All plus enter, hold the ALT key and then press enter. This method inserts a line break right where you place the cursor. Now you can continue to write the cell and whatever you type will be placed in the next line. The keyboard shortcut is a quick way to add a line break if you only have to do this for a few cells. But do you have to do this with many cells? You can use other methods. Suppose you have a data set and you want to get a line break wherever there is a common the address. If you're going to do an insert a line break wherever there is a common and the address. You can do that using the find and replace dialog box. First of all, select all the cells you want to replace the comma with a line break. I'll highlight these cells from the Home tab, click on the find and select, and then click on the box. In the finer replays dialog box, enter a comma in the fine what field? Please the cursor in the replays field, and then use the keyboard shortcut Control plus j. Hold the control key and then plus j. This will insert the line feed into the field. You may be able to see a blinking done in the field after you use control plus J, Click on Replace all. Make sure Wrap Text is enabled in the above steps, remove the comma and replace it with the line feed. Note that if you use the keyboard shortcut Control J twice, this will insert the line feed carriage return two times, and you will have a gap of two lines in between sentences. You can also use the same steps to remove all the line breaks with a comma or any other characters, just reversed it, find what and replace with entries. This is a super cool trick. I hope you found these methods useful. If you have any questions, please let me know. Thanks for watching. 25. Using Wrap Text and Shrink Cell Tools to have the size of the cells right: The next tip I want to cover is how to use the Wrap Text and shrink cells tools when the data input in this cell is too large to fit in. One of the following two things happens. If columns to the right or empty along texturing extends over the cell borders into those columns. If an adjacent cell to the right contains any data, that text string is cut off at the cell border. In this worksheet, we have some cells with text inside, click on cell A1. The content doesn't show up as we want because the text is cut off. Of course I don't want to change the cell boundaries. A quick way is the Wrap Text excel Wrap Text feature can help you fully display a longer text and a cell without overflowing into other cells. Wrapping text means showing the cell contents on multiple lines rather than one long line. This will allow you to avoid the truncated column effect, make the text easier to read and a better fit printing. Lets try it. Highlights cell A1 and go to the Home tab and click wrap text and the alignment group, all the cells contents usually appear and the cells width remains unchanged, but the cell is now taller. If we don't want to change the cell, we will prefer the falling technique undue with control plus Z. And now click down arrow from the Alignments section. Look at this option. Shrink to fit. Shrink to fit will automatically reduce the font size until the text fits in a cell. Click this box and OK. Notice that reduced the type of the size of the data inside the cell. The cell boundaries didn't change either in a vertical or horizontal direction. Now look at the cell 8-10. It contains more than 200 characters. I imported it from word. Rather than having all this in cell 8-10, we'd like to wrap it around and go down this way. With all the data highlighted, go to fill justify, and we will get a prompt. The text will extend below the range. Click okay. The text is usually showing fill justify is a lifesaver option, a beneficial technique, especially if we want to import data from another source like Word. Thanks for watching. 26. Creating a Conditional Format based on a value: Hello Students. In this lesson we will explore the fascinating world of Excel conditional formatting. So we will dwell on how to use the Excel formulas to format individual cells and entire rows based on the values you specify or based on another cell's 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, it will go into fill a specific color. So you want to create a Conditional Formatting Rule. In general, if you want to apply conditional formatting bays and another cell or format the entire row based on these single cells value, then you'll need to use Excel formulas. The first step is to select the cells B2 to M5, in which the rule will apply. You can choose one column as several months or an entire table if you want to use your conditional format to rose as a bonus tip, if you plan to add more data in the future and you want the conditional formatting rule to get applied to new entries automatically, you can either convert a range of cells to a table, Insert Tab table, or select some empty rows below your data, say 100 blank rows from the Home tab, Styles section, and click Conditional Formatting and then a new rule, a new dialog box opens. Find the option. Use a formula to determine which cells to format. Now you're ready to edit the rule description. But before that, click the Format button, choose Fill and now select the fill color for any cell that exceeds the value in cell B7. I will choose a green color. Switch between font, border and fill tabs and play with different options such as font-style, pattern, color, and fill effects to set up the format that best works for you. If the standard palette does not suffice, click More Colors and choose any RGB or HSL color to your liking. When done, click the OK button. Click on this box and type in equal B2 greater equal to B7 with absolute reference. Notice that absolute reference is critical because they don't want the cell reference to change regardless of where I copy that reference, the formula is created and I click OK. Look at the cells that exceeded the B7 value, they're all filled with green color. Also, I want to say that when I change the value in cell B7, the format is updated. This is another one of the most useful formatting tips and they use it very often. Thanks for watching. 27. Blocking a user to interact outside a specific range: All right, time to talk about how to block a user to interact outside a specific range. In 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 do so. We already learned how to protect an entire workbook, sheets or cells. But this is a simple method using only a specific option from the Visual Basic Editor window. Don't worry, no programming is required. Now how can we do this? In this workbook called blocking and dot XLS x, I have a worksheet with a table inside the cell range starts from A1 and ends to C2H3Cl. For our example, I want to block the area outside cell range from A1 to be ten. So if a user tries to edit the cell C ten, for example, he couldn't do it. Only cells from a1 to be ten can be edited. For this technique, we will use property through the Visual Basic Editor called scroll area. Press Alt plus F11. To open the Visual Basics editor, highlight this worksheet from blocking dot XLS x workbook and the properties, the panel finds these scroll area box, click here and type in the range that you want to remain unblocked and editable. So type A1 to be ten. Click somewhere else and you'll notice that Excel added a dollar signs in front of the cells, so it converts them to an absolute reference. This is automatic so you don't have to do anything. Nice. Now press all plus F11 to go back to excel. Tried to click in the cell outside the specific range. You can't. Of course, if you wanted to protect the area outside the table, then you would have to take inside the scroll area box A1 to see 20. This is a very useful tip, especially if you work in a company and share the same workbook with your coworkers. So we manage to block a user from interacting outside a specific range. Thanks for watching. 28. How to hide the Status Bar: Alright, the next step I want to cover is how to hide the Status Bar in Excel workbook. If you prefer a less cluttered look of Excel, it is useful to hide various parts to make more room for displaying your data. The Status Bar in Excel can be quite helpful. By default, the status bar at the bottom of the window shows the average count and some of the selected cells. So it would be useful to hide the status bar at the bottom of the Excel window. This change, however, requires a small Visual Basics code. Don't be afraid. It's only a short statement. First of all, press Alt plus F11 to open the Visual Basic Editor. The next step is to press control plus g to display the Immediate Window. The VBA Immediate Window is an excellent tool that allows you to get quick answers about your Excel files and quickly execute code. It is built into the Visual Basic Editor and has many different uses to help writing macros, debugging code, and displaying your code results. Start typing with the following statement inside the window. Application dot display status bar equal false. Press Alt plus F11 again and look at the results. Notice that the Status Bar has been removed from all open workbook Windows. Of course, you can display the status bar again simply by replacing the statement with the following application dot display status bar equal true. So very efficient and fast way to hide or unhide the Excel Status Bar. Thanks for watching. 29. Changing values without formulas: Alright, for this tip, I want to show you how to change values without formulas. Now this sends that it should be pretty easy to do, but it can be challenging without using the right tools. So in this case we're going to use the Paste Special options. So let's dive in. 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 to multiply them by a percentage to calculate a discount or an increment. These tasks can be done without writing formulas. So why already opened this workbook with some data inside? Let's say for example, that we want to add number ten in each number to this data list. Cell B2 will be 40, C2 will be 30, and so on. How do we do that? The first step is to go to an empty cell, for example, a ten and write ten, which is the number that you want to add and enter. Now copy that cell, select the data that we want to change, which are the number from B2 cell to E6. And press control alt V. This shortcut will open and paste a special dialog box. Of course, you could click this button to open the paste special feature. As you know, there are many ways to accomplish the same thing in Excel, but I'd like to shortcut keys. Is there faster ways to do tasks in Excel? Choose, Add, and okay, look at the numbers now. They all increase by ten. Well done, delete ten, We don't need it anymore. Let's do another example. Now we want to increase all the numbers by 20%. Go again to an empty cell, for example, HL7, and take this and DSL 1.2 and copy it. Select the data you want to change again. And from the Paste button, select Paste Special, double-click on multiply from the operations section, press OK, and the values were changed. This is an excellent way if you wanna do some mathematical operations without using functions. Thank you for watching. 30. How to highlight formula cells using Conditional Formatting: The next Excel hack I want to cover is how to highlight formula cells using conditional formatting. So let's dive in. Have you ever had a worksheet with many formulas and wanted to identify them quickly. I bet you have. Look at this sheet. Some formulas spread all over the worksheet. I use two different techniques to identify these formulas. The first is from the Home tab. Look at the find and select button on the right of your ribbon and click on it. There is an option called Formulas. Click on it and see that all cells with formulas are highlighted. This technique is fast but not reliable because if I want to rewrite a new formula, the cell is not highlighted. A better method is by using conditional formatting. Here are the steps. First select the entire worksheet with control plus a or click on the upper left corner above row one. Then press the Conditional Formatting button from the Home tab. Let's create a new rule. Since the last option, Use a formula to determine which shell to format. We're going to use a new function in Excel 2016 called is formula. Is Formula function checks whether there is a reference to a cell that contains a formula and returns true or false. So taping this box equal is formula left parenthesis, right parenthesis. Because we highlighted all the cells, A1 is the reference for all of the cells. Click on the Format button and choose the red colour for filling. Okay? And okay, again, all cells that contain the formulas or red. If I tried to create a new formula. Now the cell is filled with this red color. The second technique is more dynamic and better than the first, but the choice is always yours. I only give you the tools. Thanks for watching. 31. How to color Rows using Conditional Formatting: During this demo, I will demonstrate how you can use color rows using conditional formatting. Adding a color background in our data is sometimes essential for a user to read the information more accessibly. The fastest way to add a color style to the data is by converting them into a table. And the quickest way to transform data into a table is by pressing control plus T keyboard shortcut. But for now, we want to learn a different way using conditional formatting with control plus z. Let's undo this first click in cell A1 and highlight all the data until b 20. We want to have a specific color for alternating rows. This method is also called alternating row shading. From the Home tab, press the Conditional Formatting button and let's set up a new rule. The option that we are interested is in the last, Use a formula to determine which cells to format. Now in the rule description box will start typing the formula. Let us think for a while. If we use the function MOD, it'll allow us to calculate the remainder in a formula. For example, if I take any number and divide it by two, will have a reminder. The remainder of the MOD of two is 0, the MOD of three is one, the MOD of four is 0, et cetera. So start typing in equal MOD left parenthesis, row, open, close parenthesis, comma two, close parenthesis equal 0. This functional formatting formula uses the row function which returns the row number and the MOD function which returns the remainder of its first argument id divided by its second argument. Now press the Format button, then choose a color. Let's pick the green one. Okay? And okay, again, look at the result. The alternate row shading is complete and it major spreadsheets more comfortable to read. Now how cool is that for alternate shading of columns used a column function instead of rho function. I understand that this technique is not easy to apply, but the important thing is to see the capabilities Excel has using conditional formatting rules. Thanks for watching. 32. How to Format Numbers using Keystroke Shortcuts: All right, time to talk about how to format numbers using keystroke shortcut. Keystrokes, shortcuts are not very popular for the majority of Excel users, perhaps because we are accustomed to using the mouse or because it's difficult to remember so many shortcuts. But the reality is a keystroke shortcuts can help us do the work with excel much faster, and some of them are real gems. There are five keystroke shortcuts associated with the pneumatic formats I often use. They all use the control and shift key and then the appropriate key. I take them in sand is table so that you can learn and use them. Let's try them starting from cell F3, press Control plus Shift plus one and see the result. It converted to a number. Continue with cell F7 and press control plus Shift plus two. It formatted the time to a 12-hour format. Now for the date, there is the shortcut Control plus Shift plus three. Try it. Most of the data I use this keystroke shortcut to format a currency. And finally, the control plus Shift plus five shortcuts is useful if you want to convert a number to a percent. Thanks for watching. 33. How to modify or create a cell style: Styles are a Microsoft Word feature, right? Well, you might be surprised to learn that Microsoft Excel uses styles too. Although the data's nature doesn't require the same kind of robust options. Excel styles are more comfortable to use than words. If you're not using them because you think they're complicated, you might want to reconsider. Named styles are a set of predefined formatting options to a cell or range. The style can change the look of a number, the alignment, the background color, the borders, or the font. The good thing is that we can edit the style and change whatever we want. For example, the font size and all cells with a particular style shift automatically. You can find many predefined styles from the Home tab in Styles group to apply a style, The first step is to highlight the cells. You can do this by pressing the control plus shift down arrow, then control plus Shift plus right arrow, and then click the style you want. The preview helps you choose the style you prefer. Let's pick this light blue. Now to change an existing style, activate the style gallery. Right-click on the style you want to modify and choose modified from the shortcut menu. The style dialog box opens with the current settings for the normal style, click the Format button, and then the Font tab, and choose the font and size you want is the default. For this example. I'll select the bookend T-SQL font, and 14 as a font size. Press. Okay? And okay, again, the format of the table has been changed. You can also create your styles with your formatting options to create a new style for select the cell, for example a1 and apply the formatting you like. Yellow as a fill color and red text. Then activate the style gallery and choose new cells style. The style dialog box opens, type a new style name in the style name Fox, for example, my style and leave all checkbox is checked. Click OK and close the dialog box. Your new style is now available in the style gallery. Notice that custom styles are available only in the workbook in which they are created. If you want the style you made to be applied to all workbooks, then you have to use the merge styles option from the style gallery. The requirement is both workbooks must be open. The one that contains the new style in the workbook you want to merge styles into, I hope you understood how to modify and create your own cells style. I want to thank you for watching, and I'll see you in the next Excel hacks lesson. 34. Duplicate records and how to get rid of these: One will working with a large Excel worksheet or consolidating several small spreadsheets into a bigger one, you may find many duplicate rows in it. What I'm going to demonstrate here is how you can get rid of duplicate records. So let's dive in. Look carefully at the date of this worksheet. There are some duplicate records like Martin P. Wolf and rows 917, and like Kelly be villas in rows 313, There are many records in the sheet, so we have to find a better way to identify duplicate records and get rid of them. You can use conditional formatting to identify duplicate values and arranged quickly. Let's get started. The first step is to highlight a specific column that contains duplicate records, like column a, you could highlight all of the columns, of course, go to the Home tab, click on the Conditional Formatting button and select the first choice, Highlight Cells Rules. There are some options here. We're interested in the last option, duplicate values. The result. Excel highlighted all the duplicate cells immediately. Pretty straightforward, right? Change the color if you want and press OK. Now your job is to get rid of these duplicate rows quickly. Click anywhere within the data, go to the Data tab and select Remove Duplicates. If you don't want to check duplicates and every column, you can do this by unchecking the columns you want. Excel tells us that several duplicate values are found and removed. Our job is done. So you learned a quick, really efficient way to get rid of duplicate records. Thanks for watching. 35. Creating Bulleted List: A bulleted list is used in word most of the time. Microsoft Excel is primarily about numbers, but it was also used to work with text data such as to-do list, bulletin boards, workflows and the like. In this case, presenting information in the right way is really important. And the best you can do is to make your lists or steps easier to read is to use bullet points. The bad news is that Excel does not provide a built-in feature for bulleted lists like most word predecessors, including Microsoft Word. But that doesn't mean there's no way to insert bullet points in Excel. In fact, there are many different ways to insert bill characters in Excel. So let's dive in. The quickest way to put a bullet symbol into a cell is by using keyboard shortcuts. Go to an empty cell by pressing alt and typing 0149 from your numeric keypad. You can quickly generate a solid bullet character. If you don't have a numeric keypad, you can press the function fn key and type numbers using the standard keys. Press space and type whatever you want and enter. You can do the same with the next cell, et cetera. If you want to insert other symbols, you can go to the answered tab and click on symbols. Now highlight this symbol and press OK. Finally, if you're going to learn more Alton numeric keypad combinations, you can check the PDF file I created. You can find it from the Resources tab in case you already created a bulleted list in Microsoft Word or in another word processor program, you can quickly transfer it into Excel from there, simply select your bulleted list in Word and for us control plus c to copy and then insert the entire list into a one cell, double-click the cell and press control plus V0. In situations in which you want to insert bullet points into multiple cells at once, the C-H-A-R function may prove helpful. It can return a specific character based on the character set used by your computer. On Windows, the character code for a filled round bullet is 149. So the formula goes as follows. Equal CHR, open parenthesis, 149, close parenthesis. These are the methods I use to answer bullet points in Excel. If someone knows a better technique, please do share in my Q and a Udemy section. Thanks for watching. 36. Format Painter Tips: All right, time to talk about Format Painter tips. After you've put a lot of effort into calculating a worksheet, you would usually want to add some finishing touches to make it look really nice and presentable. Whether you are creating a report for your head office or a building, a summary worksheet for the board of directors, proper formatting makes a central data standout and convey the relevant information more effectively. Fortunately, Microsoft Excel has an amazing and simple way to copy formatting. Often overlooked or underestimated. As you've probably guessed. I'm talking about the Excel Format Painter that makes it really easy to take one cells format and apply it to another. The Format Painter Paint Brush icon is located in the Clipboard group. With format painter, you can copy cell formatting including Number Formats, borders. So merging Conditional Formatting and paste them over another range. Let's try an example. Highlights cell A1, click Format Painter, and select the A3. Sell. Some tips for using the Format Painter. If you select a range of cells, you can paint another range by simply clicking a cell. The format is copied into that range that's the same size as the original selection. Select the range from A1 to B1. Press the Format Painter button and click on the A12 cell. Look at the results. If you double-click the Format Painter button, Excel remains and Format Painter mode until you cancel with escape or the button itself. This enables you to copy the format to multiple ranges of cells. If you want to remove all formatting from arrange, select an unformatted cell, click the Format Painter button, and drag it over the range. If you want to copy an object's format, just select the object. In our case, this star. Click the Format Painter button and click another object. Nice job. There are so many variations in different techniques to use the Format Painter tool. Thanks for watching. 37. How to insert a Watermark: 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. While Excel does not have a dedicated feature D insert a watermark in Excel, it can be easily achieved using the header and footer sections and Excel. This tutorial will show you how to insert a watermark and Excel easily and how to reposition and re-size, remove and already inserted watermark. The first step is to go from the ribbon, go to the View tab, and then from the workbooks news section, click the Page Layout button to enter the page layout view. Take a look at the header of the page. Click the headers center section, and a new tab is already opened, the Design tab. Now from the header and footer elements section, press the picture button. The answered Picture dialog box appears. The next step is to locate and select the image you want to insert. As an example, I'll type Excel into the box and enter. I'll choose any picture from his collection. Click the Insert button. Now you can click outside the header to see your image. That's it. You've just added the watermark to remove the watermark and Excel just remove the code. If you need to adjust the image, you can click the headers as center position and choose a header and footer tools, header and footer elements and format picture. From this Format Picture dialog box, you can adjust the image. Lets quickly change the height and width of the image. Press OK. When you have inserted the watermark, Excel remains in Page Layout view if you like. And you can go to the normal view by going to the View tab and then press normal view. You can take anything in a cell to test it. And from the final tab, choose Print, See how the watermark appears. This is how you can easily insert and work with a watermark and Excel. Thanks for watching. 38. Making your comments stand out: Commenting cells is a favorite feature in Excel. You don't have to be an expert to need them or to use them. As is comments are a great tool, but you can do more. By default, comments are displayed as a rectangular box filled with a light yellow color. The change the look of the cells comments, we need to add two commandments into the Quick Access Toolbar. So right-click the Quick Access Toolbar. Choose all commands and add the buttons. Change shape, and Picture Fill. Now to change the formatting of a comment first, you have to highlight the comment. Then from the Review tab, click Edit comment highlighted, and then press control plus one displaying the format comment dialogue box. Let's change the formatting like font, font size, Fill Color, and comments eyes. Press OK. Take a look at how the comment changed. If we want to change the shape of a comment, first, the chain Shape button from the Quick Access Toolbar and choose a new shape from the shape calorie. Great, a nice feature to add an image to the comment. Let's learn how we can do that. Highlight the cell with the comment from the Review tab. Click Edit comment. Click the comment is elected as a shape and then click the picture fill icon with the Quick Access Toolbar. Excel displays, insert pictures, dialog box. Now choose an image like this one and we are ready to go. Thanks for watching. 39. Creating a Custom List: When we work in Excel, One of our goals is to do our job fast and easy. A timesaver feature is a custom list. If you use a list frequently and you don't want to waste time typing them, you can create a custom list. A custom list in Excel is convenient to fill a large rent of cells within your list. It could be a list of your team members at work, countries, regions, phone numbers, or customers. The main goal of the Custom List is to remove repetitive work and manual errors in the input. For example, here you have a list of five countries. If you have these values already in your workbook and you want to extend the series by repeating exactly those values in precisely that order. You can add them to a customer list database. Let's define the steps. First of all, select the cells. Click the File tab, then options. From advanced options scroll down almost to the bottom of the Advanced tab where you can click the Edit customs List button. Here you can see that excel comes with four already defined lists. So if you want to assign the list of the countries to a new list, click Import and we're ready to go. Click okay, and okay again, to make sure that the list works, go to this empty cell type Argentina and drag the fill handle. It's not necessary to start from the first value of our list. Also keep in mind that if you send the workbook to a friend, your friend will create the list. So the list is becoming part of your Excel settings. Let's see a case study. Go to Workbook orders. You see some orders and the customers kind of trait. You want to be able to sort by Greece. If you click any country's column cell and sort age to Z from sort and filter button. The first countries, Argentina. If we try to start from z to a, I can't help either sort by Greece. So if we want to sort based on the custom that we created, first of all, you want to import your list as we did before. Highlight these four countries. Click on the File tab Options and from the Advanced tab, find and press the edit customs List button. Press Import. Okay, and okay again, click from the Home tab sort and filter button and Custom Sort button, sort by country. And from order, choose a custom list and highlight this. Okay? And okay, again, look at the data table. The country grace appears first because it was early on our list. So another idea of how you can use the Custom List feature. 40. The Paste Special feature: Copy pasting in Excel is easy. I believe everyone knows the shortcut to copy a cell Control C and paste it Control plus V. But did you know that apart from pasting a whole cell, you can paste only a specific attributes such as value, formula, formatting, or comment. Well, that's where the Paste Special comes in. Excel, Paste Special makes the pasting operation smoother by letting you choose which formulas, source or destination to keep or by stripping all formatting and just pasting the values are formulas. There are two basic ways to see the Paste Special options. Look at this range with values, highlight all of them from A1 to be 20, and click on the Copy button. If you right-click to an empty cell, you'll get a lot of Paste Special options. Select for example, transpose. Transposes option can switch rows to columns or columns to rows. In our example, the two columns of change to rows undue with control plus z. Now if you want to open the Paste Special dialog box, you can do it using a keyboard shortcut, which is also control plus alt plus v. You see that it brings up the Paste Special dialog box much faster. Now I want to show you a real life example that I use. In column B. I have sales from various companies. My boss told me to increase all values by 10%. The first step I have to do is to go to an empty cell, for example, d1 and enter 1.10. I will multiply the values by this number, which increases by 10%. Okay, let's copy that cell. The next step is to highlight that range to be transformed. Press Control plus alt plus V0 to open the Paste Special dialog box, let's click them, multiply option, and press OK. You can see that the values in this range are multiplied by the copied value. Similarly, you can use the four basic math operations. Add, subtract, multiply, and divide. This is how you use paste special feature in Excel and leverage this feature in your worksheet. Thanks for watching. 41. Using Fill Handle to enter Sequences of Values: Fill handle is a feature in Excel that enables you to autocomplete a list in a row slash column by dragging it using your mouse. A basic understanding of the fill handle and Excel can save you some time and make you more productive. Here we have a worksheet with two columns, Product ID and production date. Let's go to the a2 cell. Let's first understand what the fill handle in Excel is. You can hover the mouse over the bottom right edge of the selection, where you can see a plus icon appear and the mouse pointer changes it to into a black cross. If I hold down with the left mouse button and drag Excel extends the series, you can see that it repeats the number one. So if I want to repeat a value, I have to follow this technique undue with control plus z. Now if I move the mouse pointer over the fill handle and hold down the Control key, the black Cross changes and there's a little black cross at the top right of it like an exponent. Try and drag it down to see the result. The data series extends 234 and so on. So if you want to extend the series, hold down the Control key and drag undo again. Let's try to use a fill handle with dates. Go to cell B2 and hold down the left mouse and drag. You can see that it goes up by a day each time I move down the cell. Now hold down the right key mouse and notice the autofill options button. Look how many different options I have. Copies, cells, fill series, fill, formatting, filled days, fill weekdays, fill months, and fill years, try phil months and see the result now. So you can see that the autofill option button is a potent tool. Don't worry if the fill handle is not working in your Excel workbooks to enable fill handle on Excel from the File tab Options, select advanced from the left pane of the dialog box. Ensure that you have the enabled fill handle and the cell drag and drop option checked in the editing options section. Thanks for watching. 42. How to use FlashFill to fill values in a range rapidly: Flash fill is a powerful new feature of Excel 2013 that can help you when you need to enter a lot of repetitive information. Keep in mind that flash fill is only available in Excel 2013 and later versions. Flash fill automatically fills your data when it senses a pattern. For example, you can use flash fill to separate first and last names from a single column or combined first and last names from two different columns. Flash feel recognized there's a pattern in your data and works best when your data has some consistency. For example, in this worksheet, we have two columns, one with the customer name and one with a customer ID. Notice that in the first column, I take in the first and then the last name of each customer. So we cannot sort our list by the last name. In previous versions of Excel, we would convert text to come from this button from the Data tab in Excel 2013. In later versions, the flash fill feature is much better and faster. Take a look. This is super simple. Go to an empty column, for example, column C and click on cell C2. Type in Edwards, which is the last name, and then Baker, which is the first name, and enter. Excel moved to cell C3 for the next name, I will type Howell Wang. Look at that. Excel recognizes the pattern and all last and first names are being displayed in the background and gray color. Now if I press Enter Excel will fill those values to S3 to the end using the flash fill feature. How quick and easy was that? Flash fills and amazing time saving feature. And you can use it as often as you can. If you have questions about this useful productivity tip, please let me know. By 43. How to select Data using various Keystroke Shortcuts: Who doesn't love a keyboard shortcut to help make things faster and easier. Keystroke shortcuts are way quicker when we select Data in Excel. If you ever find yourself scrolling down thousands of rows with a mouse, the nice short cuts will save you time. So let's get started. When we want to select the entire worksheet. We can also do this from the keyboard by pressing control plus a twice. Great. The simplest way to select a range is to press and hold Shift, and then use the arrow keys to highlight the cells. Now select the entire block of cells, move the cell pointer anywhere within the range and press control plus a, another way to press control, shift down arrow, and then control plus Shift plus right arrow. But first you must highlight the first cell A1. Now we can select the entire column by merely pressing Control plus spacebar. Good. Similarly, we can simply press Shift plus spacebar to select the entire row. When we need to make multiple selections. For example, a selection that includes a non-adjacent ranges. There's a really nice trick. First of all, select the cell by pressing the Shift key and click this cell. A specific range is highlighted. If you want to choose the second range, there's a lovely trick after the first election, press Shift plus F8. Now we can select another range without cancelling the previous range sludge in. Repeat this action as many times as you want. When you're finished. Press Shift plus F eight again to return to normal selecting mode. Now how cool is that? So many keystroke shortcuts to choose from and many ways to do your job faster. Thanks for watching. 44. How to set a specific print area: There will be many times you need to print out Excel spreadsheets at the office or home office. Often you really don't need to print out the entire report which waste paper and can time. Excel worksheets have many data like logo images, shapes, many data tables, et cetera. If we want to bring out a specific area of our sheet, we have to define a Print Area. The steps are simple. The first step is to select the cells we want to print out. So I'll select the cells from A1 to see 20. And then I'll go to Page Layout tab. And from the page setup selection, finally, Print Area, button. Click the said print area. And make sure that I'll print out exactly what we want. I'll go to print preview from File tab and print. As you can see, it will print only the area I selected. To remove the print area, click the Go Back button, then to Page Layout, tab Print Area, and Clear Print Area. And it will print everything on the screen. This tip is ideal for large worksheets that you want to print a specific part of the data. Thank you for watching this lesson, and I will see you in the next Excel hack. 45. Entering Data quickly and efficiently: For this tip, I want to show you guys how to enter data very fast. So let me show you how it works. In this worksheet, we have three cells with text inside A1, B1, and C1. There are three headings, name, income and expenses. In the first column, we want to insert some random names. So let's start typing names. Let's type. And Dreyer's in the a2 cell. John and A3. Maria in a for Mike and a five bill In a six. Some of these names are going to be repeating like John and the A7 cell. Take a look. If I insert John again, typing J letter makes Excel provide the name automatically. Excel is pretty clever to understand that John is a repeating text. Let's do that again. This would the name Maria in the eight cell. If I have a lot of repeating names, There is a useful technique. Simply right-click on this cell and choose, pick from dropdown list. We see all the previous names there, so we click the one we want. So a really helpful tool here that saves us time. Now let's move on to the B1 cell to show you a nice technique. We already know that if we insert a number and press enter, the active cell moves downward. For example, let's type five and press enter. The active cell, move downward to cell B3. Now type ten and enter. If sometimes you want the act of cell to move rightward or not to move at all. I'll show you a setting to do it. Go to File tab Options advanced. And from the editing options section, you can see this option which has enabled after pressing Enter, move selection. Now if I move this to the drop-down list, there are four directions that cell moves. If you press Enter, down, right, up, or left. Of course, you can uncheck the box if you don't want the active cell to move at all, try it out and select the right direction and press OK. Now I will type the number 15 in this cell and press enter. The cell moves to the right. Pretty straightforward, right? Go again to the advanced tabs from File Options and change the setting to downward. Okay, finally, a nice trick that I want you to learn and I use often is the following. First, highlight the cell that you want to fill with numbers or text. Ok, press Enter. And as you already see, now we move down the columns. But pressing tab, we can move across the rows. So it depends on what you want. We learn different techniques to enter data pretty fast. If you have questions, please let me know. Thanks for watching. 46. Copying and Moving Data with various ways: Why use the two-step process and copy and paste when you can use a different and faster way instead. That's what I'm going to demonstrate here is how you can copy and move data in various ways. So let's dive in looking at these tables on the worksheet. Finally, the Range and from any edge drag with a mouse and pasted somewhere there. It moved quickly with this technique undue with control plus z. If we want to make a copy of the table, highlight the cells again, hold down the Control key and drag any edge. We made a copy of this table. Now let's suppose we want to move this table to an empty worksheet. If we're going to transfer it to the other sheet, we hold down Alt as we drag it. Notice the lower-left corner drag inside the selection to clear cells. Once we get to the other sheet, we can release all press control plus z and go back to the Data worksheet. Finally, we're going to use the right mouse to move the table. Hold down the right mouse button and drag this data somewhere else. The right mouse button brings up a shortcut menu. So when you let go of the right mouse button, here's a menu we want to copy, for example. So select this option. Perfect. I hope you can commit some of the shortcuts to memory. As you put them into practice, you'll navigate and maintain your worksheet more effectively and quickly. Thanks for watching. 47. Using Pick from Drop down List and enter Data quickly: Alright, the next step I want to cover is how to enter data quickly using a hat called picked from the drop-down list. So let's get started entering names and Excel is very common, but very time consuming, mostly repeating names. One technique called pick and drop down list is a list that you can apply and enter data quickly and efficiently. Let's suppose that we have to fill this column with names, start typing in names, start from cell a2. Some of these names are repeating. So there is a smart way that we can get these done faster. Right-click in the next cell and choose pick from dropdown list. We see the previous names there, click on the ones you want and move on. There's also a keyboard shortcut for this technique. Press alt plus down arrow and display the names again. Keep in mind that this only works for text and only work than adjacent cells. This feature increases the speed of entry, so I recommend using it as often as you can. Thanks for watching. 48. Using AutoCorrect to enter Data fast: Excel autocorrect is designed to correct misspelled words. Did you type automatically? But it is more than just correction. You can use this feature to change abbreviations to full text or a play short codes with longer phrases. It can even insert checkmarks, bullet points, or other specific symbols on the fly without you having to access anything. For example, you can type VAT and Excel will take value added tax. This lesson will teach you how to do all this and more. Let's see an example to understand the usefulness of autocorrect. To create an autocorrect entry, click the File tab and then click options to display the Excel Options dialog box. Now click proving and then click autocorrect options. In the autocorrect dialog box, you can create your replacement for auto, correct? In this case for value-added tax, I'll type in vat and press tab. And then in the width box, I can type in the text that I want to replace. In this case, we'll make it a value-added tax, and I'll click, Add. Click. Okay, and okay again, go to cell A1, type in vat and Enter. And magically VAT is converted into value-added tax. So a great way to save time with phrases that you use frequently. 49. How to freeze titles and split screens: All right, time to talk about freezing and splitting pain's. 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 keep it visible while we scroll through the rest of the sheet. There is a feature in Excel called freeze pain to keep an area of orgy visible long scroll to another area of the worksheet. Go to the View tab where you can freeze panes the log specific rows and columns in place, or you can split pains to create separate windows of the same worksheet. Let's suppose you want to phrase the first row. Simply click on this button and select Freeze, Top Row. Scroll up and down. Now, now take a look. You could always see row one at the top. What would happen if we wanted to freeze the first column instead of the first row. Again from the Freeze Pane click Freeze First column. If I move the horizontal scrollbar to the right, column, a will remain visible. Notice that removes the frieze from row one. And alternative is to phrase all columns and rows above a specific cell. For example, we want to freeze rows 12 and columns a and B. Go to cell C3 and click Freeze Panes. The rest of the worksheet scrolls except the specific rows and columns. Another feature of Excel is to be able to split our screen. Let's see an example to understand it. Go to the middle of the screen and click somewhere there. Now from the View tab, click the Split button. What does it do for you? It allows you to divide the window with a horizontal line into two different panes and each one scrolls independently. Look at the result. This is useful when we want to compare data between them, for example, values, dates, etc. Of course, you can split the screen vertically like this. A quick way to remove the split line is by double-clicking on it. Finally, there is a four-way split, but I think it's unnecessary and confusing. Thanks for watching. 50. How to create a Data Entry Form: There are two common issues than I have faced in seeing people face when it comes to data entry and excel, one, it's time consuming. You need to enter the data in one cell and go to the next cell and enter it. Sometimes you need to scroll up and see which column it is and what data needs to be entered, or scroll to the right and then come back to the beginning in case there are as many columns to its error prone. If you have vast dataset that needs 40 entries, there is a possibility you may end up entering something that was not intended for that cell. A data entry form can help by making that process faster and less error prone. 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, and completing a data form may look like a one row at a time project. You could also enter new data through data form. To create a data form, you have to have an existing list like this worksheet. You must also have the button to complete the data entry form. So go to File tab, click options, and from this Quick Access Toolbar, Choose commands from Commands Not in the ribbon. Slide down and find the form button highlighted and added to the Quick Access Toolbar. Keep in mind that before clicking the form button, you must click a cell inside the list. Now press the button and a new window appears displaying the first row. You can navigate through the list with these arrows. You can also add a new row. Click the New button, and fill in the values. So if you have a great crowd of rows and you want to focus on each data row individually. Data entry form is ideal. 51. Filling non adjacent cells with the current entry: I can't remember how many times I used the copy paste feature, entering the same data in different cells before discovering the following trick that I'll show you. It was a real time waster. Look at this worksheet. Let's suppose that you'll need to enter the name Baker Edwards in a specific cell. First of all, highlight with the help of these control. All these cells. Then tape in Baker Edwards in the press control plus enter. And the particular name goes in all those cells at the exact same time. I can use this trick with numbers, dates, and even formulas. Let's see another example. I want the cell in these cells, so I highlight them. I'm typing the formula only for these specific cells and I press control. Enter. The same formula has gone into all these cells better of course than copying and pasting many times. This can save time and we want to enter the same data or formulas in multiple cells, especially if they're not in the same contiguous range. If you want to fill data or formulas down a column, we can use the fill handle or copy and paste. However, if the cells that are not in the same row or column, then control plus Andrew can be a real time saver. Another useful way to use a control plus enter keyboard shortcut is to stay on the active cell. I remember last year I was entering the percentage completion number in the cell. I wanted to keep that cell selected to type a different number in it to see the change in the chart. I didn't want to have to hit enter and then up arrow back to the cell using control plus Antar allowed me to keep the active cell selected and modify the cell again. 52. Highlighting Blank cells and Sorting columns with them: All right, time to talk about how to highlight blank cells and sort columns with them. So let's dive in. In this worksheet, we have three columns. The first is the country of the employee, the second is the employee's name, and the third is the age. We didn't want to repeat each country many times, so we eliminated them. For example, these employees are all from Australia and these ones are from France, et cetera. If we print the table form, File tab and print, it will look better, nothing wrong by now. The problem starts if we sort the data, the name, for example, Baker stewards is from Australia. Now highlight Column B and click the a to Z button from data tab. The sort and filter button Sort a to Z and made sure that the expand the selection is selected. Click the Sort button and let's find bakers Stewart. We don't even know from which country he is. So the empty cell created as a problem because of the sorting. Of course, let's undo this with control plus z. So we need to fill these cells with the actual country, these Also, et cetera. We've got to find an easy way to select only the blank cells from this column. Click on column a, go to Home tab. Choose, find and select. Go to special. And from this dialog box, select blanks and okay. We can either press F5 and it will open the same dialogue box. Look at the gray cells. They're the only blank cells until the end. Now we want these empty cell to be equal to the cell above it. Let's type equal a2 and be careful press control enter, because with Enter it will change only the a three-cell. We completed the first step to fill the cells with the equivalent countries. But all these cells are formulas as you can see. How do we convert them into pure text? Pay attention now to this fantastic trick. Select column a with a mouse right-click, drag the entire column right to column B without releasing the right mouse button, then left. Now release the right mouse button. And from this menu, select copy here is values only. It turns the formulas into results, into text. Click and we are done. Now we finish our job. I hope you find this lecture helpful. And if you have questions, please post them onto the discussion board. I will be more than happy to answer them. Thank you. 53. Filling Random Values into every cell in a selection: Is there way to fill dummy values in a worksheet so you can make up your datasets. There may be cases when you need to generate random numbers in Excel. For example, select random winners from a list or get a random list of numbers for data analysis or create random groups of students in a class. In this lesson, you will learn how to generate random numbers in Excel. Look at this worksheet. For example, we want to fill the values for these five countries. For each quarter, we will use the RANDBETWEEN function which generate random values. But before then, let's show you a little trick. If you want to fill in the same value for all of the cells, for example, 100, highlight all the cells. Type in the first cell 100, and press control. Enter, the value is entered into all those cells. Now let's click Control plus z. First of all, make sure that all the cells are highlighted. Now go to the formula bar and type equal rand between parenthesis, one hundred, ten hundred, which is the bottom value, and 5 thousand, which is the top value. Press control plus enter. Keep in mind that these random values would change every time we recalculate the worksheet. If we want them to be fixed, we need to paste them due to the formulas. It's easy. Copy the cells with control plus C or with right-click, doesn't matter. And from the Home tab, click the paste button's down arrow and click Paste Values. The formulas are converted into values and they'll stay the same. So randbetween formula is an easy way to make your sample data. 54. How to generate Random Decimal Numbers between 0 and 1: Sometimes you need a list of unique decimal numbers between 01 to try some functions or some features in Excel. There's a handy function called rand. I'll show you immediately how it works. Suppose you want to fill this table with random sales between 01. Start by highlighting all of the cells you want to fill. Type equal rand with a pair of parenthesis and control plus Enter. Look at these results. These cells will be changing every time we do something in Excel. So it would be better to paste them as values. Highlight them and with right-click, drag them a bit to the right and then to the left. From the menu, select paste them as values. Okay? In the previous lecture, we will show you how to fill random numbers using the RANDBETWEEN function. So you will have different ways to fill a range with random numbers. Thank you. 55. How to use COUNTIF and Data Validation to prevent duplications: In this worksheet, we want to enter the data of a customer. We have two primary columns, Customer ID, which is unique, and customer name. Of course, we don't want to enter a duplicate number or text in the ID column, but it's human to make mistake. A way to prevent us from making these mistakes is to use data validation feature and COUNTIF. What is the COUNTIF function? When we are trying to count the number of times we have an item appearing in a list of texts, a value in a text, or a list of dates, the function count if should be used. I'll show you right away. Let's put some numbers in column a. Now go to cell C2. Countif defines the range where we're looking. In our case Column a comma. And if we want to know how often the number eight appears in our list, we typed A4 cell. Look at the result. It's three, because three times this number appears. We wrote the result of a function to be equal to one to prevent duplication. The lead the numbers and highlight Column a. Go to the Data tab, Data Validation. On the Settings tab on the ALL criteria, choose custom. In the formula box type, COUNTIF, parenthesis a column comma A1 means every cell in column a, right parenthesis. All this equal to one. What do I mean by that? I tell Excel to check all the values in column a and each value will be unique and it will only appear once. So start putting some numbers. Oops, that's a duplicate. You can also customize the message or you can use text instead of numbers. So use the combination of COUNTIF and Data Validation to get rid of duplicate values. Thanks for watching. 56. How to add comments inside Formulas: Some formulas in a worksheet are relatively difficult to understand. When entering formulas in Excel, you may want to remind yourself or someone else what is in it for the future. Or you may want to add instruction to let users know of your spreadsheet, know what to do in specific cells. In this lesson, I will show you how to add a comment to a formula. In excellent way to get information about the formula is to add comments and sign of them. To do that, I use the n function. Excel users probably already know the other way to add a cell comment through the insert a comment command that creates a sticky note like comment that pops up whenever you mouse over the cell. The end function is better if you don't want those comments in red triangles cluttering your spreadsheet and you're adding comments for your personal use. The end function allows you to add comments directly inside your cells on your formulas. To use a function to add a comment, simply add a plus sign to the end of your formula and then enter some text in quotes inside the parentheses, passing that text at the end function. So double click to edit the formula, clipped to the formulas right, and type the plus sign and then the letter n for the n function. Now type left parenthesis, double-quotes, type in the comment quotes again and close with a right parenthesis. Enter. As you can see when I highlight the cell, the content is displayed in the formula bar. Thanks for watching. 57. The AutoSum magic: Did you know that Excel sum is the function that people read about most To make sure just check out Microsoft's list of top ten most popular Excel functions. No wonder they decided to add a special button to the Excel ribbon that inserts the sum function automatically. So if you want to know one is autosome and excel, you already got the answer. In essence, Excel autosome automatically enters a formula to some numbers in your worksheet. You can find it from the home or Formulas tab. It has a drop-down arrow that you can choose some of the Excel functions. Autosome has some tricks that make our work easier and faster. Look for example, at this data table, it's straightforward to sum these values. Go to the first empty cell to the bottom and double-click the autosome button. The total is ready, undue with control plus z. If you prefer, you can use the keyboard shortcut alt plus equal. Undo again. You could select the data first and then click the autosome of press Alt and equal and get the result. Now look at this table. We want to calculate the totals per quarter on the right. So like these cells and with old equal, we have our totals. The same with these cells. Undo. If you want to put totals in both locations, highlight the data and press the autosome button. Keep in mind that if you're working with a table using the autosome button after selecting the row below the table, inserts a total row from the table and creates a formula that uses the subtotal function rather than the SUM function. The subtotal function sums only the visible cells in the table, which is useful if you filter the data. 58. Identifying and fixing error inside of an Excel Formula: Although frustrating formula errors are useful because they tell you that something is clearly wrong, this is much better than not knowing. Identifying the errors in your formulas is not an easy task, but you can make it with simple steps. Excel, let's you know about it with an error message. If you create a formula in Excel that contains an error or a circular reference, a handful of errors can appear in a cell when a formula or function in Excel cannot be resolved. Knowing their meaning helps correct the problem. Here I have a table with Excel error messages. You will find this table in your resources. The first step is to click the cell that contains the error. You can see that the error in this worksheet is in cell D6. Then I go to the Formulas tab and in the Formula Auditing section, you can see many options. The Formula Auditing Group is on the Formulas tab contains commands to help you identify and fix formula errors. The most useful command is the error checking, drop down, click the down arrow, and the trace editor. Look at the tracer arrows. They're going back to cell G2. So you can identify that the error is in this cell. You can remove arrows from this button if you want. Now we will try error checking. It opens a dialog box. It checks the worksheet and identifies any errors that appear within there. On the left, there is a useful piece of information about the specific error. If we have more complex formulas, then the show calculation Step button will help us. Another way to identify an error is through the Evaluate Formula button. Click on it to see the results. So that's an easy way to identify and fix errors inside of Excel formulas. Thanks for watching. 59. Finding related formulas easy: Sometimes for a complex worksheet, it's chaotic to search would shells are affected by selected cells value. For example, let's highlight this cell and let's suppose that you want to find related cells that depend on the D6 cell. There's a keystroke shortcut Control plus Shift plus right bracket. As you can see, it selects all dependent cells and highlights all cells that has formulas that refer back to our cell. If you want to remember these related cells, you can fill them with Cullen from the Home tab and Fill Color button. Another way to find which cells are affected by our selected cells value is from the Formulas tab and Trace Dependence feature. Let's press this button and as you can see, it shows arrows with the related cells. Notice that not all associated cells have been found, but only the directly related cells. If you press it again, it will continue to add arrows because they refer to other cells, et cetera. Maybe some of these dependencies have the reference to another worksheet. Finally, if you want to find which cell is getting the data from, you can use Trace Precedents feature from the Formulas tab. Control plus Shift plus left bracket. Let's try and for this cell, click first once and then keep clicking Trace Precedents. Look at all these arrows that indicate which cells affect the value of the cell we just highlighted. I use these techniques in complex worksheets and they saved me a ton of time. I hope they will help you in the same way. If you have any questions, please let me know. Thank you. 60. How to enter the current Date or Time: In this video tutorial, I'll show you how to enter the current date and time. Go to cell A1 starts i being equal, then the formula now and a pair of D parenthesis. Press enter and the current date and time will appear. Now let's try to enter only the current date. I'll show you how to do that with a keystroke shortcut. Go to B1 cell and press control plus semi-colon and the date appears. Notice that the value is the actual date and not a formula in this cell like the previous example. If you want to use a formula you will use today and a pair of parenthesis. Another way to go is through cell C1 to enter the current time. Now press control plus Shift plus semi-colon and the time is ready. If you drag the corner with the fill handler, you will get different hours, but the minutes are the same. If you want to insert a time series that will change every ten minutes, then go to this cell and change it. Now highlight these two cells and drag them down. And we're ready. Thanks for watching. 61. Using the new in Excel 2013 Function PDURATION: In Excel 2013 and later, a new function helps us calculate how long it takes to reach specific goal. The pj Eurasian function calculates how much time is needed for initial investment or present value to reach a given amount or future value, assuming a constant annual interest rate, p Duration returns an amount of time in periods linked to the number of compounding periods per year. With one compounding period per year period equals years, with four compounding periods per year, periods equal quarters and so on. This worksheet has a data table with these four informations, rate months per year, present value, and future value. The future value is our goal. So we will use the p Duration feature to calculate how many months we need to reach our $50 thousand goal. Highlight cell B6 and type equal p Duration and parenthesis. We want the monthly rate, so we divide the rate by month per year. Type b1 is divided by B2. Now PV is the same present value. So type in B3 cell. B4 is the future value. Close parenthesis and press enter. So we want 208 months to reach our goal. Try this function by yourself and see the results. Thank you, and I'll see you in the next lecture. 62. How to Round values with various ways: When you don't need an exact answer, rounding is a useful skill to use to round a number, eliminates the less significant digits in plain English, making it more straightforward, but keeping close to the original value. In other words, rounding lets you get an approximate number without the desired level of accuracy. In everyday life, rounding is commonly used to make the numbers easier to estimate, communicate, or work with. For instance, you can use rounding to make a long decimal numbers shorter, or to report the result of complex calculations or round off currency values. Many different rounding methods exist, such as rounding to the integer, rounding to a specific increment, rounding to a simple fraction and so on. And Microsoft Excel provides a handful of functions to handle different rounding types. Round is on the primary rounding function in Excel that rounds a numeric value to a specific number of digits. In this worksheet, you can see a data table with an employee's name in the first column and salaries with three decimals in the second column. You want around these salaries to two decimals. So go to C2 cell and type equal round, press Tab B2 and number two for number digits. Remember, you want up to two decimals. Now round function has two alternative functions, Round Up and round down. Let's try them in the next column. Type in equal roundup C2 and 0, because we don't need decimals. C, The result do the same in cell E2. Also a nice trick to round a number to the nearest. For example, a $100 value is to type minus2 for number digits. Let's try it in this empty column. Nice. Finally, if you want a rounded number to the nearest multiple number that you choose, you can use em round function. For example, equal m round B2, ten. If you have any questions, please post them in the discussion board. 63. Converting formulas to values easy: How many times do we have data calculated from a formula? And we wanna take these results and not the formula and paste them into another column or worksheet. What I'm going to demonstrate here is how you can convert formulas, the values. You may have different reasons for converting formulas to values, to quickly answered values to other workbooks or sheets without wasting time on copying paste special to keep the original formulas are known when you send a workbook to another person, for example, your retail markup to the wholesale price. Or to prevent the result from modifying when the numbers in the linking cells change. You can convert formulas to their values on either a cell by cell basis or transform an entire range at once. There is a smart and quick technique using the right-click of our mouse. Let's see the steps of this nice trick. First of all, highlight the data that you want to copy only on the result of the formula. Select E2 to F5. With the right mouse button point to either the left edge or the top edge or the right edge. Hold down the right mouse button and drag into these columns i and j. The shortcut menu appears. We want to select copy here as values only. Look at the result. We made a copy of the results from the formulas. So if you're going to convert formulas into values, this is an extremely efficient and fast technique. Thanks for watching. 64. Using Named Ranges in Formulas: What I'm going to demonstrate here is how you can use name ranges in formulas. Creating named ranges is very easy and very useful, especially if we want to write formulas. When you create named ranges in Excel, you can use these names instead of the cell references. For example, you can use equals some sales instead of equals sum A1, A5. Overall, there are three ways to define a name and Excel named box, defined name button at Excel named manager. First, let's create three named ranges for these three columns. Highlight these cells and in the name box type, quantity, and return. Similarly, Create Named ranges using the other two columns with the names, unit price and total sales. Now go to cell B21. We want to calculate the sum of the second column, taping equal some parenthesis. Quantity is the name of the range. Close parenthesis and enter. Okay, one more example of the total sales range, and we are ready. So as you can see here, we have a powerful tool that you can use and formulas. Now let's open the workbook, constants dot XLS x to show you how to define a name that refers to a constant. There are some constants like VAT, and that would be better to store them using a name and use them in your formulas. Also, it would be nice to change the value of a constant at any time. For example, a VAT or a tax can alter many times v of the government. You will use a feature called define name and you can find it from the Formulas tab and Define Names section. Press to define name button. Now type in the name, in this case VAT into the name field. You can specify a workbook is the scope for the name. Click the refers to field the lead its contents and replace it with a simple formula such as equal 16%. Click OK to close the dialog box to try, it highlights any empty cell and type equal VAT. Look, it returns 16%. Of course you can use it with a combination of functions. For example, go to this cell and type semicolon equal B6 multiplied that. If that has changed, go to Name Manager, edit, and change the field value and press OK. Keep in mind that the new value will be used to recalculate all the formulas that use this name. 65. Displaying all worksheet formulas in a second: Alright, the next step I want to show you is how to display all worksheet formulas at once. So let's dive in. When I have a large worksheet with many complex formulas and searching for an error to figure out what's going on, I use a tip that shows me all the formulas in the worksheet. It's one of my favorite tips in one of my top ten. Here we have a sheet. If I want to see all the formulas, I can simply press Control and tiled key. It's the key in the upper left corner of your keyboard, besides the key with the number one. Look at your screen, all the formulas appeared. Now I can fix my formula error quickly. If you press control plus tiled again, you can go back to a typical display, a smart tip that I have used hundreds of times. Another way to display the formula tax is by using a formula text function. Let's make an example. Go to cell G2 and type equal formula text parenthesis and click this cell's address within the formula. In our case, it is F2, close parenthesis, and press enter. And there's the formula. So we learned an easy way to display the text of a formula. Thanks for watching. 66. Using TRIM Function to get rid of unwanted spaces: Are you comparing two columns for duplicates that you know are there, but your formulas can not find a single duplicate entry or you adding up two columns of numbers, but keep only getting zeros. These are only a few examples of problems that you may be seeking answers to. And all are caused by extra spaces high-need before or after or inbetween numeric and text values in your cells. Leading or unwanted spaces can cause us big trouble. For example, a problem with leading spaces is that we don't want to sort a column. Microsoft Excel offers a few different ways to remove spaces and clean up your data. In this lesson, we will investigate the trim functions capabilities, and the fastest and easiest way to delete spaces in Excel. Look at this table, it has seven columns. Let's try to sort the third column. Go to the Home tab and from the sort and filter button, select Sort a to Z. Of course, this sorting is wrong because we have these values and leading spaces. What are the steps that we can take to clean up the spaces? First of all, insert an empty column to the right of the column that has the problem. Right-click on column D and choose insert. We will use the trim function. Trim, clean up all spaces inside or leading spaces. Type equal, trim, parenthesis, and cell C2. Press enter. The spaces have gone. Now double-click on the lower right corner, and we have the list free of spaces. Use the right mouse button to drag this list to the left. Select copy here is values only delete this column because we don't need it anymore. Trim function is the easiest way to get free of unwanted spaces, especially in large workbooks. 67. How to calculate a person's age: There is no particular function to calculate agent Excel. However, there exist a few different ways to convert the date of birth to age. To calculate a person's age is a real brain teaser. The calculation depends on the current day and also leap years. The most accurate way to calculate ages by using the date if function, it's a function that has rare usefulness. Dative calculates the difference between two dates and expresses the result in terms of months, days, or years. The syntax for date if function is as following, date one and date two, or standard dates or a reference to a cell that contains a date. Date one must be earlier or equal to date too. The third argument interval is a text string that specifies the time that will be returned. Some of the interval codes are m, the number of complete months between date one and date two. D, the number of days between date one and date two. Y, the number of complete years between date one and date two. There are some other codes like YM, IID, MD, but we don't need them. Cell A1 contains the date of birth and the current date. So let's type in equal date if parenthesis a_1, comma a2, double-quotes. Why double-quotes again, close parenthesis, press enter and the function returns the result, which is the person's age. Of course. Try the function with different interval codes and see the result. And don't forget, take action. Thank you very much. 68. How to convert values between Measurement Systems: Did you ever wonder how to convert Miles to Kilometers using Excel? The answer is the convert function. The Excel convert function can convert between various measurements in categories like distance, speed, time, volume, weight, and much more. For example, you can use Convert to convert feet into meters, pounds into kilograms, Fahrenheit to Celsius gallons into liters and many other unit conversions. The convert function requires three arguments, the value to be converted from unit and the two unit. In cell A2, we have a distance expressed in miles. So you can use this formula to convert miles into kilometers. Go to B2 and start typing equal, convert parenthesis a_1 comma ny in quotes, comma KM and quotes parenthesis. Press enter and be careful of the arguments. The second, third arguments are the unit abbreviations. You must use the exact abbreviation as they are also case-sensitive. Let's make another example with Fahrenheit and Celsius. Highlight cell B5 and start with the following formula. I think it's a straightforward function that you can use. You can also download the PDF that I created for a list of measurement units for resource files. 69. Locking and Hiding Formula cells: During this demo, I will demonstrate how you can lock and hide formula cells. So let's get started. If you work for a company, you may need to distribute your workbook to a coworker or a user. So if you want to hide the formula cells on a worksheet, there are some steps that you must follow. First, you have to select all the formula cells. Select the cell. You can do it easily from the Home tab, find and select and go to a special option. A new dialog box appears. Select the formula option and make sure that all the boxes below are checked. After pressing OK, Excel selects all the formulas cells. Now right-click on any of the selected cells and choose format cells. From protection tab, check both the locked and hidden checkboxes. Click OK. You have to unlock all the NADH formula sells the same way. Select the cell from the Home tab, find and select, select the go-to Special Option. A new dialog box appears. Select the constant option and make sure that all the boxes below are checked. After pressing OK, Excel selects all the non formula cells. Now right-click on any of the selected cells and choose format cells. From protection tab, uncheck both belong to in hidden checkboxes. Click OK. The last and most crucial step is to protect the worksheet. From a review tab click protect sheet. In the protect sheet dialog box specify a password. Click OK, and you'll be prompted to re-enter the password. When you protect a worksheet, the user cannot insert rows or columns, change the column width, or create imbedded charts. From there, you can specify the actions that are allowed when the sheet is protected. After these steps, we have managed to hide our formulas. 70. Formatting Data as Table with Table Styles: Excel tables are used to summarize data and put data in more organized structure. We can make a table in Excel using keyboard shortcuts very quickly. Look at this sheet. If you press control plus L or control plus T, it automatically displays the create Box dialog box. Click OK, and we have our table. Now if you want to select the table, you can press control plus a twice. Look at the result. If you're going to pick the entire worksheet press control plus a one more time. Finally, from the table tools, there is a design tab. From design, you can change whatever you want from the table. Now I'm going to demonstrate here is how to format data as a table with table styles. Here we have the same table that displays customer details. Also, you notice that I've changed the format in some cells. For example, these cells have the italic format. These are bald. These have different font sizes, and these have a different color than the others. When you want to create a table from the Home tab, click Format as a table. You can see that the formatting is still the same and converting a table didn't override the existing format. You can use a trick to clear the preventing formatting press control plus Z to undo again from the Home tab press format as a table. And now I want you to right-click to a table style. Look at the options. I will choose to apply in clear formatting. The dialog box appears, click OK. The previous formatting has been removed, so you don't have to worry about your data format before you convert them into a table. Now it's easy to get rid of this. 71. Using the table 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. One of the most useful features of Excel tables is structured references. When you have just stumbled upon a special syntax for referencing tables, it may look TDS and confusing. Still, after experimenting a bit, you will surely see how useful and cool This feature is a structured reference or table reference is a unique way for referencing tables and their parts. They uses a combination of table and column names instead of cell addresses. This special syntax is required because Excel tables versus ranges are mighty and resilient standards cell references cannot adjust dynamically, is data is added or removed from a table. Making a structured reference in Excel is very easy and intuitive. If you're working with a range, convert it to an Excel table first, for this, select all the data and press control plus T. In this war G, there is a table with four columns. Click anywhere inside the table, and from Table Tools, click on the Design tab. Look at the tables name. We will use it in a bit. Now go to cell D37 to create a formula. Type equals some parenthesis. And when I type the first letter of the tables name, it appears, select it and type a left bracket. We can now see the columns headers, double-click total sales, close brackets enclose parenthesis. When I press enter, I get the result. Of course, when I add a new row, it will update the total. Thanks for watching. 72. How to create separate worksheets from PivotTable: All right, the next if I want to cover is how to create separate worksheets from PivotTable using filter areas. So let's dive in. Sometimes we need to create separate worksheets for a specific field. For example, in this sheet, we have five products for three months, January, February, and March. What I'd like to do is to create a separate worksheet for each of the months. I'll show you how to do that in two simple steps. The first step is to drag the month and drop it into the filters area. Nice. It displays months in the top left corner of our worksheet. The second step is to highlight the analyze tab from the Pivot tools table. On the left-hand side there is an options button with a down arrow. Click the arrow, and we will choose the show report filters page option. Select months from the small dialog box. And OK. Excel immediately creates three new sheets, one for each month. This is a useful feature, but doesn't overdo it because you will fill your workbook with lots of sheets. Try it and if you have questions, let me know. Thank you. 73. 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. We want to make a chart for each row independently all the way down the column. First we select the cells from B3 to m3, but be careful only the numbers. Then we click on the Insert tab, we see three different kinds of sparklines, line, column and when loss. Let's click online, a new window appears. We already selected these specific cells, make sure that they are correct. And the data range box, the Location Range to the cell. We want the sparkline cell to appear. Press this button with the red arrow. Select the N3C and the button again. Then OK. Now drag and drop down. We see our line charts, one for each cell. If we want to make some changes like color, we click on the Design tab. Then we click from style, a different one. Also we can insert high point or low point for our chart. There are more settings, as you can see, like markers or negative points. Another useful function is the axis button. If we look carefully at our sparklines, for example, column n, We can see that the values are different, but the sparklines look identical. To have sparklines look virtually comparable to each other, we need some adjustments to do. Click on the axis button, the vertical axis, minimum and maximum value options are set to automatic. Let's change them to the same for all sparklines. Look at sparklines. There's a vast difference in how they look now and before, especially the N7 cell compared to the other cells. Now will try to insert a different kind of sparkline. Go to Insert tab and from sparkline section select column. Click on date range and highlight the same cells. Type in the location of range O3 and okay, drag and drop all the way down. Again. If we have positive and negative numbers like these data than an ideal choices, the win-loss chart highlight the cells from P3 to P7, then click on win-loss sparkline. Indeed arrange now type B3 to M7, again, only the numbers. So we managed to create these miniature charts called sparklines. 74. Linking Chart Titles with Cell Content: Usually titled loop current three places, chart title above the chart shows the vertical axis title located on the left-hand side, and horizontal axis title across the bottom. As you can see, there is no vertical or horizontal axis Chart Title. An easy way to add Chart Elements and of course titles is by pressing the plus symbol to the charts, right? For example, by clicking on the square box axis title, we see both titles in our chart. But if we only want the horizontal axis title, we click on the arrow and make our selection and the same with the chart title. Now let's click on the chart title then on the formula bar, right, Office programs and press enter. The new title shows up. If we want to link titles, we simply select our title, Go to the formula bar, type equal, click on cell a2, sales, which is merged. Press Enter and ready. We can do precisely the same with the axis titles. Of course, every time we change titles from data that changes appear on the chart, a more advanced option is to select more tunnel options from the Add Chart Element button or plus symbol. We have two primary tabs, title options and text options. At each of them has three sub tabs. Now we have as many options to completely change the title of our chart or the title of axes. Some examples are fill, border, more advanced visual effects like a shadow work low. And finally, the alignment, like our titles angle, similar changes are on the Text Options tab and have to do with fonts, mainly like text fill. We also have more choices to change our titles appearance, like Go to home tab, select the font size, go to Format tab, and select Shape Styles. We will see them as we continue on the course. 75. How to make all the Charts exactly the same size: All right, the next if I want to cover is how to make all the charts the same size. If you have several imbedded charts on a worksheet, you might want to make them all the exact same size. In this worksheet, we have four charts to make, all the charts of the same size. First, identify the chart that is already the size you want. You want to make all the charts the same size as char 1. First of all, click the chances a lacked it. Now choose the format from Chart Tools. You see the height and width settings in the size group. Please remember the height and width settings. Press control or you click the other three charts so that all four are selected. The next step is from drawing tools and format to enter the height and width settings and then click OK. The charts are now precisely the same size. If you want to align the charts manually or use the Charts Tool, format a range and a line commands. 76. How to deal with missing data for a Chart: Look at this worksheet. If we observe the source data, we will seize an empty cells and a gap in the yellow and blue lines. So how do we feel this missing data on a chart? First we right-click on the line that has a gap like Outlook line. Then we choose select data. A new window shows up. If we look at the bottom left corner, we will see the hidden in empty cells button. Let's click on it. Now we have three options, gaps, 0 and connect data points with a line. The bullet is already at the Gap and makes sense. What if we choose 0? Let's try it. Click OK and OK Again, we see a connection from this point to 0 and then to the other end. Press Undo or control plus z. Right-click again, then select data and press the hidden and empty cells button. Or choice now is to connect data points with a line. Click OK. And OK. It made a connection from this point to this point. And this is the most reasonable choice. If we didn't have gaps but had hidden cells, then we would just click this option. Thanks for watching. 77. Creating a chart with keyboard Shortcuts: Hey everyone. What I'm going to demonstrate here is how you can create a chart using keyboard shortcuts. So let's dive in. I've already opened this Excel workbook with a worksheet called charts. There is a table inside with some data. I will show you how you can quickly create a chart with keyboard shortcuts using two different ways. The first step is to create a chart, is selecting the data. It's better to not include totals for now and put all the data together. So let's highlight the cells from B1 to F4. After selecting data, we press alt plus F1. It's the quickest way to create a default chart, which is the clustered column chart. Look at that. I created a chart in less than a second, that symbol. Now press control plus Z to undo that. And let's move on to the second way, how you can create a chart. Until now, we created a chart in the same worksheet with our data. But sometimes it's better to have a chart in a new worksheet. This method is simple. We already highlighted the cells of our data from B1 to F4 with the mouse. Then we're ready to press the F11 key. F11 creates a new chart that appears on a new worksheet called chart one. What does this do for you? It shows only the chart with no distraction from our data. Of course, by making changes to the value of data, the chart will be changing even though it is a different worksheet. If I want to create fast a default chart, I use both methods. If I want more complex charts, I use the Excel ribbon. Hope you find these methods helpful. Thank you. 78. How to analyze an active Workbook using the Inquire add in: All right, the next if I want to cover is how to analyse an active workbook using the acquire add-in. So let's dive in. Analyzing workbooks provides a comprehensive analysis of its logical structure on error status. This information can be essential for assessing the potential risk and impact of the workbook in your organization. Inquiring add-in is an auditing tool that enables you to provide a very detailed workbook analysis. It gives you a report with the following categories. Summary, general information about the structure and content of the workbook. Workbook with sub-categories, general workbook statistics, formulas with sub-categories, specific information about formulas in the workbook. Cells with sub-categories, specific information about cells in the workbook ranges with sub-categories, specific information about ranges in the workbook. Warnings, several types of notifications about workbooks structure and content. Keep in mind that this feature is available in the Professional Plus version and Microsoft 365 apps for enterprise at editions. Now let's see how to install and use it. Go to File tab and then Options. Click the Add-Ins tab. Now choose COM Add-ins from the Manage drop-down list and click go to display the COM Add-Ins dialog box, find and selecting enquire item and click OK. When the enquire add-in is installed, Excel displays a new tab inquire. To analyze the active workbook. Click on the inquired tab and from the range report section. Press the workbook Analysis button. If prompted, save the workbook. A new dialog box appeared called Workbook analysis report. Depending on your workbook size and complexity, it may take from just a few seconds to several minutes for the report to appear. Select a category to see more details about it, such as the relevant worksheet, cell or range of cells, data connections formulas are errors. To complete a report, first, select the items to include in the report. For example, I will include them all. You can export the data to a report by clicking the Excel Export button. This creates a workbook that contains the workbook analysis data in the format that you want to use to document the subject file. Thanks for watching. 79. How to compare two lists of items and identify difference between them: During this demo, I will demonstrate how you can compare two lists of items and identify their differences. When we say two lists of items, we mean a to multi column lists of names. For example, two lists of numbers. To be able to identify the differences between the two list is a useful task. To apply this technique, we will use the conditional formatting. Look at this worksheet. There are two lists, the old on your left and the new one beside it. The first step is to name the range of the lists, highlight the first. Go to the formula tab. Defined names, and define the name type old inside the Name Box. Follow precisely the same step with the second list and name it knew. The next step is to select the cells and the old range from a2 to A2V2. And from the Home tab, click the Conditional Formatting button and choose new rule. In the New Formatting Rule dialog box, click the option labeled. Use a formula to determine which cells to format. Enter the following formula in the dialog box. Equal count if parenthesis nu comma a2, close parenthesis equals to 0. The COUNTIF function counts the number of times a particular value appears in a range. If the formula returns to 0, it means that the item doesn't appear in the range. In this case, the cells background color will change. Now click the Format button and select the formatting to apply when the condition is true. Choose for example, red for filling color, and then OK. Follow precisely the same steps for the new Range. Enter this formula in the dialog box. Equal COUNTIF parenthesis, old, comma c2, close parenthesis equal to 0. Choose also a fill color, for example, a green one and click okay. Look at the result from the old list column. The countries with the red color are unique, so they are not on the new list. Similarly, the countries with the green colour that are in the new list don't exist in the old list. Now keep in mind, like most things in Excel, there are many ways to accomplish the same thing. For example, you could match data in Excel using the match function. Thanks for watching. 80. Using Quick Analysis Tool to find information about data: The quick analysis tool is a collection of some of the selected options that you can use to analyze the data quickly. It includes Conditional Formatting, charts, pivot table and spark lines. It also takes care of what type of data, text data, or numbers you've selected and showing you the options based on that quick analysis tool introduced with Excel 2013. So if you're using Excel 2010 or any lower version, you won't be able to find this assumption. Most of the options you have on the Quick Analysis Tool or fixed, but a few of them are based on the selected data type. For example, in the conditional formatting tab, you will have different options when selecting datatypes, comparing those. If you choose a numeric data. When you hover on any quick analysis tool option, it shows you a sneak peek at the selected data. When you select a range of data, Excel displays a quick analysis button in the lower right corner. Another way to open the Quick Analysis Tool is by selecting your data and pressing controlled plus q on the keyboard. At the top we have a menu, items, formatting, charts, totals, tables, and sparklines. When we hover the mouse over an icon, Excel displays a preview of how the option will appear. Let's try totals. I can find the some, the average that count percentage of the total for each one or a running total. Click on some and see the results. Excel adds a new row to the table with the sum of values. I use a quick analysis tool very often and I think it's beneficial. However, if you want to turn off the Quick Analysis button, you can go to the File tab Options. Go to the General tab of the Excel Options dialog box and de-selected checked box labeling Show Quick Analysis options on selection. Thanks for watching. 81. Getting Data from a PDF File: All right, time to talk about getting data from a pdf file into Excel. Pdf files are prevalent, especially on the internet. Excel gives you the ability to export a workbook into a PDF file from the file tab and save as an option. But what about the opposite process? I mean, you have a PDF file with Excel data, maybe assemble file and you want to copy them into a new workbook. The copy and paste technique is not very useful. And that's because the data will move with formatting problems. For example, the data will be copied in a single column, so rearranging them is a waste of time. The best solution is to first move the PDF data into a Word file. Excel can open a PDF file directly, but word can. So the first step is to open the PDF file from the Word file tab. Let's make an example in open a table. Then the information can be copied and pasted from Word to excel without formatting issues, let's do it. We manage to paste these data from a pdf file into an Excel workbook. Notice that only Word 20132016 versions can open PDF files. Also, if you search Google for online PDF to excel converters, you'll find many applications. Thanks for watching. 82. Presenting Information in a more Compact way, using Group Tool: Worksheets with a lot of complicated and detailed information are challenging to read and analyze. Luckily, Microsoft Excel provides an easy way to organize data in groups allowing you to collapse and expand rows with similar content to create more compact and understandable views. Grouping in Excel works best for structure worksheets with column headings, no blank rows or columns, and a summary row or subtotal for each of the subset of rows. Look at this worksheet, too much information for your audience. I think it's better to present our information more compactly and not displaying all this data. Let's put the active cell somewhere within the data. Go to data tab. Group from the outline section and press auto outline. Look at these symbols and numbers on the screen. Each of these numbers can collapse our data. For example, click the first minus symbol, and we have managed to collapse the quarters from the years 201314. Now click on the plus symbol and look at the result. So you have to decide how much detail you want to shell. If you'd rather Nazi those outlining symbols at the moment, you can press control eight after using it or when we're finished with it, it's an ungrouped, clear outline. The auto outline feature is ideal for big data tables with formulas tabulating data from the left or above. Thanks for watching. 83. Applying Goal Seek Tool to find a target: Suppose you are a business owner or working in the financial department of a company. In that case, you already know how important it is to set goals are applying different scenarios with different goals. Excels what-if analysis tools are underused. There are three main What-If Analysis Tools, Goal Seek, Scenario Manager, and data table. In this lesson, we'll learn some tips about the Goal Seek tool. Goalseek is Excel's built in a what if analysis tool that shows one value in a formula impacts and other. More precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell. The best thing about excels Goalseek is that it performs all calculations behind the scenes. And you are only asked to specify these three parameters. Formula, cell targets slash desired value, and the cell to change to achieve the target. The goal seek tool is especially useful for doing sensitive analysis and a financial modelling and is widely used by management majors and business owners. This worksheet shows the quantity, unit price, and total sales of these products. We want to discover how many items we have to sell on the first product if we're going to increase the compete sale to $300. As I said before, you can use the tool that's built into Excel called Goalseek. To use Goal Seek, click the Data tab on the ribbon than in the forecast group. The one If Analysis button and then click Goal Seek. The Goal Seek dialogue box has three fields. The set cell, D2 value cell, and the by changing cell. Our goal is to get D2 cells, the $300. So our set cell is the cell that contains our target value. So type in D2, press tab and type in the amount that we want to set it to. Press Tab again in the last field by changing which shell type in B2 and clicking OK. Excel does its calculations and finds the approximate value. We've learned a quick way to find a target value. Thanks for watching. 84. Forecasting Scenarios with Scenario Manager: What if analysis in Excel allows you to try out different values or scenarios for formulas. With scenarios in Excel, you can store multiple versions of data in the same cells. The following example helps you master what-if analysis quickly and easily. Assume you want to wine shop and have 100 wines and storage. You sell approximately a certain 50% for the highest price of $60 and a certain 50% for the lower price of $15. So the total profit is $3,750. You want to forecast total profit by creating different scenarios, inserting different percentages into cell B5. So you have to define scenarios for optimistic scenario and a pessimistic scenario. Start by clicking the Data tab on the ribbon and then the what-if analysis and click Scenario Manager. In the Scenario Manager dialog box, click the Add button and type in the scenario name box optimistic as a name for the first scenario. Select cell B5, percentage sold to the lower price for the changing cells and click on okay. And to the corresponding value 0.3 and click on OK again. Now I'll create a second scenario called pessimistic. So I add the add button and I'll type in pessimistic in the scenario box. Select again cell B5, the percentage sold for the lower price for the changing cells. And click on okay. Enter the corresponding value, 0.7 and click OK. We created two scenarios with separate sets of values. What is important is that if you apply a scenario to a worksheet and then save and close your workbook. The scenario values overwrite the original workbook values. So it's better to create a scenario that contains your original amounts. So let's do it. Click the Add button and type initially in this scenario name box. Select cell B5, the percentage tilt for the higher price for the changing cells. And click on okay. Enter the corresponding value, 0.05. and click OK. Let's try the different scenarios where we created using the Show button. Finally, you can create a scenario summary. Makes sure that the original values are displayed in your worksheet. Create a scenario summary worksheet, click summary, and then in the report type area, make sure to type the Scenario Summary option button that is selected and click OK. It's that simple. The scenario summary is ready. Thanks for watching. 85. Monitoring the value in a worksheet using Watch Window: Sometimes a great feature in an application never gets the recognition it deserves. And the watch window and Excel is an excellent example of one such feature. If you use Excel regularly, you probably have worked on some extensive worksheets that span hundreds, if not thousands of rows. It could be useful if some of the cells you need to monitor often could be displayed in a separate window so that you can see the current value and formula at a glance. Microsoft's created the Excel watch window for that exact purpose. Using the watch window, you can pull out a central cells into a separate window and keep track of them there, which saves you from having to scroll all over your worksheet. Look at this workbook. It contains two worksheets, the dataset and the summary worksheet. It's a time waster to keep flipping back and forth between the two sheets when you're trying to make a change in the values. And you want to see how it affects the result of a formula or the summary. A nice feature of Excel is the watch window. How does it work? Go to the summary worksheet. I will click the Watch window button on the Formulas tab, and then I'll click Add watch. And the ad watch dialog box. I select the cell that I would like to watch the value of. So all select cell B2. Now I will click Add. And the watch window appears here, and I can see the value of cell B2 on summary worksheet, which is currently 3250. Now I go into the dataset she tapped and as you can see, the Watch window stays open. Type the value of 100 and cell E5, and press enter my watch window updates to indicate the value of cell B2 on summary worksheet has changed. I can watch as many cells as I want. So I think Watch window tool is a smart way to watch cells from other worksheets. 86. Calculating Running Totals: A running total or a cumulative sum is a sequence of partial sums of a given dataset. It is used to show the summarization of data as it grows with time updated every time a new numbers added to the sequence. Imagine you track sales every day. Your data contains a row for each date with a total sales amount. But maybe you want to know the months total sales each day. This is a running total. It's the sum of all the sales up to and including the current days sales. In Excel 20132016 versions, it's easy to calculate running totals. For example, in this sheet I have 12 months of a year with salaries. I want to calculate running totals in this column. When I see running totals, i mean that C7 cell, we'll get the sum of these two months. D7 cell, we'll get the sum of the first three months, et cetera. Let's get started. Highlight all these cells. At the bottom right corner you can see the Quick Analysis Tool. Click it and it displays various features. The third dam is, you can see is called totals. Click on it. Click on the right point triangle here, and you can see the two running total buttons. The first calculates the column, and the second calculates the rows. Look first at the live preview. Now click and the running totals are ready. This is a time-saving feature that you must use.