Tag: Excel Hints

Posted on

How to ‘Freeze Panes’ in Excel

How often when using Excel have you had to scroll down to find what you’re looking for in a huge spreadsheet only to find your top headings disappear off the screen? Well worry no more. The solution lies in using an incredibly handy tool called Freeze Panes. This tool ‘freezes’ the top row and left column headings making it easier to check data against the appropriate headings when scrolling. This can increase your productivity and save you so much time

What are Freeze Panes

The images below show 2 separate ways to ‘freeze’ panes:

  1. Freezing top rows, scroll down

scroll

  1. Freezing left columns, scroll right

scroll

 

When freezing panes there are 3 options:

  1. Freeze top row (i.e. row 1)
  2. Freeze first column (i.e. column A)
  3. Freeze both rows and columns

The first 2 options can only be applied separately whilst the 3rd option applies the ‘freezing’ in both directions.

How to apply Freeze Panes

 

  1. To apply Freeze Panes to the top row (row 1) go to VIEW > FREEZE PANES > FREEZE TOP ROW
  2. For the first column, go to VIEW > FREEZE PANES > FREEZE FIRST COLUMN

scroll

  1. To ‘freeze’ both rows and columns select the cell B6 (in this example). This means when you go to VIEW > FREEZE PANES > FREEZE PANES, any rows above and to the left of cell B6 will be ‘frozen’ when scrolling in either direction

  1. After ‘Freeze Panes’ has been applied, you may wish to turn off this feature. This may be because you might want to reset the areas to be frozen or you just want to take it back to normal. To do this, go to VIEW > FREEZE PANES > UNFREEZE PANES
  2. If you add more columns and/or rows to your spreadsheet, you will need to ‘refreeze’ the panes. For example, adding 1 column to the left would mean selecting cell C6 and then going to VIEW > FREEZE PANES > FREEZE PANES

Conclusion

‘Freeze Panes’ is a great time saving tool that will help you organise data in large worksheets and will therefore make you more efficient in viewing your data.

 

Posted on

Excel : Audit your Spreadsheet with the Inquire Add-In

new feature for microsoft office 2013For users with the Office Professional Plus package, the Inquire add-in comes pre-installed on Excel 2013. Helping you to analyse, audit and review workbooks, this great new feature also has the potential to highlight errors and security concerns.

We recently discussed, how to take back control of your spreadsheets by reducing “fat finger mistakes” and auditing errors, which can have huge cost implications to businesses. The Inquire add-in is another great tool for preventing these issues from arising and escalating in Excel.

The new tool can be accessed via the Inquire Tab in the ribbon and includes a number of useful functions:

Worksheet relationship

  1. Workbook analysis
  2. Workbook relationship
  3. Worksheet relationship
  4. Cell relationship
  5. Compare files
  6. Clean Excess Cell formatting
  7. Workbook passwords

These seven functions are simple to use, many providing visualisations to help better understand the information.

We all know Excel for being data driven, however, visualising things often makes this mass data easier to understand, take for example recommended charts & graphs or all new quick analysis techniques.

A few of our favourite Inquire features include:

Cell Relationship in Excel 2013

  • Cleaning excess formatting, including formatting in blank cells which bloats file size and contributes to poor performance in Excel.
  • The ability to compare two workbooks, highlighting cells that differ. This is particularly useful during an audit.
  • Being able to visualise the relationships between cells. Understanding the audit trail of how a figure came to be is a great way of maintaining the integrity and accuracy of the data.

How to: Enable the Add-in through File > Options > Add-Ins, from the Manage drop-down choose COM Add-Ins > Go. Tick Inquire and click OK.

To use it select the Inquire Tab in Ribbon > Choose function

For more tips and features on Excel 2013 and other versions, browse Microsoft training Excel courses from STL, available London and UK wide.