Then scroll to get to the place that you want to go. You don't have to do this if you have a bookmark for this exact location. How can you do that? Well, you just have to use this little box here. This is a box that you probably look at every single day but had no real use case for it, unless you're an Excel Pro, then you're probably using this. But if you haven't used it so far, you can use it now as a bookmark. Just select your cell and then go to the Name Box and type.
In a name for your bookmark. So, I'm going to call it "BookReport." You can't have any spaces here, then press Enter. Now, it does help if you use some type of naming convention so all your bookmarks start with something that you easily recognize because this Name Box also includes any tables that you might have in your workbook, and you might be using the Name Manager for other purposes as well. So, now we have a bookmark for this cell. What does this mean? It means that we.
Can be anywhere in our workbook, and all we have to do is click on this dropdown to jump to our bookmark. You can also just type in the name of the bookmark. So, here is "BookReport." I'm going to type it in, press Enter, and I jump directly here. This is a cool way of using this Name Box, which creates names in Name Manager. So, if at any point in time you want to delete a bookmark, you're going to go to Formulas > Name Manager. You're going to see your bookmark here. Click on.
Delete, OK, and that bookmark is gone. A very cool trick to navigate larger workbooks really fast. Next up is Analyze Data. This is formerly known as Ideas. Now, this is your shortcut to get answers from your data, and it's based on AI. Let's just do this quick thing together. Let's say you just received this data set, right? It's the first time you see it. You have Month; we have Product; we have Total Sales, Units Sold, and Customer Ratings for each product and.
Products with the lowest customer ratings," press Enter. "Here's our Answer - Showing bottom 5 in 'Product'..." That's the Product column "...by average 'Customer Ratings.'" I got the average ratings, and it filtered it to the bottom five. So, all I need to do is insert a PivotTable, and I get my answer on a separate sheet. I can adjust the PivotTable settings if I want from the PivotTable Fields menu here. If I want to find out the detail for this first one, which has the lowest ratings,.
I'm just going to double-click, and it's going to create a new sheet with all the details. Now, this here is a standard PivotTable feature, but the great thing is we didn't even have to know how to create a PivotTable. All I had to do was go to Home and click on Analyze Data, and the rest was just a click or two away. It's kind of like having a mini data analyst right in your spreadsheet. Next up is One-Click Forecasting. This is a lifesaver for anyone dealing with sales or.
Revenue data. Just imagine being able to forecast your future sales. Well, you don't have to imagine that because we're going to do it together right now. So here, I have Bicycle Helmet Sales data monthly starting from 2021 all the way to June 2023. Let's say I want to forecast my sales for the next year and a half. All I have to do is select my data set, I'm going to press Ctrl + A, go to Data > Forecast Sheet. That's it. My future sales data is automatically forecasted,.
And it accounts for seasonality as well. Now, it's forecasting a lot of future periods, but I can adjust that right here. So, for Forecast End, I can click on this calendar picker and just adjust it to the end of December. Now, under Options, we have the Confidence Interval. It's set to 95%. This means that 95% of my future data points are going to fall somewhere between this lower bound and the upper bound right here. Seasonality is set to detect automatically, but you can also set it.
Manually as well. Now, once you're happy with what you see here, just click on Create, and there you go. You have your chart with future sales data forecasted right here. You can make adjustments as you need to this. This is a standard Excel chart. Update anything you want under Formatting or under Chart Design. The great thing about this is that you automatically get a table that goes along with this chart. You can see the lower confidence bound and the upper confidence bound.
- Design A Responsive Homepage With Full Screen Video Background - Part 2 BlogSpot
- 900+ HQ Stock Images Free Download Photo Stock Mega Bundle Sheri Sk
- Learn Windows 10, New Features, Tips and Tricks
Of your future months. So, you can also make any adjustments that you want directly to this table, - ID Card Make
And it's going to reflect automatically in your chart. The next time-saver is the Quick Analysis Tool. This one is like having your own personal Excel assistant. So, let's say I have units sold for different products, and I want to highlight the values that are above 500, and I want to do it really fast. What I'm going to do is press Ctrl + A, then Ctrl + Q to bring up the Quick Analysis Tool..Right now, we can see all the different options we have. We could quickly add sparklines, turn this into a table if you want, add totals for sum, average, count, and so on. We could add a chart if you want. So if I hover over this with my mouse, you can see the different charts that we can immediately insert with just a click of the mouse button. If I want to do some formatting, so apply some conditional formatting, I'm going to go to Formatting. If you want to use your shortcut keys,.
You can press Tab, and then you can see how things look if you added data bars to this, if you added a color scale to this, I can set and so on. All of this is a bit too dramatic for me. So, let's say I just want to highlight specific values. I'm interested in months and products where my unit sold value is above 500. So, I'm going to go to Greater Than, press Enter, change this to 500, and adjust the color that I want to use. So, I'll go with green fill. You can also use your own.
Custom format if you want, but I'll go with green fill in this case and click on OK. My conditional formatting is automatically applied. Now, what if I wanted to add sparklines to this? I'm going to do Ctrl + A, Ctrl + Q again. You don't even have to press Ctrl + Q. If you come to the side and you click on this little icon, this menu is going to pop up. Let's go to Sparklines and add a Line Sparkline. Now, we can quickly see the monthly trend. If you wanted to add running totals to this, again, let's.
Just highlight. This time I'm going to go down and click. Go to Totals and select Running Total. And automatically, we get a running total added. So, check this out. Here we get the sum of January, and here we have the sum of January all the way to March. For February, it's just January and February. All of these calculations and visuals are just a click away. Last but not least, Power Query. You see, Power Query is the ultimate time-saver, and I had to come on camera for this.
One because I feel so strongly about it. It's not a tool that's reserved for geeky people. It's a tool for anyone that works with data, data from different places, or messy data, which is practically what anyone using Excel deals with. You see, Power Query is in the Data tab in the ribbon, so you already have it. And if you want to import data from text or CSV, you just have to come here. You want to grab data from the web, from a web page, just click on this. You want.
To grab data from a picture, just use this, or grab data from a PDF file, you can do it from here. Now, I have separate videos on these, but in this example, I want to show you one concrete case. And if you have your data set up like this, you are going to feel the relief that comes with using Power Query. So, imagine this. Imagine you go to the office, and your boss gives you this file. You have monthly data, so we have the date here, but basically, these are just placeholders.
For the first of the month for different products and their total sales. And your boss tells you, "Give me this data in this format." Basically, we want to have the months in the columns. So, you take a look at this data set, and you see, "Okay, we have over 400 rows of data. How are you going to go about arranging the data to get it in that format? Are you going to first replace these with the name of the month and then grab the data and copy it over to the right column? Is that the.
Way you're going to go about it? Or if you know Power Query, you are going to do this with less than 10 clicks." Okay, so here comes click number one. We're going to go and select From Table Range. This is going to send our data to Power Query. Click number two is to go and add a column. Go to Date. That's three clicks. Month. Name of Month. Okay, so that's four clicks so far. Now, I'm just going to select the Month column to delete it. That's five clicks. Now, let's.
Select Month Name. Six clicks. Go to Transform. Seven clicks. Pivot Column. Eight clicks. We just
Have to select which column is our Values column. This is the Total Sales column, so I think we're at eight clicks or nine clicks. I'm losing count, but let's say, okay, nine clicks. Click on OK, and that's it. We've done it. Let's do our 10th click to send the data back to Excel. I'm just going to click on Close & Load, and now we have our data formatted as a matrix by month. That's 10 or less.Than 10 clicks to go from here to here. How cool is that? Now, there's so much that Power Query can do, and if you want to learn it in a structured way, we have a complete course called "Master Excel Power Query: Beginner to Pro." Now, just before I recorded this video, I went to look how many students we have, and I was shocked, 51,323 learners, including our corporate customers learning Power Query from us. I am super grateful to have so many people trust us teaching them this.
Beautiful, wonderful, very useful skill. This is a very comprehensive course. It's over 18 hours of video, and it comes with a lot of bonuses, including two very comprehensive downloadable eBooks. The list of topics you can see here, we have a lot of challenges and quizzes and best practices because it's very important to test your knowledge on different sets of exercises and challenges and data sets. Because we have a lot of topics here, we've also created a Transporter.
For you. This is a topic transporter where you can search based on keywords and tags. This way, you can quickly find the lecture that you want and be automatically transported to that lecture. So, for example, let's say you want to take a look at the Pivot Column technique, right? Something that we just covered. You can just search for it, and then take a look at all the places that we cover the Pivot Columns. Right? So here you can see the tags, and here are the lectures. Now,.
Let's just jump up here. Let's say you come to the Advanced Pivot Techniques, and you want to be transported here. You just click on this, and you're immediately taken to the lecture here. We also have subtitles for all of our videos, and in case something doesn't make sense and you're stuck, you can just ask a comment. Put in your comment here, and you're going to get help from our friendly teaching assistants. Whether you learn Power Query from our course or from one of.
These YouTube videos, just remember it's a major time-saver that can make your data life a whole lot easier. So, don't miss out leveraging its power. All right, so that wraps up our deep dive into Excel time-savers. I hope these tips help you reclaim some of your valuable time. If you found this video helpful, go ahead and hit that Like button, and don't forget to subscribe for more awesome content and share it with your Excel friends and colleagues. Thank you so much for.
