Easily navigate a spreadsheet
Learn how to navigate a spreadsheet in Excel 2010
Welcome to Business Productivity, I’m Ulrika Hedlund. Excel is a wonderful tool for analyzing and reviewing data. But sometimes if you have a very large spreadsheet it can be quite difficult to get a good overview of all your columns and rows and easily navigate between different worksheets.
In this video I’ll show you how you can easily navigate a large spreadsheet. I’ll show you how you can zoom in and out to get a better overview of your numbers, how you can freeze columns and rows so that you always see the headings even if you’re scrolling down, and finally I’ll show you how you can open multiple windows to compare worksheets side by side. Let’s have a look!
Stepping through worksheets (00:46)
Here I have our Marketing Budget for the years 2009-2012. As you can see I have separate worksheets for each year. I can easily step through the various budgets by just clicking on the appropriate tab, or I can right-click anywhere here in the arrow-section and just select the year I want.
I can also use the keyboard shortcuts to quickly switch between the different sheets. To do that I’ll hold down the Control key and then I’ll press Page Down to go to the right and Page up to step to the left.
Minimize the menu to see more of the spreadsheet (01:26)
In order to see more of the spreadsheet I can choose to minimize the menus. To do that, I’ll just double-click the Home tab. If I want to access the menu again I can just click on the Home tab and the menu will float above my spreadsheet. If I want to dock it, like it was before I’ll just double click the Home tab again.
Zooming in and out (01:47)
I can also use the zoom slider here in the bottom right corner in order to zoom in and out to show more of my spreadsheet.
There! Now I can see all of the columns in my budget. Say now that I want to focus in on a specific area of this budget, for example I want to look at personnel costs the first quarter of 2009. To do that I’ll mark the range of cells, go to the View tab and then say; ”Zoom to selection”.
Now I get a very focused view on those particular numbers. To zoom back again I can just click 100%. Or go to the zoom slider again.
Freezing rows and columns (02:32)
As you can see, this budget has a large number of rows and when I scroll down I lose track of which column is for which month. If I go to the View tab, under the Window section I can select to Freeze panes, so here I have the option to freeze the top row.
But in my case this doesn’t really help because I want to have the names of the columns and that’s on the 4th row. So I’ll unfreeze the pane again and then in order to freeze the 4th row, what I’ll do is that I’ll mark the row underneath, go to Freeze panes again and select Freeze panes.
Now you can see that that the 4th row has been frozen and I can scroll and still see which column belongs to which month. To unfreeze the row, I’ll just click Unfreeze panes.
Now the same thing goes if I scroll to the right. I lose track of the column with the budget posts. So if I want to freeze this column, I mark the column to the right and again I select Freeze panes. Now I can scroll to the right without losing track of the budget posts.
Well, say that I want to freeze both the row that has the column names, and also this column, with the budget posts. In order to do that, mark the cell to the right of the column and underneath the row you want to freeze and select Freeze panes.
This way Excel will freeze both the row and the column and now you can scrolldown and to the right and still keep track of your headings.
Splitting the window to compare rows and columns (04:14)
Now I’d like to compare the budget for the month of January to the month of December. Now to easier see these columns side by side I’m going to split my window into multiple panes. To do that, go to the view tab and click Split.
As you can see my window has now been split into four different panes. I’m going to move the pane so that I can see the month of January in one, and then I’m going to scroll down so that I can see December and January next to each other.
Now it’s much easier for me to scroll down and compare January and December side by side. To remove the splits I simply click split and the splits disappear. Another way to split your screen is by moving this little handle bar.
To do that, hold down the left mouse key and simply drag it to where you want to position it. And then you can do the same with the horizontal bar. To remove the bars, just double click, and they disappear.
Hiding columns, rows and sheets (05:16)
Quite often in Excel, you add columns and rows for your own reference that you don’t necessarily want others to see. So here I have a column where I’m just calculating the increase since 2009 and since I don’t want this to be visible in my report I can select to hide this column. To hide a column, mark the column and then right-click and select Hide.
Another way to do it is to mark a column, go to the Home tab, click Format, and then under Hide & Unhide select Hide Columns.
You can see that the column is hidden because there’s a missing letter here between O and Q. If you hide a row you can see a gap in the numbering.
Sometimes it can be quite hard to spot if you have hidden rows or columns in your spreadsheet. If you want to get a good overview of hidden columns and cells you can click Find & Select, Go To Special… and then select “Visible cells only” and click OK.
As you can see there’ll be a white line where you have hidden rows and columns.
If you want to unhide all of these rows and columns you can do that by marking the entire worksheet by pressing the cell between A and 1 going to Format, Hide & Unhide, and then say Unhide Rows and do the same thing; Unhide Columns. And then all of your columns and rows will be visible again. You can also hide entire worksheets, so say for instance that I want to hide the budget for 2009. I’ll just right-click and select hide.
Again, if I want to unhide this, I can right-click next to it and say Unhide and select the budget for 2009 or I can go to the Format tab again, Hide & Unhide and select Unhide Sheet.
View sheets side by side to compare numbers (07:11)
The final thing I want to show you is how you can take different worksheets and compare side to side. So now I want to compare our budget for the 2010 with the budget for 2011. To do that I’ll go to the View tab and I’ll click New Window.
As you can see Excel has now opened up a new window, showing the same spreadsheet. I want this window to show the budget for 2011 instead, so I’ll just click on Budget for 2011. And then I’ll go to the window section and select View Side by Side.
Now I can see the budget for 2011 side by side with the budget for 2010 and I can easily scroll up and down. If I want to compare these row to row instead I can click Arrange All and select to arrange them horizontally.
And then click OK. And here I have them row by row. Now I have what’s called synchronous scrolling, so I’m scrolling both the windows at the same time. I can remove this by clicking the Synchronous Scrolling and that way I can scroll one window at a time. To go back to the original view, I’ll just close down one of the worksheets and then back to my original view.
Now I hope you have a better understanding of how you can navigate your spreadsheet, how you can hide and unhide columns and rows and how you can easily compare sheets side by side. I believe these navigation tips are even more useful if you’re analyzing someone else’s spreadsheet that you’re not too familiar with. I’m Ulrika Hedlund for Business Productivity – Thanks for watching!