# Stop Scrolling! 4 Pro Ways to Navigate Excel ## Метаданные - **Канал:** Leila Gharani - **YouTube:** https://www.youtube.com/watch?v=pIh0dzyz2bw ## Содержание ### [0:00](https://www.youtube.com/watch?v=pIh0dzyz2bw) Segment 1 (00:00 - 05:00) Where is this thing? Large spreadsheets are usually a sign of great work. They show the depth of your analysis, but they can be a nightmare to navigate. Imagine you're in a meeting and someone is like, "Go to cost center 3300. " And you're scrolling and scrolling. Uh yeah, this can feel unprofessional. It can add to your anxiety. So, let me show you four simple ways to navigate large Excel file. We're going to start with a hidden menu that's been there forever to a fully clickable dashboard where everyone is going to be like, "Wait, did you build that? " The first one is the secret right click. So, this first method has been around forever. It took me 10 years to notice it. Right click on the navigation arrows left to your tabs, and you're going to see a list of all your sheets. Select the one you want. Press enter or okay this and you're going to jump right there. Now if you want to move around from here you just need to use the shortcut key Ctrl + page up to move down and Ctrl + page down to move forward. Easy. Number two is the modern navigation pane and this is available in Microsoft 365. All you need to do go to view navigation and you're going to see a list of all your sheets and their content directly here. You want to jump to a sheet. Just select one and you'll be right there. You can also search for a sheet. So if I type in ST, I can see United States. Select it and I'll jump right there. Now, you can also right mouse click on a sheet and rename it, delete it, or hide it directly from here. There's also a secret door for this one. So, let me close this. Go to the status bar, right mouse click, select sheet number. Okay, now this is checked. It's going to add the sheet number to this. If you click this, the navigation bar opens. This next one is my favorite and honestly it's the one that gets reactions in meetings because it shows that you know your stuff and that you care about how others experience your work. The interactive navigation dashboard. So notice here I have the cost center report for different cost centers. We have quality assurance, sales, marketing and so on. And like any good report, I've added a cost center summary over here where we pull in information for the different cost centers on budget and actual from the different sheets. Now, this is already good to have, but I can pimp it up even more by turning these codes into a navigation bar. All I need to do is add hyperlinks to them. So, I'm going to select the first one. Right mouse click, link. Jump to place in this document. Find a sheet that you want to link to. In this case, we want to jump to the cost center 1000. So, select that. You can jump to a specific cell reference. By default, we have A1, but my headers are in B2. So, I'm going to switch that to B2. Press enter. And now, we've created a hyperlink. So, all we need to do is click on this and we are right there. Now, let me just repeat for the next ones really quickly. A few moments later. And here we have it. A really neat navigation sidebar. Just click one of these and you're transported to that sheet. Now, it would be cool to add a way to get back to the summary sheet from each of these. So, here's how you can do this really quickly. Let's go to the first cost center. I'm going to add in the home emoji and let's just put summary here. I'm just doing it on this sheet. Okay, let's right mouse click link and select the cost center summary and okay, this I'm going to copy this to all the sheets in one go. Let's copy the cell. Ctrl + C. Select the next sheet. Hold down the shift key and go to the last cost center. Just make sure that all the sheets in between are also selected. Go to the cell that you want this copied. I think it was in H. And CtrlV this. Now we can jump back to the summary in one go. Let's just unselect or ungroup sheets. I'm going to click this and I'm transported to my summary sheet. Now, you can also create a more elaborate dashboard looking version of this that I think you'll love. So, let me show you that for the different countries. Let's just scroll back to the first sheet. Control and leftclick. So, here I have my performance tracker for the stores in different countries. I also have created a summary sheet. This time, instead of linking each single country, let's add ### [5:00](https://www.youtube.com/watch?v=pIh0dzyz2bw&t=300s) Segment 2 (05:00 - 09:00) a really neat navigation bar that has the flags of the different countries. Let me expand this column so we make space for the flags. And let's add a neat background color to this. I'm going to go with this dark gray. Okay. So, I already have images of the flags that I want to insert. So, let's go to insert illustrations, pictures, place over cells, this device. The flags are right here. I want to select them all. Press Ctrl+ A and insert. Now, with all of these selected, I'm just going to make them all a little bit smaller. Now, let's bring the last flag and put it down here. The first one should be somewhere here. Let's quickly rearrange these. I want to be able to select them like I select objects in PowerPoint. To toggle that option on, I'm going to go to home, find and select, select objects. Now, let's do what we do in PowerPoint. Let's just drag to select these. Let's quickly align them. Picture format, align options, distribute vertically. Let's go back to align and align center. Okay, so this is looking good. Let me use the arrow keys so that I can place them right in the middle here. Okay, so that's good. Now, our select objects toggle is still on. Notice I can't click into any cell. I just need to press escape to toggle it off. Now I'm going to add hyperlinks to each of these. Select the first one for the UK. Right mouse click link. Place in this document United Kingdom and okay. Now let me quickly apply hyperlinks to the others as well. Okay. So now all hyperlinks are added. Notice when I hover over these I get the hand icon. I click on one and I'm automatically transported to that country's report. Right? So that's just 90 seconds of setup and suddenly your spreadsheet looks like an app. Excel has a lot of neat functionality, but let's be real. Many of us don't learn Excel for fun. You might be learning it because better skills lead to better work and better work leads to better opportunities. That's why I built XelPlus. Our courses teach smart spreadsheet design, pivot tables, functions, charts, dashboards, automation, even Power BI and Python in Excel. Basically, everything you need to become the person who actually knows what they're doing. And if you're a manager, think about what changes when your whole team works at that level. We have teams at Fortune 500 companies and government agencies that use our courses for exactly that reason. After all, time saved is money saved. If you're interested, check the link in the description. If you're looking for team licenses, there is a link there, too. Our most popular with business customers are our bundles. We have the essentials, the power bundle, which includes Power Query, and Black Belt, which includes it all. So, now let's jump back. The last technique is a real slick one, the name box bookmark. Let's say the UK is an important subsidiary and we want to be able to quickly navigate to this sheet no matter where we are. We can use the name box and give a cell and here a name. So let's say I want to jump to the header here. I'm just going to put UK here in the name box directly and press enter. Okay, just make sure you don't add spaces here. You don't have any symbols and stuff in there. You can use the underscore to connect things together. Now let's also say that I am responsible for this cost center. So this is finance and accounting. I'm going to put CCF for finance. Right? So just type in and press enter. Now the cool thing is that no matter where you are in this document, you just need to type in UK and you're transported to that sheet. Or you can click on this dropdown and select the name and you're transported there as well. Okay, so this is a really neat trick that pros like. If you ever want to get rid of these or modify these, you have to go to the name manager. So go to formulas, defined names, name manager, you're going to see your names right here. You can delete them or modify them as you need. So try these out and let me know in the comments which ones stuck. the secret right-click trick, the modern navigation, the bookmarks, or are you going to be building that sidebar navigation dashboard? I hope you enjoyed this one, and I'm going to catch you in the next video. --- *Источник: https://ekstraktznaniy.ru/video/27579*