Micro Soft Excel: 4 more tips to help you

Microsoft® Excel® [1] can do so much in terms of features, commands and functions. Some of those are complex while others – which are actually very simple – are not known by all users. Want to easily sort and organize the data in a worksheet? Or protect your file from unauthorized viewing and changes? Hide rows or check for duplicate entries? All of these are possible.

Sorting groups of data and information
Excel can sort groups of data and information according to the criteria you tell it to follow, and in the order you desire – for example, alphabetical or chronological. Let’s say you have data entered as First name, Last name and Age: you can sort according to any of the three and then choose subsequent levels.

Highlight the group of cells you want to sort. If there is a header (title) row of cells, you can highlight those, too. Now, from the “Data” tab, choose “Sort” from the “Sort & Filter” group. If you have highlighted the header row, make sure “My data has headers” in the top-right corner is ticked off. Now choose the column you want to sort by and in which order (ascending/descending). Using our example of First name, Last name and Age, you can choose additional levels of sorting if, say, more than one person has the same “value” for either First name, Last name or Age.

No matter how complex the data, the same sorting rules, functions and features are applicable.

Password protecting an Excel workbook
Information security issues are on the rise and Excel is here to accommodate. For documents with sensitive information such as sales figures, you can add a password to prevent unauthorized opening of an Excel workbook.

From “File” or the “Office” button, choose “Save As”. In the “Save As” window, click on “Tools” in the bottom-left corner then “General Options”. Now enter your desired password and continue saving as normal. That document is now for authorized viewing only.

Hiding rows or columns
when it’s time to print your Excel worksheet, you may prefer some information not to be printed – for example, confidential information such as employee salaries or sales figures. To avoid printing specific rows or columns, just hide them before printing. (Hiding rows or columns can also simplify working with a complicated spreadsheet.)

To hide rows, select them by clicking the row numbers (left click and drag to select a block of rows; and remember, hold down “Ctrl” while clicking to select non-adjacent rows). Then right click one of the highlighted border row numbers and select “Hide”. Use the same procedure to hide columns, simply by right clicking a highlighted column letter and selecting “Hide”.

“Unhidden” is just as simple. To unhide rows or columns, highlight the entire spreadsheet by left clicking the box formed at the intersection of the row and column borders at the top left corner of the spreadsheet. Then right click a row or column border and select “Unhide”.

Spotting duplicate entries
Similar to how Word® can detect repeated words, Excel can spot repeated cell entries, but you have to tell it to. It’s a simple command.

Excel 2007 – which should be the norm by now – makes it simple. Select and highlight the cell range you want to check. In the “Home” tab and part of the “Styles” group, you’ll find “Conditional Formatting”. Click “Highlight Cells Rules” from its drop-down menu and then select “Duplicate Values”. Choose your desired formatting for how you want to identify duplicate entries – and you’re done!

[1] This article refers to the English version of Excel 2007.

Microsoft, Excel and Word are trademarks of the Microsoft group of companies.

Post a Comment