Excel Hacks & Shortcuts: 100+ Best Excel Tips (2024 UPDATED)

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. (NEW 2024) Turn ANY Image into Editable Excel Data with AI Technology: Want to turn images that have table data into data that you can edit in Excel. With the data from picture feature, it's a snap. Today, we'll learn how to use the OCR, an AI power technology that can scan images like the printout in our example and convert them into editable digital data. So let's dive in. Picture this. You're wrapping up a productive day, feeling content with all you've accomplished when suddenly a message from your boss pops up. It's a dataset. She'd like you to quickly review and answer summary questions about it. Simple enough, you think as you request the file. But here's the twist. There's no digital file. She only has a printed version. Now what? If you ever find yourself in such a predicament, stay calm. I've heard tales about people dedicating days or even weeks to manually inputting data like this. But there's no need for such laborious efforts thanks to OCR or optical character recognition. This AI Power technology can scan images like the printout in R example and convert them into editable digital data. And the bonus this technology is conveniently integrated into Excel. To give this a try, open a new workbook in Excel. Then navigate to data from picture, picture from file, and browse to locate your image. In this case, I have this image, which is a dataset. Depending on the file size, Excel might take a moment to process and extract data from the image. Once it's done, you'll have the chance to review the imported data for any discrepancies via the Review button. So let's press the Review button. All right. While Excel does a commendable job at parsing the data, it's not infallible. It's your responsibility to validate and confirm these. For instance, this cell is spelled as components, but with a space after the letter M and a S at the end. To correct this, click inside the red bar, select the text, and type over the mistake. Click except to save these changes. Some discrepancies might be subtle and harder to detect, so I'll leave them untouched for now. Let's click Close. This error detection feature is indeed impressive. However, keep in mind that there could be false positives or overlooked errors that Excel didn't flag. So always maintain a watchful eye. If Excel were entirely foolproof, it would auto correct all mistakes without requiring your intervention in the first place. Once you're ready, insert the data by clicking this green button. There is a warning from Excel that we didn't review all the cells. Let's answer it anyway. Now, you have this nice dataset directly in your worksheet. OCR might not have perfectly loaded the data into the desired cells, so some manual adjustments might be necessary. So make any adjustments necessary to clean your data in the worksheet once it's been loaded. Still, this method saved a ton of time compared to manually entering your data. Before finalizing your results, it's a good idea to run another spell check. Although data from pictures should flag all potential issues for correction, having this backup plan is always wise. To perform a spell check in Excel, go to the review tab and click on spelling in the proofing group. While OCR technology wasn't originally developed for Excel, its integration makes it incredibly user friendly and quite accurate, though not flawless. Thank you for watching all the way to the end, and I'm going to catch you in the next video. 3. (NEW 2024) AI powered data visualization with Recommended Charts: Ever find yourself staring at a blank screen, unsure of how to visualize your data. This is where recommended charts can be a lifeline. It offers customized suggestions, helping you kickstart the visualization process. So let's dive in. For hands on experience, I've opened this demo file. In this file, we have a simple dataset showcasing the average whole time from five different call centers. I'm looking for help on how to best visualize this data. To start, click anywhere within the dataset. Then go to Insert and choose recommended charts. As mentioned, this feature's goal is to suggest the most fitting chart types for a clear and insightful visualization of the given data. However, some of the charts that Excel suggest this time around, namely scatter and line found below here, just aren't appropriate. It misinterprets the first column treating these numbers one, two, three, and so forth as meaningful data points instead of identifiers for different categories. This brings us to a crucial lesson. Always be meticulous with your column labels. Without these, Excel might not grasp the data's context, which can lead to misguided assumptions and as a result, unsuitable chart suggestions. To rectify this, I've prefixed the numerical values and the region columns with region. When generating the recommended charts, Excel no longer suggests the scatter plot, as it is now evident that these are not too quantitative variables. I've decided to opt for Excel's first recommendation of a bar chart. This choice does a good job at comparing values across various categories, which is, after all, what we're trying to do here. While bar charts might not be the most thrilling visual option, creating a chart merely for novelty sake rarely yields favorable results. Feel free to personalize your resulting charts designed, including labeling the axes, eliminating grid lines, and so forth. It is important to note that recommended charts are designed to provide a starting point in the process, not to complete the entire task for you. 4. (NEW 2024) Using AI Copilot in Excel: In the world of spreadsheet software, Excel has long reigned as the go to tool for crunching numbers and analyzing data. But what if you had a smart assistant helping you navigate Excel's vast capabilities, giving you expert advice and quick insights? That's the idea behind copilot, the newest and most innovative feature of Excel. Copilot uses artificial intelligence or AI to change the way we interact with spreadsheets. So let's start learning this amazing tool. In simple words, Excel copilot is like having a smarter helper right inside your spreadsheet. It uses artificial intelligence, which is a type of technology that makes computers think and learn like humans. When you're working on your Excel sheets, AI watches what you're doing and suggests helpful things to make your work easier. For example, if you need to do a calculation or make a chart, you can just ask copilot using regular words and we'll figure out what you need and help you to do it. It's like having a friend who knows a lot about Excel and gives you tips to get your work done faster. Now, although copilot currently does not come with every level of Microsoft 365 subscription, and it's not freight, it's worth looking at how it can help here in Excel. To get access to the copilot features in Excel, all you need to do is purchase the Microsoft 365 and copilot subscriptions and install the Excel app. Get Microsoft 365 subscription. Ensure you have a Microsoft 365 subscription that supports copilot, such as Microsoft 365, personal, family or business. Obtain copilot subscription. Install Excel 365 or log into the web app. Once you have purchased the required subscriptions, the copilot icon will show up in your Excel ribbon granting access to its features. So if you have access to copilot with your subscription, you'll see a button up here in the very far right end of the ribbon with a home tap selected. And if you see it there, you can follow along with me as we continue working with our National Bank Projected Revenues workbook. I have a link in the description, so you can download the same file and follow along with me. If you don't see it set up there in the top right, just sit back, relax, and watch as we explore some of what copilot can do here in Excel. To use it, there are some prerequisites. Copilot can only work with Excel tables, and that's because the first thing that must happen is the data we use copilot with has to reside inside a table. That's how copilot knows the boundaries of what data to analyze. So we'll take care of that first. It's really quite easy. What we want to do is the locations. We want all of the months of the year and the data down below in a table. To do that, we select the data first. So click and drag from A to across down to eight and release. That's the data that needs to go into a table, and it's easily done by going into the Insert tab. And from that ribbon, click in Table. You can see the range is already there because we selected it first. Our tables has headers, it's checked on. Those are the months of the year. The labels like location. Click Okay. And it's now on a table, and there's a lot of things we can do with tables. So there are many more advantages than just copilo it to having tables like these filter buttons that we see across the top. You can see the formatting colored in the banded rows. Well, right now, the table design tab or ribbon is on display because of that. And we can go to the table styles, drop down button and choose a different style than the default. For example, if we wanted to look more like what we started with, we go to the very first one, which is none. That's no style. Click there. And you'll see the only difference now if we click in the background is that each of our labels across the top has a drop down filter button. That's how we know that this is a table. So we have to select a cell inside the table to be able to use copilot. If we click the copilot button, you will see a message down there. AutoSave is turned off, and this is a second prerequisite. It needs to be stored in the Cloud in your OneDrive, for example. So to do that, it's really quite easy. We click this green button, or we go off to the AutoSave button, which is currently turned off. We're working with a workbook that's stored locally on our own computer. But when we turn this from off to on by clicking it, it's going to take it and move it to the Cloud. I have my personal One Drive already connected. So you might have your own personal OneDrive here that you can sign into if you're not already. So it's taking this and moving it into the Cloud. I can tell it's in the cloud because AutoSave is turned on. I don't need to update changes. That happens for me automatically. And up here, you can see the save button has circular arrows indicating it's automatically updated. That's my hint that I'm working with a table in a workbook that's stored in the cloud. The two prerequisites, we need to use copilot, and sure enough, copilot is now a selectable button. So if you're following along, make sure you click anywhere inside the table. You save to the Cloud and click copilot. This is going to open up a pane on the right hand side of your screen. Now, copilot is like having an advisor that you can ask questions or make requests. First, if we scroll up, you'll see some examples of things that you can learn to work with like adding columns of formulas and highlighting data. Sorting and filtering and, of course, analyzing data as well. Down below some sample requests or questions you can click or go down below in the prompt area, click there and simply type in, or if you're hooked up with a mic, you can even speaker request or question. All right. Let's try something simple like add a column that calculates all the totals for each of the locations. So it might be something like this. Add a column that calculates the total revenue for each location. Now, all we do is we hit Send to send that request to our advisor named copilot. And you can see co pilots working on it, analyzing, and trying to understand the data on the table. And then it's going to pop up some suggestions. So we'll let it do its thing. All right, I just finished up. And as we scroll up, we can see what it's going to do. It's going to summarize the total revenue for each location by adding up the revenue for all of the months of the year. That's exactly what we want it to do. You can see what it's doing, creating a formula for each of the months of the year. If we want a better explanation of the formula, we can click the expansion arrow. It's going to sum up the values for January to December for each of the locations. That's perfect. That's exactly what we want. Now, the insert button is actually also a preview button. If we scroll over to the right of column M, that would be the ideal spot for those totals to go. Just hover over insert column. You can see it shaded in the background. That's exactly what we want. So click the button and it gets added just like that. Total revenues. Click anywhere in the table to deselect the highlighted content, and thanks to copilot, we were able to do that without really knowing how to do it, even though we could have used some of our knowledge to create those sum formulas to total up those numbers. But copilot did it nicely and quickly. All right, let's try another one. Maybe we're having difficulty picking up all of the trends that we see for the various months and locations. So we could simply ask, are there any trends for each location? Let's go into the prompt area and click there. Are there any trends for each location? Question mark and send? Again, it's going to analyze the data on the table where we have our selected cell, and it's going to provide some options to us here in the copilot pane on the right hand side. All right. It's finishing up, and as we scroll up, you can see, well, it looks like we have some correlation here. We could add it to the sheet if we wanted to. There's the explanation. Notice also down below, we can see another insight. There might be more insights, or maybe we'd like to see all the insights and have them added to the grid. All that means is it's going to create a new sheet down below to put all the insights in so they keep them separate from our worksheet data that we have been working on throughout this lesson. So let's choose add all insights to grid. Sure enough, sheet two is created for us, and you can see some insights in their own little windows, different correlations. You can see it's looking at correlations between various locations. Totals always seem to be the largest value. Of course, that makes sense. Down below some pivot tables as well. And if we want to keep those, we just leave them where they are on sheet two. We might want to relabel these, though, so they're better understood. For example, double click Set two and just type insights. Press Enter to lock that in. That means if we double click Set one, we might call it. How about revenues, like so and press Enter? There are other things that we could do like ask it out to show the locations where the total is more than a certain value or less than a certain value to filter out data. Really, the only limit is your imagination. So let's close up copilot pane up here in the top right hand corner. As we finish up our journey through copilot and Excel, it's clear that this tool can change the way we work with spreadsheets. It is designed to help us work more intelligently, not more strenuously. Thank you for watching this video all the way to the end, and I'm going to catch you in the next video. 5. (NEW 2024) Analyze Data in Excel for AI Powered Insights: As an Excel user, I suppose that you saw this button, analyze data that is sitting right here on the home tap. Today, we're going to learn how to use this AI feature to make your data analysis simpler, faster and more intuitive. So let's dive in. In previous videos, we tapped into the power of recommended pivot tables and charts. You quickly summarized, analyzed, explored and presented your data using AI recommended layouts. Pretty impressive. Now I'll introduce you to the analyzed data feature, which really elevates the game. It delves deeper with AI to unearth trends, patterns, and insights within your data. While it's a more advanced and dynamic tool than recommended pivot tables and charts, its increased capabilities also make it a tad trickier to master. Let's get our hands dirty here. I've already opened the Excel file wholesale customer data. It is a dataset containing customers annual spending on various product categories, along with categorical variables for channel and region. I have a link in the description so you can download the same file and follow along with me. First, ensure your data is formatted in a table as analyzed data requires it. Next, locate the analyzed data button on the home ribbon. Activating it will prompt a menu to the right of your dataset. I prefer exploring these results from the bottom up. First, the discover insights menu showcases ready made pivot tables and charts selected by AI for potential relevance. Remember, if your insights deviate slightly from mine, it's because AI operates probabilistically. Replicating or auditing outcomes can be tricky, if not impossible. Like me, you'll need to exercise discretion here. Sifting through these insights to select those that contain meaningful information, not all hit the mark. For example, this scatter plot here inappropriately places region on the x axis. Moving on, this next pivot chart seems more logical. So I'll add it into the workbook. At the top of the analyzed data menu, Excel introduces another option for insights through natural language quering. This means that you can ask questions about your data in everyday language and Excel will attempt to answer. It even offers a few example queries. Give them a try if you'd like. Again, your suggestions may differ from mine. Upon selection, a preview unveils the results, leaving you with a choice to incorporate them into your workbook or not. The Insight section is particularly captivating and shows the sheer prowess of analyzed data. Turning to the main menu, I might, for instance, request the analyzed data feature to calculate the total grocery sales for region three. So let's type. What are the total grocery sales for region three? Enter. I'm promptly provided with a pivot table containing the answer, which I can directly insert into my workbook. This saves a significant amount of time compared to manually retrieving this information. At its core, analyzed data streamlines data analysis. It is as effortless as typing in your query and allowing Excel to shoulder the analytical weight. However, as promising as this seems, there are pitfalls. Let's spotlight where things can go awry. Okay. I'll click on the first worksheet, and let's delve back into the analyzed data feature. A challenge surfaces almost at once when attempting to calculate total sales by region. Let's type. What are total sales by region? The results fall short of expectations pointing to a fundamental issue, the data structure. Here we're given the sum of region instead of the sum of sales. This is not the result we're looking for. So to understand why, let's observe the original data. Take a look at the multiple columns in the data denoting sales. Ideally, these should conversion to one unified sales column. Likewise, the departmental headers such as fresh milk and so forth, would be best served in a single heading labeled department. You might have grappled with a data issue like this in the past without even realizing the root cause was a structural issue for the data. Before Power Query, reshaping datasets like this in Excel was a daunting task. But with it, things have become incredibly straightforward. So to dive in, position your cursor within the data table. Then head to data. Get table from tables Range. Let's quickly reconfigure the data here. Again, our mission is to meld the six columns spanning from fresh to deli into two consolidated columns, department and sales. To get started, click Channel. Press Control and select region. Right click and opt for unpivot other columns down below, and press Sto. The data is now in a more digestible format for AI. The new columns by default are named attribute and value. A quick double click lets you change those to department and sales respectively. Having reshaped the data, head to file, close and load to head back to Excel. Now choose the revamp dataset, click on Home and revisit Analyze Data. Ask it to extract total sales by region once more. Ideally, you will receive new results. And here, instead of getting the sum of region, we get the correct results. Let's insert this and celebrate our achievement. As adept as analyzed data might be, it's reliant of the quality of data it's fed in. The age old expression garbage in garbage out remains relevant in the age of AI. So in the last example, we combine the analyzed data feature and power query to provide us high level visual summaries, trends and patterns. 6. 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. Ribbon 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, 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 F one. The control plus shift plus F one 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 it 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, you can see that the developer tab is on the ribbon. I can also reorder items on the ribbon. Right click anywhere on the ribbon, Okay. Highlight the developer tab and click on the move off button to move it before the formulas tab. Now press. The developer tab moved exactly where you wanted it to be. You can try this out and arrange your Excel ribbon. 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 do a mistake is by clicking this button with a 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 typed in A one sell the phrase name customer. But this is a mistake. We wanted to type only the word name in A one. 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 has a drawback. You can only do 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, B three, B six, B 11, and B 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 four actions at 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 in action and Excel is very common. A very clever and fast way is using the F four 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 one, four and five with a yellow color. Later, you decided to fill row eight with the same color, highlight row eight and press F four or control plus y. How cool was that? You repeated the same action without doing the same steps over and over. So here we have one of my favorite hacks. 8. Customizing the Quick Access Toolbar: If you use an Excel command frequently, you can add it to the quick access tool bar. You can even add commands to the quick access tool bar that is not in the ribbon. By default, the quick access tool bar 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 tool bar 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 tool bar by simply right clicking anywhere on the Quick Access Toolbar and then choose to customize the quick access tool bar 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 toolbar. 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 are 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 ad button. The save as command added to this customization Quick Access Toolbar on your right. Press. 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 toolbar, 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. 9. Using Keyboard to Access Ribbon: Hi, everyone, and welcome to this lesson. What I'm 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 are 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 formula 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 F one. 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. 10. Inserting, Deleting & Hiding Columns and Rows: All right. Time to talk about the best techniques for inserting deleting hiding and unhiding columns and rows. You can fill new data in table and excels only by adding new rows or new columns. The easiest way to add a new row, for example, the row above row 12, is to point to row 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 have to find another method to insert a new row only in table one. The bottom line here is to be careful when you're using the Insert row method. Let's undo with control plus Z. Select the specific data from the first table in row 12 12-12. Okay. Now, right click Insert from the Insert dialog box, select the option shift cells down and. Take a look now. The table with countries is intact. For the levers of the keyboard, there is a shortcut Control plus plus to activate Insert and similar control plus minus to activate delete. Sometimes we have to insert 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. 11. Applying Document Themes in a Workbook: A theme is a quick and easy way to give a professional and modern look to a Microsoft Office document. A document theme 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 subtle colors. To change the Excel default theme, you need to go to page LO tab and then click on themes. These are the built in Excel theme choices with a live preview feature. Move the mouse over to theme and see the results. If you like it, click to apply to the theme of all the worksheets in the workbook. To use a document theme 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 to a different worksheet. The theme always applies to the entire workbook. Also, it's better to use a theme than 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 effects. Thanks for watching. 12. Saving Excel as a Read only Webpage File: I will demonstrate how you can save all or part of a workbook to a static web page 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 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 in a workbook with someone and be assured that the data remains intact, you can save your Excel workbook as a web page file. An HTML renders an Excel workbook and a web page 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. Find the file from the desktop and double click to open it. The file opens in your browser and it's read only as you can see. A fast and secure way to send your previous Excel data. 13. Navigate between Workbooks Quickly: During this lesson, I will demonstrate how you can navigate between workbooks quickly, so let's dive in. Right now, we have a workbook open 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 workbooks 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 workbooks 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 two open workbooks. Switch 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's 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. Go to File, New, blank workbook, and we created a new workbook. Do the same steps one more time. Okay. Let's 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. An 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 F six, fast and easy. You can choose whatever you want. Thanks for watching. 14. Zooming In and Out Quickly: All right. The next tip I want to cover is how to zoom in and out quickly. Many times, we have a lot of data in a worksheet, and usually it all might not fit onto one screen. If I want to see my data more clearly, I need to adjust the zoom level. So to zoom in and out, you can use the slide bar on the lower right hand corner. You can zoom out if you click the minus sign or zoom in if you click the plus sign. There is also a Zoom lever, which, if I click, a new dialog box opens. From this dialog box, you can set the zoom level. A second method that I often use is to hold down the control key and move the mouse wheel. Try it and see how easy it is. If you want to have more zooming options, you can click the view tab and use the Zoom section. Here is the Zoom section. It contains three buttons. Click, for example, the Zoom button. It opens the Zoom dialog box. It has five Zoom levels, a fit selection level and a custom level. Select one of these or put a custom Zoom level, for example, 150%. Great. If you want to Zoom to a specific range, simply select the cells, for example, A one to B 20 and click the Zoom to selection button. You can also add Zoom buttons to the Quick Access Toolbar. Then use keyboard shortcuts to access them. Choose your favorite method to Zoom in and Zooml quickly and save time in Microsoft Excel. Thank you for watching and I will see you in the next lesson. Okay. 15. 4 Ways To Protect Excel Workbooks, Worksheets And Cells: Hey, everyone, I hope you are 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 as read only. You can protect the structure of a workbook, you can protect a sheet or a specific part of a sheet. Let's get started. Here we have a workbook with a sheet called block. First of all, we will learn how to fully protect this 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 a 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, one, two, three, four, 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 to open it. Enter the correct password, which is 1234 for our demo, fo. To cancel the protection, you can go to the file Info. Protect the workbook button, click on Encrypt with Password, delete 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 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 unprotect and change the workbook. If you do enter a password, make sure that you choose a password that is easy to remember. Write 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 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 two worksheets, one protected and one unprotected. To make a copy of a worksheet, right click Move or copy and create a copy and okay. Re name it to protect sheet and Enter. Now go to Review tab from ribbon. Find and click the Protect sheet 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 elements you want people to be able to change. I'll leave them as they are and click Okay. Selecting the protect sheet command makes it impossible to make further changes to the contents of any of the lock cells in that 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 the message that it says it's a protected sheet, and to unprotect you will need a password, which I didn't put. Great. Unprotect the sheets by clicking again to this button and click again. Now we are going to uncheck all these and leave them empty. Okay. Press. 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 budgeted amounts. Click once again in the 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 D two to D 20 in our example and click the button, allow users to edit ranges. Then click on, give a range 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 again. Do not forget to protect the sheet now. It's very important. Let's do it from the protect sheet button. Check all the boxes. Okay. Click Okay. As you can see, it works. You can't edit any of the cells of the worksheet except the cell ranges from D two to D 20. All these protection methods work with shapes or charts as well. I hope that you have learned a lot of options to control the protection of your Excel files and minimize the mistakes of your co workers or employees. Okay. 16. 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. 17. (NEW 2024) Select alternate columns in a worksheet: Microsoft Excel. The CO two spreadsheet software for data enthusiasts and professionals offers several methods to select columns based on specific criteria. While many are familiar with the standard techniques, there exists lesser known but more efficient variations to accomplish this task. The simplest way to select alternate columns in Excel is by utilizing the control key in combination with the mouse. Here's how you can do it. Press and hold the control key on your keyboard. While holding the control key, click on the header of every column. Repeat the same steps until you've selected all the desired columns. Okay. Now release the control key. As a result, you have the alternate column selected. By using the control key, you can hand pick every third, every fourth, or every fifth column, so you can apply formatting or perform calculations on specific sections of your data. This method of selecting every other or every nth column is a straightforward and effective approach, particularly for small datasets. However, it may become cumbersome and time consuming when dealing with large sets of data. Manually clicking on each column header can be prone to errors and become tiresome. In such cases, alternative methods come in handy to streamline the process and save valuable time. If you prefer a more precise method of selecting every other or every nth column in Excel, you can achieve this by choosing the choose calls function. Here's how you can use it. In an empty cell, for example, J two cell, enter the Choose calls formula. The first argument should be the source range that contains the columns you want to select. In our example, it sells A two to G 100. Type. In the subsequent arguments, provide the column numbers that you want to return. I want to return company, city, and region. Squall type two, three, and four, because company is the second column, city is the third column, and region is the fourth column. Close parentheses and press entry to apply the formula. The Choose calls formula returns the specified columns as a dynamic array, which you to easily select and copy to another part of your worksheet. As you see, Excel offers a diverse range of methods to select alternate columns or every other column. Whether you prefer to commonly control the key technique where you manually handpick the desired columns or the ingenious use of the choose calls formula. Excel has you covered. Happy column selecting. 18. 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. 19. 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. 20. 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. 21. 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. 22. 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. 23. 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. 24. 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. 25. 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. 26. How to Prevent Someone from Unhiding a Worksheet – Very Hidden Property: 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. 27. 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. 28. (NEW 2024) Restore Missing Labels using Conditional Formatting & Formulas: We're looking at a list here, and it was prepared for printing purposes. Rather than having the department names being repeated in column A, we only see the department N at the beginning of the department. It makes it easier to read. However, if we sort this data and let's keep an eye, for example, on Robert Allen, who's in the ADC group, we're going to run into problems. It's simply with the active cells somewhere in column B here. Do a quick A to sort. That's sorting alphabetically by the way of the data tab. Here's the A to Z button. There it is. And look what's happened. His department names all seem to be pretty much clustered towards the top. And Robert Allen here, who is in the ADC group is now in the operations group looks like. We got a mess on our hands, right? Well, let's undo this with Control Z and use what we might call a trick here. What we really want to do is fill in all these with ADC and all these with Ed Min tracking and so on and so on and so on. And how far down does this list go? 750 rows almost. Clicking Column A, we're going to use a command, which also has a keystroke shortcut. It's found on the home tab. Find and select. Then click Go to Special. And there are lots of little treasures in here, including one for selecting blank cells. Click OK. And what's happened in column A is the blank cells. They're currently gray, have all been selected. And this does not occur anywhere else below the list. So we don't worry about all those empty cells below. We've got all of those empty cells selected. What we want to do with them is in effect, have them contain whatever appears above them. So right now, the active cell is A three. So we write a formula equal, and I'll simply click on A two. But we want the same relative formula to exist in all the other cells. So I won't press enter, but I'll press control enter. And every one of those cells is now equal to the cell above it. It's pretty amazing shortcut when you think about it. Now, we click column A, and we want to get rid of those formulas again, using a trick you might have seen in one of our earlier lessons. I'll point to the right hand edge, hold down the right mouse button and drag into column B. And then right back on top of column A, releasing the right mouse button. Copy here is values only. So every one of these cells here has an actual department name in it. We're all set. But what if we wanted to print this list now and not show these? Let's say we don't want to get rid of them. We saw the problem there, but a little trick here. Let's simply make the font of these other cells be white. We're going to use some logic that says in effect for every one of these cells, if it is equal to the cell above it, that's true of most of these. But for example, not this one. When it's equal to the cell above it, let's just make its font to be white. We're going to start this with cell A two. A quick way to highlight all of these is holding down the Shift key control down arrow. Remember, A two is the active cell, and we're going to use a feature called conditional formatting. It's found here in the home tab, and none of these standard rules will actually work. So we go to a new rule, use a formula to determine which cells to format. And the formula is simply equal A two equal A one. And by interference, this means whenever the current cell is equal to the one above it, let's make the font to be white. Format. Font style color. White. Okay. Okay. And there it is. The data is still there. I'll press control home. The data is there. You can see it in the formula bar of each one of these. And yet, when we print this, we won't see the others. Two different techniques using a number of shortcuts and tricks, you might say, to restore labels initially, bring them back, and also to hide them when we want to print out the information. 29. (NEW 2023) Input Messages: In this video tutorial, we're going to learn how to do great notes and excel with input messages. So, let's dive in. Here we have our employee order form. Let's say that we want to add a little note for the recipient of our workbook here, so they know how to correct format input the data into our cell. What we can do here is we can leave a tasteful note through the input messages. So instead of just leaving text onto the side of the workbook or even writing it in an e mail, we can set something up. That way, when the user clicks on the sale, they will see a little input message with the extra details about how to work with the workbook. For our example here, we're going to navigate over to sale B four. Let's add in a note here inside of B four about the format that we want the user to put the office in. In order to access the input menu here, we're going to navigate on the Data ribbon to the data validation tool inside of the data tool segment. Okay. And here, when we click on the Data Validation option, we get our data validation menu. Now, you may be familiar with this menu by creating dropdown menu. However, we have this extra tab over here called input message. And this is where we can go in and essentially write a note to the recipient of our workbook that they will receive when they click on the sale. Let's say first that we want to give it a title, and let's call it input format because this is the format that we want the user to input the data in. And for our office names, let's have our users put it into the format of our city comma state. And then we also have this checkbox here to show the input message when the cell is selected, which we want to leave on for this example. Now, what we're going to do is click on. As you can see, when I click into this cell, we now have this cute and tasteful note here titled Input format with City and State. This is a great tool to use if you're creating some form of model or any type of workbook that is a form that you would like to give a little extra guidance to the recipients of the workbook in terms of how you want the data to be inputed or where to input the data in general. Okay. 30. 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. 31. 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. 32. (NEW 2023) Copy and paste visible cells only (not hidden cells): In this video tutorial, we're going to learn how to copy and paste our visible cells only, not the ones that you've hidden, so let's get started. First, I'm going to walk you through a super common mistake that I see people make. This is when you highlight particular cells and then try to copy and paste them. You'll see what happens. So in this example, we have our raw data in columns A D. We have the items, the order amounts, the order date and the sales amount. And here on our order report, as you can see, we only field for items and sale amount. So we don't need columns B and C. So what we can do to hide columns B and C is we can click on the column letter heading of the first column, we want to hide. Hold down our shift key and click on to the next heading. And this is that we can quickly highlight B and C. And now to hide them, we can either right click to those highlighted columns and click on Hide, or my favorite shortcut is control and the zero key on your keyboard. And that we'll go ahead and hide those two columns. Now, this is where the common mistakes come in. You might be like, all right. All right. We got all the items in the sales amount matches the order report, item in the sale amount. Let's go in here. Let's highlight this data and then control plus C or right click and copy it. And then let's click into sell G four and do Control plus V or right click and paste it. And on hell. As you can see, it took our hidden columns there and it actually pasted them into our cells. So what we can do here to avoid this is I'm going to do Control Z on my keyboard to undo, and this is where we can get fancy. So I'm going to highlight our data here from A four through D 13 with our columns hidden there already in B and C. And now we're going to navigate on our home ribbon to the find and select button. And here, we're going to select the G two special option or the shortcut on your keyboard is control G. All right. Now we're going to select the option that says visible cells only, or the shortcut for this on your keyboard is the letter Y. And when we click Enter or, it will copy in that selection, and now here's the trick. Without clicking anywhere else, or it will unselect all of what you just selected. If we do Control C in our keyboard or right click copy, as you can see, now, it kind of looks like there are two different pieces highlighted there, right? From Column A to column D, got those little dancing ants, the green lines around there going up against each other, showing that there are two different pieces here. With that copied we're going to click and to sell and do Control V or right click Paste. Now, those two will paste in nicely for us. If you'd like to reformat, you can go ahead and highlight our table and come over to the borders option on the home menu and click back on those thick outside borders. And there we have our data nicely pasted into our order report. This is a great one to remember. If we're going through your data and there are certain items that you don't need. Whether they're columns or rows and you want to copy that data and move it somewhere else, it's a smart idea to hide whatever you need and then go to that visible cells only option and then copy and paste from there. Okay. I. 33. 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. 34. (NEW 2024) Convert Birth Date to an Age without an Expression using Power Query: In Microsoft Excel, you don't need an expression to calculate someone's age. Use power query to simplify the process. Calculating age requires a bit of a specialized knowledge. Any expression, you must understand date arithmetic and accommodate leap years. Years ago, the expression was much more difficult, but the XLSM format supports the Excel year frac function, which makes calculating age much easier. On the other hand, you can skip it all and use Excel power query instead. Power query adds the calculation automatically and you don't have to know the expression. In this video, I'll show you how to use year frac at the end of the sheet level and then how to use power query to calculate age. This video is really about power query because you don't need an expression to calculate someone's age. But in the interest of being comprehensive and offering alternatives for users not interested in using power query, I will show you how to use an expression at the sheet level. There are a number of expressions that calculate age. But if precision matters, use the following equal your frac, date of birth com now. Year frac function calculates the fraction of the year represented by the number of the whole days between the two dates using the form. Frac start date, end date basis. The optional basis argument identifies the type of day count to use, which requires a bit of explanation. Here is a table that explains the different type of day count basis. For our purpose, we omit the optional basis argument. Let's inside cell D three and enter the following function equal ear fraC C three, common now. Enter. As you can see, the function returns a decimal value that tells us how many days into the next birth year each person is. If you like, you can kill the decimals. Okay. Nice. This function is easy to use if you know about it. The biggest obstacle is knowing which argument to use. Now let's use power query, which takes the guesswork out of your hands. To avoid coming up with an expression that returns age turn to Excel power query. The first step is to get the data into power query. Let's click anywhere inside the dataset. All right. Now click the data tab. Then choose from table range in the get and transform data group. If the source data is not in a table object, Excel will prompt you to convert the data range into a table object. Check the M table has headers option and click Okay. Excel will load the data into the power query. Cool. Once the data, including the date of birth is in power query, you can add an age column, very easily. Select the date of birth column. To select it, simply click the header cell. Okay. Now click Add Column. In the from date and time group, click the date drop down, and let's choose age from the drop down. As you can see, the default format doesn't accommodate the age values we expected. To change the format, select the new age group and click Transform. In the date and time column group, click duration. From the drop down, choose Total Years. The age column now displays age as a whole number. Fantastic. The decimal values represent the fraction of the year until the next birthday. If you like, you can round the values as follows. Select the age column, click Transform. In the number column group. Click the rounding drop down. Finally, choose round down. This is the result of rounding down with age values. It's doubtful that you'll want to round up, but it is an option. At this point, you can load the data including the rounded He values into Excel, where you can use them as you would normally in any dataset in Excel. To do so, click the file tab and choose close and load in the close group. Here are the results of a new Excel sheet. The one thing that you lose is the decimal value portion in the He values. If you need that, don't round down in power query. 35. Enter a Line Break into One of Your Cell’s 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. 36. 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. 37. 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. 38. (NEW 2024) Data Validation and Custom Format: Validating Microsoft Excel data is vital to prevent erroneous information. For instance, a phone number that's not really a phone number is of no use to anyone. This can happen when someone enters alpha characters or they may enter to few or too many numeric characters. They won't do so intentionally, but mistakes happen. To ensure that phone number is valid, you might turn to a custom number format, but it won't be enough. In this video tutorial, I'll show you how to combine a custom number format and data validation to force users to enter correct number of numeric digits. Unfortunately, nothing can prevent the user from entering the wrong numeric digits. Phone numbers have ten numeric digits and they generally use the x format. Let's break down a requirement for a phone number. One must be ten characters. Two, must display the x format. Three must be numeric digits. The first thing that jumps out is the x format. The custom format can take care of that. The other two will need validation because of a custom format won't reject an inappropriate entry. It will accept the entry but not apply the format. There are two custom formats that will help when formatting phone numbers. Here's the first. The hash character is a numeric digit placeholder. The zero character is also a digit placeholder, but displays a zero when there aren't enough input digits. The solution requires a combination of custom format and data validation. To illustrate, we'll enter the same input values into a table object and see how a custom format and data validation handle each. First, let's create the custom format for column D of the table object shown in worksheet one as follows. Let's write click D three and choose format cells from the resulting sub menu. In the category list, click custom at the bottom of the list. Okay. In the type control, select general and then enter this hash format. If that custom format is already in the list, select it. There's a space character between the right parentheses and the hash character. Click Okay. Okay. Let's highlight the cells from D four to D six. From Home tab, click the small icon at the bottom right of the number section. It's here and it opens the number format window. From custom category, select the hash format that we entered before. Press. The custom format takes care of the values as you can see. Now, I want to try the zero format. Let's highlight cells from C three to C six. From the Home tab, click again, the small icon at the bottom right of the number section, the number format window. In the category list, click Custom at the bottom of the list in the type controls, lick general and then enter this zero format. Okay. Click Okay. The worksheet one shows both custom formats applied to the same phone number. What you quickly discern is that neither custom format solves every problem. As long as the input value is ten numeric digits, both formats work as expected. However, when that's not the case, neither format gets the job done. Neither format works if the input value contains a non numeric character. Neither format works as expected, if the input value is less than ten digits. Neither format works as expected, if the input value is more than ten digits. The conclusion is that you need both a custom format and data validation. The next step is to add data validation. Move to worksheet two. Excel's data validation feature allows you to limit data input, ensuring that your data is accurate. Most data validation controls fulfill business rules. For instance, you might want to limit input to only dates or numeric values. Those are simple examples, but some requirements are much more complex and data validation is up to the task. In this case, we'll use an expression. Now, let's enter the data validation rule to take care of the other possible errors. Select C three if necessary. All right. Let's click in the data tab. In the Data Tools Group, click the Data Validation option, and here is the dialog window. In the resulting dialogue, choose custom from the allowed drop down. In the formula control, enter equal and left parenthesis is number C three inside parentheses, Len, C three cell sign parentheses, equal to ten and close with parentheses. Click Okay. All right. Well, let's explain this formula. The function remains true if all arguments evaluate to true and returns false if one of the arguments evaluate to false. The Excel is number function returns true when a cell contains a number and false, if not. You can use is number to check that a cell contains a numeric value or that the result of another function is a number. The len formula in Excel returns the number of characters in a given cell. For example, if cell A one contains text Hello World, the formula equal n A one would return 11, as there are 11 characters in the text Hello World. The following expression checks two conditions. Is the entry a number? Is the number exactly ten characters. If either condition is no, data validation rejects the entry. Now, let's see how the two features work together to force the proper formatting of phone numbers. Enter one, two, three, four, five, six, 7890 in C three and press tab. The custom format takes care of this value, as you can see in C three. Very cool. Let's try to enter the following cell C four. A 234, five, six, seven, eight, nine zero. Because the input value contains a non numeric character, the A character, data validation displays an error. Click retry, replace the A with one and press tab. Data validation will accept the entry. Remember, because we're using an Excel table object, Excel copies both the custom format and the data validation to new records. Entering one, two, three, four, 5678, And one, two, three, four, five, six, 78901 will also fail data validation. The former has too few digits and the latter has too many. Make the necessary corrections. So data validation will accept both. Nothing can prevent personnel from entering the wrong numeric character. However, combining these two features will help with other input errors. Many users are unaware that you can combine these two features for better input control. I hope you like this video on how to combine Excel custom format with data validation and remove the pressure for input perfection from personnel by letting Excel do the work. 39. 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. 40. 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. 41. 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. 42. 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. 43. 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. 44. 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. 45. 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. 46. Hide the Status Bar (using VBA): 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. 47. 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. 48. 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. 49. How to Color Rows using Conditional Formatting - Alternate Row Shading: 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. 50. (NEW 2024) 6 ways to save time using Flash Fill: Flash fill showed up in microsoft el 2013. You're probably already using it. However, you might not realize just how flexible and powerful this tool is. It's a quick way to enter new or clean up existing data. In the past, you might have needed a function or an expression to get what you need. Now you enter the pattern you want and press control plus. No fuss no. In this video, I'll show you six ways flash fill can make your work just a bit easier by automatically anticipating your film needs after making an entry or two. Now, there are two requirements when using this feature. One, the source data must conform to a consistent pattern. Two, you must refresh the flash fill results after updating the source data. Perhaps the best feature of all is that the results are explicit data and not the result of a function. That makes the results easier to work with. Way one. How does flash fill work? Using flash fill is simple, but you'll need to let Excel know what you want. The first way is the simplest one. Move to sheet one. Here I have a small table with some names. This column is the flash fill result that we are going to implement, okay? Let's click on cell C three and enter what you want in the first cell. I don't want to extract the last name Parkins, so let's type it. Now, press control plus E to implement Flash Fill, which will come plet the list. That was really fast. Occasionally, you'll need to make two or three entries before Excel catches on. This first example is a good lesson on how flash fill works because the data is inconsistent. The results are inconsistent, as you can see. Flash Fill does its best. The problem is the inconsistent data, not flash fill. In this case, Flash Fill simply extracts the characters from the right to the left until it encounters the first space. That's the only pattern they can discern. Now that you know how to use Flash Fill, let's continue with some examples of how Flash Fill can help you work more productively. Let's move on to the second way. Way two. How do you use Flash Fill to extract text? I already clicked on S two. Let's suppose that you want to extract the first names from the source data in column B. To do so, enter Laura in C three. Now press Control plus E. As you can see, the source data still has an inconsistency, but this time the results are better than the first example. If you decide to change the source data, so it's consistent, don't forget to update the flash fill. You'll have to run it again. Way three. How do you use flash fill to combine text? Let's move on to the next sheet. In the last example, we extracted the first name from a cell that contains full names. You can also combine names separated into different cells. All right. Let's click on D three and type Parkins. Com space, Laura, Enter. Click again into D three and press Control E. By that time, Flashfll was able to complete the pattern and fill in the rest. Not only did Flashfll combine the names, but it also transposed them and added the comma. Way four. How do you use flash fill to change case? You might have noticed the name Alexis I B seven. It is still upper case. Sometimes you might not be able to clean up the source data. You might be working with linked data or perhaps there are just too many records to fool with. This is a great opportunity to put flash fill to work, and you could even replace the source data with the results you wanted. Let's enter Alexis I E seven. And then press control plus E. Yes, Flashfll can work up. You make the correction only once. Flashfll will fix them all once you show it the pattern. Wave five. How do you use flash field to extract numbers or text. Earlier, we use flash field to extract first names from cells that contain full names. Data doesn't have to be separated by space and doesn't have to be all text. In sheet five, I have column B with several strings that contain both numerical digits and alpha characters. To extract only the numbers, enter the numbers from the first two strings by site. Let's type 72 in cell C three and press Control E. Look at the message in this dialog box. Because the pattern is complex, flash film needs a couple of examples to pick it up. So I will type in C three, the number 72, and 819 and C four, and then press control plus fill in the remaining cells. Very cool. Now, let's delete the numbers and type ABF. Control plus E and Vo. This works just as easily with Alpha characters. Way six. How do you use Flashfll to extract and add? Don't stop with just one task because Flashfll can handle more than one change at a time. Here in worksheet six, we have some values, some big numbers. What do I want? I want Flashfll to add $1 symbol and the letter K to the digits. So let's add $80,000 into C three. Now, I use the power of flash fill to fill in the rest. Control. Notice, however, that the consistency issue created an error in C. It should be 1 million to maintain the pattern. Flash Fill can't do everything. These are just a few examples of how you can put flash fill to work for you. I've included a few monkey wrenches, so when it happens to you, you'll know why and how to fix the source data if possible to meet the consistency requirement. 51. (NEW 2024) How to Add a Condition to a Drop-Down List: You can use a microsoft Excel dropdown list to display a simple list. Though sometimes you need a bit more control. Let's suppose you have personnel scattered across four regions, north South east and west. You want to work with personnel by region, not all personnel. The situation like that requires two dropdowns, one that lets you specify the region and the second that displays only the staff in that region. Note that this technique works only with a matrix style dataset. I'm showing the simplest technique, but it's not the only technique. If you Google, you'll find complex and sometimes even convoluted solutions. Once we're done, you'll see that this solution isn't perfect, but it's super easy to implement. If it doesn't work for you, try embedding drop down controls instead. Here we have a simple matrix style type dataset listing representatives for each of the four regions. Notice that the region list is an alphabetical order from left to right. It's the only true limitation of this technique. Representatives are listed on each region, and some of those representatives are listed more than once. I did that on purpose because it makes no difference to the technique. We need two Excel drop down lists. The first Excel dropdown list will set the condition, the region. The second Excel dropdown list will display the representatives in that region. To the right, you see labels and formats in preparation for creating the Excel dropdown list. To create the region list, do the following. First of all, select h2s. Click the data tab and then click data validation in the Data Tools Group. From the allowed drop down, choose list. In source control, enter or select the cells from B two to two. Click Okay. With the conditional dropdown in place, it's time to create the dynamic dropdown that will display only the representatives from that region selected by the conditional dropdown. For instance, if you select self then the conditional dropdown, the second list will display Mary and Mike. Now, let's create the second dropdown. Select H three and repeat the steps above. Let's choose list from data validation in the source control enter. Equal, indirect H to an absolute reference and inside parentheses. The indirect function in Excel is used to create a reference specified by a text string. It enables us to create references from cell values. This function is one of the lookup and reference functions in Excel. The formula references the conditional dropdown in H two. Click Okay. Each time you change the region in the conditional dropdown, the rep dropdown updates accordingly. Remember that the conditional list of header text must be an alphabetical order. In addition, you'll notice the dropdown in figure has space for four items because the list contains blanks. If you deleted Mary, the first line would be blank, the second line would display Mc, and the third and fourth lines would still be blank. This is one of the things that you have to live with when you opt for an easy solution. For most of us, the two drop downs work well enough as it is. Okay. 52. (NEW 2025) Record your actions to create a Script: One thing you can do now here in all versions of Excel, including the desktop version here in Windows is record actions. You don't need to know coding to create scripts. All you have to do is turn on the recording, go through the steps, stomp recording, and the script will be created. That's what we're going to explore right now. I have a workbook open here. When I go up to file and select print with this particular workbook, you can see, by default, it's printing in portrait mode on an A four sheet of paper and it doesn't quite fit. I have these arrows down below, so I can see the rest of it across multiple pages. So one thing I might want to do is just change this up so that the page layout is wider than it is tall, change the size of the paper. These are actions you might have to repeat over and over. Instead of repeating them, you can record them, and then just play this script when you need it. So I'm going to before I start changing anything in this particular sheet, turn on the recordings by going up to automate. Then click New Script. And there it is. Record actions. By choosing record actions, you can simply record what you're doing, and the script will be coded for you. So we'll click Record Actions. Over here on the right hand side, a pain is going to open up telling you that you're about to record everything you do on this page. If you don't see this automate tab at the top, it's possible your subscription license does not allow for it, or you might have an administrator who's disabled it. But just so you can know this is how we do it. We go up on the automate new script, and then click Record Actions. Everything we do now is going to be recorded. So we'll go up to page layout. We'll go up to the orientation, click the dropdown and change it to landscape. We'll also change the paper size maybe from letter to legal, so it's even wider. You can see these things are being recorded here in the record actions pane. For the sake of time, we'll keep it short and sweet. Click on stop at this point. So everything that I just went through was applied to this worksheet. But a script was also recorded, so I can reuse it. We'll go up to where it says script and change the name to something more descriptive like Print wide. Press Enter, that locks it in. You can add a description if you want to be even more descriptive about what this script does, like changes to landscape using legal sized paper. Like so, press Enter and lock that in. All right. So we're ready to test this out. I'm going to close this up, the code editor, and now close up record actions. And when we go up to file and print, because we went through those steps, you can see how everything fits. Let's return the orientation to portrait. We're going to change the orientation by using our script this time. We're just going to go up to automate. Click the All scripts button. There it is. Print wide return in Office scripts. Clicking it and then clicking Run is going to apply to our current sheet. So all those other options you see here can be adjusted if you wanted to after running it. Let's see what happens when we go up to file and print now. On this sheet, you can see it's using the A four paper, and it is in landscape orientation. Everything seems to fit nicely on a single page this way. So we could go from worksheet to worksheet, running this script, not having to repeat all of the steps necessary to get it looking the way we want. At anytime, we can go in and edit. If you are into coding, clicking Edit will show you the code that was written based on recording your actions, something you don't need to know. Thanks to this record actions feature, now available here in the desktop version of Microsoft cell. Thank you for watching this video all the way to the end. I'm going to catch you in the next video. 53. 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. 54. (NEW 2023) Shrink your Pivot Data: Pivot Table Grouping Tool: When your Tata is getting bigger and bigger, then you need the powerful pivot table grouping tool. In this lesson, we're going to use it on a super long table. So let's pivot. PV PKP TP. P Shut Shut Let's take a look at this powerful pivot table tool. To demonstrate this, we have some data over on the left with our offices order dates and sales amounts. And from this, I've gone ahead and created a pivot table to the right of us to work with. Now, something to notice about this pivot table is that we're going to have all of our dates there as our column headings, which is making our table super long. Let's say that we actually want to summarize this by quarter instead of by date here up at the top. What we can do is leverage the Pivot table grouping tool. In order to access this tool, all you need to do first click on your Pivot table. And here I'm going to navigate over to one of the dates that we want to go in and update. And when we click on the dates, we can navigate to the group option. And when we click on the group there, there are a few different things that we can look at here. We can unclick Months and click on quarters in order to group it by quarters. The nice thing about some menu below here is that you can actually drill up or drill down as many layers as you'd like. So you can actually click on multiple different layers if you'd like, and you can unclick them to unselect them. You can also specify at the top in the auto selection where you want your dates to start and end. So this is a powerful tool if you only want to look at, let's say, one year of your data or only a few months of your data. You can go in and cap at the ends where you want it to be cut off. In our case here, I'm going to leave both of those checked on the default settings and simply click on quarters here in order to summarize our data by quarters and click. Now when we do this, as you can see, we'll take our pivot table and trick it down into the quarterly summary for each of our different row labels. A great use for this is if you have a large data set with tons of dates and you don't want this whole long pivot table. This is a great way to easily summarize the different date groupings. 55. 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. 56. 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. 57. 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 58. 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. 59. 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. 60. 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. 61. 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. 62. 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. 63. 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. 64. 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. 65. 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. 66. 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. 67. 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. 68. (NEW 2024) 5 WAYS to Generate Random Numbers in Excel: Today, we'll learn five different ways to generate random numbers in Excel. Using these random number generators, you can actually create your own datasets. So let's dive in. Okay, I have already opened the Excel practice file called random. I have a link in the description so you can download the same file and follow along with me. All right. The first way to generate random numbers is using a formula called RAND or RND. And all we need to do is to have open parentheses and close parentheses. Now, the catch here is that the number this generates is going to be a random number, random decimal 0-1. Watch what happens here. Excel is smart enough to know that we would want to do this for the entire table that we established here. I want to show you something else. These random numbers that were just generated, they're still alive. By that, I mean, if I was to click Return on this particular page on this particular cell, watch what happens. All the numbers are regenerated. So I can do this over and over again. And that may be something that you like, or it might be something that you don't like at all. So, the other thing that you can do, suppose I click Return one more time and you say, You know what? These are the random numbers that I want. What you can do is you can highlight these numbers. You can right click and hit Copy, and I can go either here or I can hit my Control C, but I hit Copy. Then what I'm going to do is I'm going to go on the same cell right here and I'm going to hit paste special and values. And there you go. These numbers are now actual numbers. You might say, Well, who wants a random number that's a big giant decimal? I want whole numbers. I want integers. So let's go ahead and do that here in this worksheet called Rand between. Let's generate random numbers for the invoice numbers and also for the sale amount. So in this case here, what I'm going to do is I'm going to type in a different formula. I'm going to use the Rand between. So you can see right here. I click on that and what could be your invoice numbers? Let's go ahead and do something 1000-1, and then we'll go all the way as high as 3,500. Let's see what happens here. All right. I generated a random number. Now, again, this number is still alive. Now, the other thing I want to do is I want to generate random numbers for this entire row. So what I'm going to do is I'm going to go ahead and drag this down using the little handle, and there are my invoice numbers. Once again, still alive. So what I can do is I can copy this. I can then go here. Once again, I can do a pay special values, and then these are now locked in as those values. If I want to go over here, I can say, Well, let's go ahead and do a sale amount. So I'm going to do again rand between, and in this case, I'm going to do values 0-1500. I'll close that up, and once again, if I want, I can double click here still alive. So what I want to do is again, copy this paste specials values. And since this is a sale amount, I can go up here, click on the dollar sign, and now this has turned these numbers into sale amounts in dollars. Now, notice here in the first worksheet, we had random numbers that were big giant long decimals. But here, all my random numbers were whole numbers. How about if I want something that is both a whole number and a random number together? Let's go ahead and do that. Click on the third worksheet. Let's actually go back to the second sheet and copy our invoice numbers from here. It's already done, and do that here. And the sale amount, I'm going to do equals. Now, I want a decimal, so I'm going to hit Rand, and that's going to give me the decimal portion of my random number. But I'm going to also add the Rand between. So there I go. And let's go ahead again. We can do any number we want. I want my sales to be anywhere between $100 and $5,000. Again, that first cell is still live. So when I decide to copy this down, you can see that it changed everything. Now, again, I can do a copy, pay special values, and you still may say to yourself, even if I turn this into dollar amounts, I know that there's these big giant long decimals attached to each one, and I don't really like that. So what we can do is we can round this. So I'm going to go round, and I'm just going to have standard roundings. Sometimes you can always round down, round up. I'm just going to do a standard rounding. I want to round this number right here, and I want to have two digits after the decimal. And what you'll notice first, copy this down. I'm going to now copy this. I'm going to paste the values where I want them to be. And you'll notice the long decimal is now gone, and these actually look like real sale amounts that I would probably trust. All right. Let's go to something a little bit different. Click on the Rand normal worksheet. In the world of statistics, we often talk about something called a normal distribution. You might say to yourself, I don't exactly know what a normal distribution is. Don't worry about that. We will get there. But for now, every once in a while, you may want to generate random numbers that follow more of a normal distribution. So what we can do here is notice we can go to the data menu. And we can find a Data Analysis tool. Now, notice it's not up here right now. To add this feature, you have to click first on the File tab and then options. From AddIs go to Excel AddIs. There you go. Check the Analysis tool pack box and press Okay. And here's the button you want, okay? Now, when I click on this, I can scroll through and there's a random number generation. And this can allow me to do both integers and decimals and all sorts of things that we've already done. But it also allows me to, let's say, how many variables do I want here? It's going to be one variable. That's going to be our exam scores, and the number of random scores I want is 100. And instead of them being discrete, I wanted to follow a normal distribution. I'm just going to click on normal here and you can see that there's a number of other options as well. But for now, we're going to do a normal distribution. I want the average of those random generated numbers, exam scores here. I want the average of those scores to be, let's say, 75%. And the standard deviation of those, let's say it's going to be about 15. And the output range, where do I want these numbers to go? I want them to be right here in B four. So it locks it in at B four. I think I'm all ready to go, and there you go. It generated random test scores, a little bit more than I had listed right there, but you can see. All right. Click on the last sheet. Let's say you wanted to generate a lot of random numbers all at one time. We also have a formula called rand array, and there it is. I'll click on this. And what this allows us to do is it asks us how many rows and columns of random numbers do you want? So I'm going to say, Well, we want 50 rows of random numbers, and how many columns? Well, we want to go from this column here all the way to there. So we want seven columns of random numbers. The smallest, my min number is going to be 25, and the largest number is going to be 100. Now, notice at the end, it's asking me, do I want this to be an integer? So do I want it to be a whole number 25, 26, 27, or do I want to have integers in there? Do I want this to be 35.734? Well, let's say I just want this to be integers, in which case, I would type in true. And you can notice here it's actually telling me what I can type in. So I go ahead and I type in true. And there you go. Now, if I wanted to, let's go back here. I can do the same thing again, Rand array. I'm going to do this for 100 rows. For those seven columns, smallest number is going to be zero, highest number is going to be 1,000. In this case, I do want decimals, so I click on false. So close the parentheses, and there you go. All right. So we found lots of different ways to generate random numbers in Excel. 69. (NEW 2024) Combine VLOOKUP and Pivot Tables to Calculate Payments: You and I have a business where we have sent people out to different locations, and they've complained about parking fees and tolls and everything else. And we've agreed to pay a stipend based on the location and help defray some of those costs when they go to these locations. We need to figure out how much to pay each person. First of all, Emilia works 7 hours at Harbor Center, $40 per hour. So we need to multiply the hours time the rate and then add on the $25 for Harbor Center. So in column, we want to create the formula for the entire column to calculate each rose pay and then use a pivot table to calculate how much each person should get. Let's dive in and get these people paid. I'm going to insert three more columns to make some room. First of all, let's just type pay and sell E one, and now click on Sell E two. We're going to multiply the rate times the hours. So I'm going to type equal D two multiply by B two. Right? And I'm going to put this whole thing into a table. Format as Table from the Home tab. Let's go with this blue. Cool. The range of data is from A one to G 22. And make sure that the box my table has headers is checked. Press. Highlight that E two cell and double click. Good. So now that's the pay. Now, let's get the stipend. Type stipend inside F one. Click in F two cell and start typing equals V Loop. Because we need to look at Harbor Center in this table and go to the other table to pull over the 25, going to look up location, so click C two, C. Where are we going to want to look for Harbor Center? We want to look for it on this other table. So I'm going to highlight that I two to J five, both in absolute parentheses. Okay. And now we want the second column. So Type two, and we want an exact match. False. Close parentheses, Enter. Beautiful. So now we have the correct stipends. Now we can add the pay and the stipend. Type final pay into G one. All right, cursor on G two equals pay plus the stipend. Enter. Very cool. So now we see milion needs to be paid these values. So we're going to handle that inside the pivot table. Is Tap pivot table. Yes. Let's put it on the new worksheet. Okay. We want employee names in rows Final pay in values. Let's make these dollars. Home dollars. This is exactly what we need to pay, and we can send this on to payroll. 70. 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. 71. Enter the Current Date or Time using NOW() Function: 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. 72. (NEW 2024) 3 Ways to Combine Cells in Excel: &, CONCATENATE, and TEXTJOIN: Sometimes in Excel, you'll want to take two cells to combine them together. Let's take a look at how to do this. Here we have three different ways to join your cells together. In this example, we're going to be joining the cells that we have in column C D and E inside of our cell in column B using different functions in column A. To get started here with this first one, we have the sine or ampersand. Now, you may be familiar in using the sign to mark it as the world. However, in Excel, the sign is like the glue stick. What we can do is use it in between the cells that we want to glue together as the glue. Let's test this out. If we click inside cell B three and type our equal sign to start off the function, the first thing that we need to do is select the first cell that we want to combine. In this case, it is cell C three. Next, if we want to combine that with cell D three, we need to remember to add that glue in between so that it sticks. So just go in and type the sign on your keyboard. And now we're going to put the cell reference for D three. Then lastly, to combine it again with cell E three, we can go in with that sign and type three. If we click Enter, it will go ahead and combine those cells. Now, that's great and all, but I need a space in between these different cells. What am I supposed to do with this giant word? Not to worry. Here's a trick to add spaces using this method. So all we need to do is, I'm going to start us over here. If we type equals, click on our first cell reference followed by the sign, and now we're going to join it into cell D. We're going to add a space in between the signs. Now, this is something incredibly important to remember across all of Excel. Whenever you are using a string of text inside of a function, we need to put that text in quotation marks. We can't just go in here and type of space like this or it will not work. We need to put a space in quotation marks. Now, similar to our zine gluing method, we're going to add in another zine here and stick it to our cell reference for D three. Same pattern here, and sign again to join it. Followed by our space and quotation marks and then our sign where we can click on E three. All right. Now, let's see what happens. If I click on Enter, all of those will be combined together with a space in between. Let's move on to the other two options that we have here that we can use to essentially join our cells together. This function is called concatenate or concat for short. What we can do here is if we type concat, as you can see, there are a few different arguments. It's essentially saying input text one, and then the dot dot dot is signaling you can go in and add additional pieces of text or cell references. In our case here, if we want to combine these three cells, all we have to do is highlight three of them and close our parentheses, then that we'll go ahead and combine them. Now, similar to what we just did before, let's say we want the spaces in between. We can use a similar method. So if I come back and type CCAT, and click on the first cell that we want to combine, I can now use a comma to move into the next argument. And instead of clicking on cell D five, we can put a space in quotation marks as our argument, and now we can insert D five, followed by another space in quotes. Followed by five, and that is the way with the concact function to combine your different cells together with spaces. Now, this third one here is my favorite, and it is the text join function. And what's really nice about the text join function is that it's actually an argument built in for you to specify the dliimter A K. What symbol you want to use between your different cell references. In order to use this, all we need to do is type equals text joining to our cell. And as you can see, there are three different arguments. The first one is our Dalimter. So this is where we tell Excel what symbol we want to use in between each of the cells that we are combining. Let's stick with the theme here and use the space again. We must remember that the space inside quotation marks as it is a string of text within our function. The second argument here is ignore empty. This is where we tell Excel what we wanted to do for highlighting an area that has empty cells in it. Do we want them included or do we want to exclude them? In this case here, we don't have any empty cells that we're working with, so I'm just going to type in true. Now we can go ahead and highlight different cells that we're going to combine. I'm going to close my parentheses here and click on Enter. And now we'll combine our three cells with space. There's many different uses for all three of these, and it's really just a user preference on which one you like the most. However, a great point about the text join is if you have an entire long list of items that you want to combine and you want to add that space in between, it is much simpler than the sine method or the concatenate method, as you won't have to manually go in and put in the sine inside of the quotes for each because text join does have that nice dliimter argument baked right in. 73. 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. 74. (NEW 2024) Copy Formulas Down a Column: It's not uncommon in Excel that when you work with formulas, you often need to copy formula down a column. And in this work she called copy down, we need to put in some formulas in column. We've got a compensation column in column D. In column, we're going to be calculating the new compensation. Everybody's going to get a 3.5% raise. Column E probably doesn't ultimately need to be this wide, but for the moment, let's make it wider so that we can see the formula better. There are multiple ways to write this formula. Certainly, one way is equal D two times this amount. Now, many of you know that if we're going to be copying this entire column down, we need to make sure that this F one remains constant or absolute. We can either manually put in dollar signs or simply press the function key F four. So if I were to press enter now, we would only have the amount of the increase, but we need to add that onto D two. And I'm not going to press enter because although that will complete the formula, it will move the active cell downwards, so I'll press control plus enter. And that is an accurate amount. And to be even a bit more precise about this, what I should do here is round this, so we don't have any trailing pennies. Let's imagine that the rule is we don't have any pennies in our yearly salary. So putting a com at the end and then zero, and we'll round that result. And there it is. Now, we want to copy this down the column. Using copy and paste techniques is going to take forever. We have only five rows, but imagine if we had 1,000 rows, and dragging it this way, if you pardon the pun, is going to be a real drag to go down to 1,000 rows. So what do we do? Double click on the lower right hand corner. That's referred to as the fill handle. And as you drag this over, that thicker plus turns into a thin plus. Just double click, and there it is. We're all set. So a real time saver, double clicking to copy a formula down a cell. 75. (NEW 2023) Meet the new FILTER() function: In this lesson, we're going to be looking at the new filter function in Excel. Let's see how it works. In this example, we're going to be looking at our filter function. Here in Column A, we have a list of our different office locations. In Column B, we have a list of the departments for each of those office locations. In Column C, we have how many employees are in each of those offices. So let's say we want to pull some of that data out for a particular city. In this case, we're going to be pulling data for the City of Miami. Now you might be thinking. Can we just put a filter on that raw data and filter out all of the offices besides Charlotte? The answer is yes. However, that is altering the original dataset. If you go over and look at it, you're no longer going to see the other offices. Let's say here we want to keep that dataset intact, but pull out the Miami data. What we can do here is leverage excels new filter function. To get started with the filter function, we can go into our cell here 11 and type equals filter. And here, there are three different arguments. The first argument is the array. So this is where you highlight the entire dataset that has the data that you want to pull. In this example, I'm just going to highlight our data from cells A two all the way down to C 80. Next, we specify the criteria that we want to tell Excel for what to pull. In this case here, we want every time the office says Miami for that data to be pulled out. What we can do here is highlight the column that has the criteria. So I'm going to highlight column A from A two through a 80. And then I'm going to type an equal sign and type Miami in quotation marks. Because remember, anytime that we have a string of text, we want to put those in quotes. Then the third argument here is if empty, this one is optional as it is in brackets, so we don't need it for this example. But essentially what it'll do is it'll allow you to tell Excel that you want to handle any empty cells in the output. In this case, I'm just going to close our parentheses. Now when we click Enter as you can see, it takes the data from Miami and pulls it into our table here. Now, I want to point out something. This is technically a data spill. And what that means is if I go ahead and click on any of these cells. Notice up in the formula bar does not say for any example here on the number 20 for cell G 13. It's still showing a great version of our filter function. So if you ever want to use this data and now reference it somewhere else, maybe we make a pivot table off of it. Good best practice is to highlight all of the data and do Control C on your keyboard to copy or right click and click Copy. And then a shortcut is control plus Alt plus V to bring up the pay special menu. And here on your pay special menu, we can paste these as values by typing the letter V on your keyboard and click. Now when we click here and to sell G 13, you will see the number 20 in the formula is these different data points are now actually inside of ourselves, and we can use them elsewhere. 76. Generate Random Decimal Numbers between 0 and 1 with RAND() Function: 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. 77. Add Comments Inside Formulas using N() Function: 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. 78. 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. 79. 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. 80. Convert Values between Measurement Systems using CONVERT() Function: 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. 81. Combine LEFT and FIND Functions: In this lesson, we're going to learn the left and find function. Also, we're going to learn how to combine the left and fine function to make our left functions more dynamic. So let's dive in. Now, first, I will demonstrate how the left function works. Let's say with our city and state column in column A, we want to extract just the city names inside of column B. What we can do here is click inside of our cell B two and then type equal sign to start our left function. And then when we type left, as you can see, there are two different arguments. The first argument is our text. So this is where we highlight text that we want Excel to be extracting from. You could also write out the text inside of quotes if you'd like. However, in order to make this dynamic, I would recommend using cell references. So in this case, if we want to extract the word San Diego from our Cell A too, what we can do is click on cell reference, and then the number for the characters. This is where you would specify how many characters long you want to extract, starting from the left. So here the words San Diego is nine characters long. And just to describe what characters are in Excel, this stands for either letters, numbers, spaces or any type of symbol that counts toward the character count. In this example here, the S in San Diego would be character one. The A is two, the N is three, and the space is four and then so on. So here, if we want to extract the word San Diego, I've put in the number nine here, and that will extract the word. However, let's say we wanted to drag this for all of our different cities. We can't be using the number nine as our character count number, or else it will cut off or include too many characters for each of these city names as they are all different lengths. What we can do here is the number of characters argument of the fine function. Instead of putting in that number like the number nine in this example, we can replace that with a fine function and have it find that the character count number of it and show how far to extract. So let's check this out. If I come back into our cell and do the left function of our text, I'm going to go in again and click on our cell here, A two. And for the number of characters, instead of typing the number nine, we're going to use a fine function. Now, what the fine function does is it will return a number to you based on what particular character you're looking up. So it'll tell you the character count number location. So, for example, if we were looking up the spaces in our San Diego cell, it would tell us that the space is character number four, because it is thereafter SN characters one, two, and three. We had that space as number four. In this case here, we're looking at that comma. So I'm putting a comma there in quotation marks. And next within that text, this is where we're going to go and Excel where we want to look for it. So here, for example, it's going to be cell A two again. And the start number argument there is optional is signified by those brackets. We don't need that one, for example. So I'm going to enclose my parentheses and show you what happens. When I clicked Enter, as you can see, it extracted the word San Diego. However, it did include that, and that's because the fine function is reading the character account number of them. In this case, let's say it's character number ten. It's pulling up until the tenth character. So let's have this adjust for every time it finds the character count number of the comma and then subtracts one. So we don't include the comma in our output. The trick here is I'm going to hop right back into the formula here and we're going to move back into our left function. And if you ever get confused when you're in Excel and you're like, Which function am I in? We got a function in a function. Always look at which one popped up over here on the screen and which argument is in bold. That will let you know which one you're actually in. So here's where we want to adjust our number of character arguments of the left function. We have our fine function here. That's pulling whatever character count number that comma is in. In this example, it's character number ten, and we want to add a minus one at the end. So it'll take wherever the comma is, pull that number, and subtract one for each of the different cities on our list. And now when we close out of our parentheses, it will pull that word San Diego. And when we copy it down, it will go in and adjust for each of our different city names. This is a great use case. If you have a list of particular items that you want to extract that are varying lengths, this is a great function combination to use. 82. Filling Random Values into Cell Selection using RANDBETWEEN() Function: 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. 83. Identifying and Fixing Error inside of an Excel Formula (Error Checking): 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. 84. Using the PDURATION() Function to Calculate Time: 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. 85. Round Values with Various Ways using ROUND() Function: 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. 86. 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. 87. Calculate a Person's Age using DATEDIF(): 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. 88. 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. 89. (NEW 2024) Convert Birth Date to an Age using Power Query or YEARFRAC() Function: In Microsoft Excel, you don't need an expression to calculate someone's age. Use power query to simplify the process. Calculating age requires a bit of a specialized knowledge. Any expression, you must understand date arithmetic and accommodate leap years. Years ago, the expression was much more difficult, but the XLSM format supports the Excel year frac function, which makes calculating age much easier. On the other hand, you can skip it all and use Excel power query instead. Power query adds the calculation automatically and you don't have to know the expression. In this video, I'll show you how to use year frac at the end of the sheet level and then how to use power query to calculate age. This video is really about power query because you don't need an expression to calculate someone's age. But in the interest of being comprehensive and offering alternatives for users not interested in using power query, I will show you how to use an expression at the sheet level. There are a number of expressions that calculate age. But if precision matters, use the following equal your frac, date of birth com now. Year frac function calculates the fraction of the year represented by the number of the whole days between the two dates using the form. Frac start date, end date basis. The optional basis argument identifies the type of day count to use, which requires a bit of explanation. Here is a table that explains the different type of day count basis. For our purpose, we omit the optional basis argument. Let's inside cell D three and enter the following function equal ear fraC C three, common now. Enter. As you can see, the function returns a decimal value that tells us how many days into the next birth year each person is. If you like, you can kill the decimals. Okay. Nice. This function is easy to use if you know about it. The biggest obstacle is knowing which argument to use. Now let's use power query, which takes the guesswork out of your hands. To avoid coming up with an expression that returns age turn to Excel power query. The first step is to get the data into power query. Let's click anywhere inside the dataset. All right. Now click the data tab. Then choose from table range in the get and transform data group. If the source data is not in a table object, Excel will prompt you to convert the data range into a table object. Check the M table has headers option and click Okay. Excel will load the data into the power query. Cool. Once the data, including the date of birth is in power query, you can add an age column, very easily. Select the date of birth column. To select it, simply click the header cell. Okay. Now click Add Column. In the from date and time group, click the date drop down, and let's choose age from the drop down. As you can see, the default format doesn't accommodate the age values we expected. To change the format, select the new age group and click Transform. In the date and time column group, click duration. From the drop down, choose Total Years. The age column now displays age as a whole number. Fantastic. The decimal values represent the fraction of the year until the next birthday. If you like, you can round the values as follows. Select the age column, click Transform. In the number column group. Click the rounding drop down. Finally, choose round down. This is the result of rounding down with age values. It's doubtful that you'll want to round up, but it is an option. At this point, you can load the data including the rounded He values into Excel, where you can use them as you would normally in any dataset in Excel. To do so, click the file tab and choose close and load in the close group. Here are the results of a new Excel sheet. The one thing that you lose is the decimal value portion in the He values. If you need that, don't round down in power query. 90. 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. 91. Create Table & 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. 92. 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. 93. Create Separate Worksheets from PivotTable using Filters Area: 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. 94. (NEW 2024) How to Create Infographics with Excel Add ins: Today, we'll learn how to use Excel add ins to enhance data visualization and engage the audience. These content add ins are part of Microsoft Excel and can enhance what you're already looking at. So let's go ahead and take a look at that. I have already opened this Excel file called data. Here I am in the data worksheet. The first thing I want to do is to make sure that your cursor is anywhere inside the dataset. Go up to Insert tab and we're going to insert a new pivot table on the new tab. Now, once we've created a new tab here, I'm going to go and call this add ins. This is just to keep organized, and I recommend that you do the same. So I'm going to grab mine, drag it all the way over to the right just to make sure I know that it's the most recent step I'm taking. So one thing that we haven't really looked at is the performance of each salesperson and trying to understand how they contribute to each activity. So what I'm going to do is I'm going to grab the salesperson ID here. I'm going to drop it into the rows. And then I'm going to grab Order ID here and drop it into the values. You can see right now it says sum of Order ID. This is not right. Order ID is a unique identifier. So instead, we want to go to the value field settings, which you can get to by clicking on the down arrow right here. We're going to click Value Field Settings, and then I'm going to select count. And when I'm ready, I'll go ahead and click Okay. Now the next thing I want to do is understand what is the total due related to those orders. So I can grab the total due from over here and drop that into the values column like that. That's going to give us the sum. And the next thing that I really want to do is because this is really just a list of numbers, it's always a good idea to sort one of these columns just so you can understand the distribution of the data. So in cell B four, I'm going to right click. I'll go to SRT over here and we're going to do a largest to smallest sort. Now, let's visualize this data with a Microsoft Excel add in. There are one of two places that you can find AddIs and this really all depends on the version of Excel that you have. So on my version, I can go to the Home tab, drag your mouse all the way over to the right and you will see the Addins button over here. Now, on your version, if you don't see any Add ins button on the Home tab, what you're going to need to do is to click on the Insert tab and you will see an addIs button somewhere in the middle here. So it's just going to be in one of those two places. So on mine, I'll click Get AddIs and then the add in that I want to insert is called People graph. So I'll type in People here and I'll hit Enter. And you can see People graph comes up. I'm going to click on this green Add button. And you can see that this content add in has now been added. So this add in starts with some sample data. And to change that data, what we'll want to do is click anywhere into the add in, and sometimes you need to click it again. But I'm going to click up here kind of in this white space, and you can see that it was very quick. But you can see there's a data button here. So I'll go ahead and click that. And that's going to allow me to change the data. So I'll click Select data here. And it says, Please select two columns in your Excel spreadsheet. It gives you an example right here. And what I'm going to do is I'm going to select A four and take it all the way down to B 53, and I'll hit Create. Now, this is giving us some data for all of our activities. So I'm going to grab status here and drop it into filters. And if I'm going to click Down arrow here, we can see that it's showing us everything. What I'm going to do instead is click Select multiple items. And what I am most interested in right now are the due paid and past due because that reflects money that could still come in. On the other hand, canceled and returned reflect transactions that are unlikely to be collected upon. So I'm going to remove those having selected select multiple items, and then I'm going to click Okay. And now I understand that salesperson 121, the person with that ID, has sold the most amount of items that are collectible. However, they might not have sold the most amount of total due, which is to say, from $1 standpoint, they might not have the most. Now, before we go any further, let's see if we can go ahead and change the name of this title. So I'm going to click over here on this dataset button again, and it says numbers about the app. I'm going to write collectible activities because this refers to transactions we can still collect on. And then I'll just click this button here. So let's go and take another look at data visualization to see if that provides a little bit more insight. Go ahead on your machine and click on that Get Addins button again. In this one, we're going to look up called bubbles. So I type in bubbles. Here, I hit Enter. You can see it shows up immediately. Let's click Add and then Okay. All right. I'm going to move some of this around a little bit. To get started, what you'll want to do is to move your mouse over to the select table bubble. You'll click on it. And now it's going to ask you for the data to visualize. In this case, we're going to look at everything all at once. So we'll select A four through C 53, like that. Go ahead and click Okay, and now we see a visualization that uses a mix of color and size. So on the one hand, the size is dictated by the count of order ID, whereas the color of each bubble is dictated by the total do. So as we look at this, we actually see that 144 is probably one of the bigger sizes, and it's more red as compared to a few others. Now, the Bubbles chart is very interesting and it's very fun. But as I said, in going through this, not every data visualization you'll look at is actually going to make the cut to the end. I present this to you because I think there's a lot of different ways to look at your data, and I think this one's very fun. Thank you for watching all the way to the end, and I'm going to catch you in the next video. 95. 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. 96. 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. 97. 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. 98. 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. 99. 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. 100. (NEW 2024) Automate Excel with Python: Python and Excel are both popular powerful tools for working with data. If you've ever had to shuffle your data back and forth between these tools, you know how much of a hassle that can be. Now, though, we can use Python directly inside of Excel, eliminating the need to move our data back and forth between tools. In this video, we're going to look at this exciting and powerful new feature of Excel. Let's open a new workbook. Python in Excel allows us to write Python code directly inside of an Excel workbook, leveraging the power of Python for data analysis and visualization without the hassle of moving our data between different sets of tools. If you have a family or personal subscription and are running the current channel, Python in Excel is available in preview. While Python in Excel is in Preview, you can add or edit Python formulas with premium Compute up to a limit. To gain full access to Premium Compute, you must enable the preview. You will find all the details from the following Microsoft page. Because this feature is in preview right now, what you see in this course may look a little bit different when you use it. Once the feature is available in your account, a Python item appears in the formulas ribbon. This item contains a few buttons, including one to insert Python into a cell, one to reset the Python environment, one to open the diagnostics panel, and one to change the initialization parameters for the Python environment. That appears over here on the right. I'll close this window for now, though. When the feature is enabled, the Pi function also becomes available to use in workbook sells. The Python environment that's used in this feature runs on Microsoft's Cloud, meaning that you don't have to deal with installing or configuring Python on your own computer at all. If you're already a Python developer, this also means that your local Python environment stays separate from the one that Excel uses. Because the Python environment runs in the Cloud, the same Python environment is available if you open your workbook on a different computer or if you send your Python Enabled Excel workbook to someone else who has Python feature Enabled. There's no need to worry that your Python code and Excel will work differently on different systems. The Python environment includes the Python Standard Library and many popular data science, machine learning, plotting and text manipulation libraries. Once the Python and Excel feature is available for your account, you can insert a Python cell using the button in the Python section of the formula's ribbon or by going to a cell and typing equals Pi opening parentheses. And the cell will switch to a Python cell. We could also use the keyboard shortcut Control plus Alt plus Shift plus P to create a Python shell. Notice that the interface changes a little bit. We see a Pi indicator here on the left of the cell. This indicates that this is a Python cell. Within the cell is a multi line editor where we can write our Python code. I can work inside here or I can click up in the formula bar to use that editor. I can switch back and forth between the cell and the formula bar with Control F two. We can enlarge this bar by clicking on the downward facing arrow on the right side, or we can toggle this larger view with Control Shift U. I write a very basic program here. All right, A equals three. B equals four. A plus B. Because this is a multi line editor and we use the Enter key to add new lines within it, we need to press Control Enter to commit the code to the cell. And when I do that, Excel runs the Python code in the cloud. So we'll see a busy indicator for a moment, and then we'll see the result of that Python program here in the cell. Recall that in my Python cell, I didn't write print A plus B or return A plus B. I just wrote A plus B, and here we see the result. The value we'll see in Excel is the result of evaluating the last line in the Python code in that cell. Kind of like how we would see a value when we run one line of code at a time in the Python REPL. We don't use return to send a value back to Excel. We just get the value of the last line in the cell. In our workbook, we can have more than one Python cell. The Python cells are evaluated in row major order. That's from left to right, top to bottom, and they all act as part of the same environment. So we can use one cell to set up imports, for example, and maybe another to set some variables we know we use in more than one place later on. And then we can access those items by calling them in later Python cells. I'll create another Python cell here, and I'll define another value. All right, C equals seven. And then I'll use one of the values from the previous cell. All right, C divided by B, I'll press Control plus Enter. And there's my result using the value from one Python cell in the next one. If we have Python in more than one worksheet in a workbook, the cells are evaluated in each worksheet left to right, following the tab order we see at the bottom of the screen. This gives us some flexibility in terms of using Python and Excel interchangeably as we work. But we do need to keep in mind how the cells are evaluated as we develop our worksheets and projects. We can't refer to a Python cell that's below or later than the current cell. So we need to think out our evaluation pipeline and arrange out work accordingly. For this reason, you'll likely want to put final plots or results or dashboard type summary items on the last sheet in your workbook or something like that. As an example of the order of evaluation here, I'll create either Python cell above the one that I just created. In there, I'll use the value C that I defined below. Type C plus two. And I get an error. At the time this cell is being evaluated, the variable I'm trying to use hasn't been defined yet. I'll delete this cell. By default, anytime a Python cell is updated or anytime a value in an Excel cell that's involved in a Python calculation is updated, that cell and all the other Python cells are also recalculated. But especially in large or complex worksheets or workbooks, this isn't always what we want. In the formulas written in the calculation section under the calculation options, we can change the mode from automatic to one of the options below it to perform a partial or manual calculation. The partial calculation mode will update Excel formulas automatically, but won't update Python or data tables until we tell Excel to calculate. And in manual mode, nothing will update automatically. We'll need to tell Excel to calculate before we see any changes. I'll switch to manual mode here in cell B four, so let's change B equals five. And we can see how that works. Notice that the value of the cell below it, which uses this value hasn't changed, and it's now stale. What you see for stale values may be slightly different than what's shown here. I can go to the formulas ribbon and click Calculate now, or I can press F nine to calculate. And now my value has been updated. That's a small sample. But if you have a large sheet that indicates a lot of computation, this option will keep Excel from wasting time recalculating when it may not need to. I'll switch my calculation mode back to automatic for this course, but decide for yourself which mode best suits your work. You spend a little while exploring the Python and Excel feature. Once Python for Excel is available for you, spend some time working with your own data and your own workflows. As a Python developer and an Excel teacher, I'm very excited about this new combination of tools. I hope you are, too. Thank you for watching this video all the way to the end, and I'm going to catch you in the next video. 101. 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. 102. 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. 103. 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. 104. AUTO OUTLINE: 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. 105. WATCH WINDOW TOOL: Monitoring the Value in a Worksheet: 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. 106. 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. 107. 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. 108. 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. 109. CONDITIONAL FORMATTING: Compare Two Lists of Items and Identify Differences: 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. 110. 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. 111. A bit of light-hearted fun watch this 1992 MS Excel advertisement: Hey. Hey, Greg, buddy. Saw Wilson. You're showing her the projections for the new vacation packages this morning. I am. You're joking, right? You're such a kidder. Tell me you did it. Come on. Don't do this to me. It's just one little spreadsheet. This could make or break the deal. Her exact words were 9:00 A.M. Or never. Calm down. Just give me a minute. I could. What is this? It's Microsoft Excel. Now, hold still. I'm about to perform a miracle. You're crazy. There's no time. Now, Wilson just wants this year, right? First, we'll put in the names of the tours. Tennis, golf. Safari. Total. Right. Ought to do it. She wants to see a 10% growth. Yeah yeah yeah. I'll work out the first two quarters. I got it. I got it. We could tell her that you got mugged on Friday night. She'd want a police reporter. Click on the autosum. You only have two quarters here, aren't you going to finish it? No. The Microsoft Excel is. Watch this. It's called autofill. It's going to finish the table for me. I just drag this little cross as far as I want the data extended. Check it out. Excel picks up on trends, totals, quarters, dates, and does a projections for me. My spread sheet doesn't do that. Very cool. Auto fail. My career is over gone. Look, you're not going to make. Not unless I make room for this title. What? Start all over. Don't panic. I'm just going to drag the table over here and make room for it. Hey. Hey, did you just drag that? My spreadsheet doesn't do that. Okay, watch this. At that title. A little bigger. Centered. It still doesn't look right. Give it up, man. Look, let's just say your PC got run over by a bus, and you're lucky to be alive. Whoa, I better format those numbers. Click on the table, click the right mouse button, get the shortcut menu for numbers. We want currency formats. This one looks good. My spread sheet doesn't do that. Okay. I admit it. That was pretty neat, but you'll never finish it. This isn't good enough to present. You can't show her this. Oh, Ye of Little Faith. I really think she'd buy the bus store. With a click on the Auto format, our superhero shops through several professionally designed formats and applies one. That one. With a click of Amighty Mouth. I can't believe it. You did it. We did it. It looks great. This is a miracle. This is Microsoft Excel. I took a minute to work up those figures you wanted. My spreadsheet doesn't do that. What was that? 112. Introduction to Excel 2019: Hello, and welcome to this introductory Excel tutorial. This lesson is an overview of Excel's basic concepts that you should know before learning more advanced techniques. When you open Excel, you'll see a blank workbook with a worksheet in it. The very first thing I want to show you are the areas of Excel and how they are divided. The top area is called the menu bar and the ribbons with tab. Rribbons give me more space to various things in Excel and clearly spell everything with words like conditional formatting, find and select et cetera. All the important things that we commonly use are in the home ribbon. Similarly, there are other ribbons, too, like Insert, page layout, formula, et cetera. The second thing is formula and name box. If you type any formula in cell, this is where you would see it. The majority of the Excel screen is given to the worksheet grid where you have numbers for rows and alphabets for the columns. Each cell in Excel has a unique address like B two, which is row two in column B. The last area in Excel is the status bar. Some of Excel's users complain that the ribbon takes up a lot of space. If you want to save up space and increase the worksheet grid area, you can double click on any of the titles like Home insert, et cetera, and Excel will collapse the ribbon. If you double click again, it will be expanded. If you single click on it once, you will see the ribbon momentarily, but it'll be gone once you finish whatever you need from the ribbon. You can also use Control F one, which will turn the ribbon on and off. You can use that arrow button to collapse the ribbon. Another thing that you will see is the save undo and redo buttons right at the top of the screen. This is called the Quick Access Toolbar. You can add anything that you want to access very quickly to this tool bar from the down menu. For example, let's suppose you often use the center alignment button. You can add the button to the Quick Access toolbar so that it is easy available for you to use always. To do this, right click on the button and select Add to Quick Access Toolbar. By clicking on the file tab, you can see the various menu options like opening a file, saving a file, printing a file, closing Excel, et cetera. Excel comes in a very beautiful way to access everything that is out there in the ribbon. Select any cell and hold down the Alt key momentarily. You will see that Excel shows you some shortcuts. For example, if you press H, you'll activate the home ribbon. Excel will show further shortcuts like C for copy, one for bold, etcetera. It's a very popular way to learn shortcuts that are available. Press escape to go back one step. Also, when you press lt, all the things in the quick access tool bar are assigned default shortcuts of one, two, three, four, and so on. It is a very easy way to access things that you want very quickly. Escape again. The name box tells you where you are in the worksheet. If you select D five, you'll see column D and row five highlighted. You can select a bunch of cells that'll tell you what the top left cell is. If you are in D five and want to go to F, you can type 30 in the name box and it'll take you there. Now, let's open this Excel workbook with some data inside. Select the titles and make them bold, align them to the middle and add a fill color. Highlight all the data and simply add a border around it. Click on the second sheet. As you can see, we have a similar table. Now we need to do the same formatting steps again, but we hate doing that because it's repetitive. Instead, we can select cells in the first table and click on the format painter button and select the cells of the second table. The format gets copied to the second table instantly, saving you the task of repeating the steps from the second table. But what if we have more similar data, maybe five more tables. So instead of clicking the format painter, we can double click on the format painter and it locks the mode. So you can repetitively use it and Excel will apply the format. When you're done, press escape to release the lock mode. Go to a blank sheet again. From serve tab, you can create a table from this button, or you can click on the Smart Art from the hierarchy, and you can create a nice organization chart. Now, create a new sheet in A one cell type. Go to organization chart. We want to add a link to this phrase in A one cell. Right click on it. Link, click on pace in this document, and select sheet one. Press and click on this link. Excel is going to take this particular location. This is one way to use the hyperlink functionality from the Insert ribbon. Let's go to sheet one and click on any cell. To add symbols like arrows, et cetera, from the Insert tab, click on the symbol button. There are some fonts like or that have only symbols. Click on this and press Insert. Remember that if you change the font, the symbols may not work properly because the symbols may have been defined for those fonts. Okay. Return to data table. Suppose you want to see the names and columns in sales and rows. Would we type the data again? No. Instead, we can select the data, copy it, and come to any open area, and select paste and then transpose. You can always fix the alignment if it gets messed up. Now, select the new data table and press clear all buttons. Alternatively, you can also clear formats alone and set. Select this data table. When you select the cells, you'll see a thick black border around them. If you place your mouse on the edges, you'll see a mouse pointer change to a double arrow. When it is there, hold the control key and drag to make copies. Instead, if you place the mouse on the edge and then drag, it'll move the data. If you want to use the keyboard, you can press control X and then control V to move the data. Similarly, to make copies, we use control C and Control V. Both these techniques work and save a lot of time. Let's talk about dates. To get today's date, I can type equal today open parenthesis. You'll see today's date. But if you open the worksheet tomorrow, you'll see tomorrow's date. Let's type tomorrow's date. Uh, Select the first two dates and place your mouse at the bottom edge till it changes to a plus sign and then drag. You're telling Excel to fill the dates for you instead of having to type them. Excel does it in a very smart way, and it works forward numbers either. If you type five and ten and both select and drag, Excel will nicely increment the values by five each. This is how you can use auto fill techniques to save time and do things faster. You can select from a variety of options like long date. If you select long date, you might now notice a bunch of hash marks in the cell because now the text is too long to fit in a cell. To fix this, you can select the entire column and double click on the header line of these two columns. The column width will now get adjusted automatically. Keep in mind that even though we are typing dates from Excel's point of view, everything is a number. Let me type one in a cell. If I select that and ask Excel to show it as a date, Excel shows me first January 19 hundred. So all dates start from first January 19 hundred. Another trick is that if I type control plus semicolon in a cell, I'll get today's date. To print these data, I can click on the file tab and print button. From here, you can see the preview of how it'll look when it is printed. Sometimes we'll see our content split over multiple pages. This can be fixed by selecting fit sheet on one page. If I click on the page set up here, you can choose to print in landscape orientation. You can go to the margins tab and tell Excel to print it so that the content is centered horizontally and vertically on the page. Okay. Close the window and click this arrow to return to Excel main page. You can turn the grid lines off from the view menu and uncheck the gridlines box. Similarly, you can turn off other things. For example, you can choose not to show the formula bar and disable headings. These are the standard ribbons, but there are some advanced ribbons as well, one of which is the developer ribbon, where we can use some of the options like recording macros, inserting form columns, etc, One of the things that we commonly see when we have a lot of data and we want to put only a portion of the data to focus. You can select the data. Go to V tab and click Zoom to the selection button. Excel will zoom it to a large extent that only these things are visible. Everything else is still there, but they are beyond the scroll area. Restore to a 100% like this. Before we close the workbook, we must save the file. So from the file tab, click on the save file and point it to a location. I will choose desktop. We're saved our first file, and we've learned some interesting things in Excel. I hope this has given you a good overview of Excel. Thank you very much for your time. Bye.