» Excel for Finance
Written by ·

How to Freeze Panes and Zoom in Excel

 

In this post, we will see how to freeze panes and how to zoom in and out of data in excel.

Freezing Panes is especially important when you have really huge data. Like shown in the figure below, where the data size is large and you need to scroll down, in such case you are not able to see the headers as you scroll down.

 

Picture1 How to Freeze Panes and Zoom in Excel

Freeze Panes

Same is the problem with large number of columns.

Key to Freeze Panes

The shortcut to freeze panes is Alt+W+F+F for Excel 2007 and Alt+W+F for Excel 2000 and Excel 2003. What it exactly does is it takes top left corner of whichever cell you are currently in and freezes the panes then-and-there.

Picture2 How to Freeze Panes and Zoom in Excel

Freeze Panes

As shown in the above figure, the first row i.e. the row of titles is freezed so that even if there is huge data and you have to scroll down, still you will be able to see the titles and don’t have to scroll up every time to see them. Same is the case with columns.

Picture3 How to Freeze Panes and Zoom in Excel

Freeze Panes

Use Alt+W+F+F (Excel 2007) or Alt+W+F(Excel 2000 or Excel 2003) to unfreeze the frozen cells.

The Freeze Panes functionality is most commonly used to freeze the first row and/or first column. You will see why when we study models further.

If you are using Excel 2007, then you can use Alt+W+F+R to freeze the first row and Alt+W+F+Cto freeze the first column.

Even though this option is not present in Excel 2000 or 2003, there you can use Alt+W+F+Fto freeze row above your current cell and column to the left of your current cell.

So this was about freezing panes.

Now we will see how to use the zoom feature in excel.

Use Alt+V+Z to zoom the excel sheet.

Picture4 How to Freeze Panes and Zoom in Excel

Zoom in Excel

As shown in the above figure, you can zoom using Alt+V+Z. It asks you the percent of zoom,which you can select using arrow keys.

Picture5 How to Freeze Panes and Zoom in Excel

Zoom in Excel

Similarly you can zoom in as shown in the figure below using the same combination of keys.

Picture6 How to Freeze Panes and Zoom in Excel

Zoom in Excel

Also, you can use the Fit Selection option of zoom to fit the selected data on one screen. This can be done using Alt+V+Z+F.

Picture7 How to Freeze Panes and Zoom in Excel

Zoom in Excel

So this was all about how to Freeze panes and zooming feature in excel. These features are also used frequently in financial modeling.

Let me know if you have any queries regarding freeze panes and zoom in excel .

Written by · 1 Comment

How to Hide, Unhide and Group in Excel

In this post, we will see very important features- hide, unhide and group in excel.

Hide and Group in excel feature is useful when you have really huge data that spans over too many rows and columns, and you want to do away with some rows and columns and want to access only few of them. We will use a sample excel sheet for this.

Group in Excel

Excel group columns

The shortcut to group rows & columns is Alt+D+G+G where Alt+D takes you to data navigation keys and G+G tells that you want to perform grouping action.

 

Picture1 How to Hide, Unhide and Group in Excel

Group in Excel

 

As shown in the above figure (encircled in red), the grouped columns are indicated by a minus sign.

 

Picture2 How to Hide, Unhide and Group in Excel

Group in Excel

Use Alt+D+G+U to ungroup the grouped rows and columns where Alt+D takes you to data manipulation menu and G+U indicates you want to ungroup the grouped data.

To hide rows or columns, select the rows/columns to be hidden and press Alt+D+G+H where Alt+D takes you to data manipulation menu and G+H indicates you want to perform hide action.
Picture3 300x95 How to Hide, Unhide and Group in Excel
Fig:Hide and Group in Excel

As shown in the above figure, hidden rows are indicated by a plus(+) sign.

To unhide hidden rows/columns, press Alt+D+G+S where Alt+D takes you to data manipulation menu and G+S indicates you want to show the hidden elements.

Other shortcuts to Hide,Show,Ungroup and group in Excel

  •  To group rows/columns, select the rows/columns and press Shift+Alt+Right Arrow key.
  •  To ungroup the rows/columns press Shift+Alt+Left Arrow key.
  •  To hide individual row  press, Ctrl+9
  •  To unhide individual row , press  Ctrl+Shift+F9
  •  To hide individual column, press Ctrl+0 (Ctrl+Zero).
  •  To unhide individual column press Ctrl+Shift+0(Ctrl+Shift+Zero)

Please note that unlike the previous shortcuts for hiding and unhiding rows and columns(ALT+D+G+ combination), here we need to use separate combinations for hiding and unhiding  individual rows and columns.

So this is how we group/ungroup, hide/unhide rows and columns in excel.

Now, try to group in excel along with other features mentioned in this post and let me know if you face any problems.