1.) Hiding Duplicate Records As Easy As 1-2-3!
One of the most common mistakes users make in preparing a report or sorting data is including duplicate entries. A built-in filter in Excel will take care of this problem. Follow these steps:
· From the Data menu, point to Filter and click Advanced Filter.
· Drag across the worksheet to select/highlight the lists containing duplicate entries.
· Check Unique records only option.
· Click OK.
Excel will now hide any duplicate records in the selected range.
2.) Saving time with Custom Views
Custom Views is a tool in Excel that lets you assign a name to a particular sheet layout so you can recall it for later viewing. Making good use of custom views can save a great deal of time. For example, you can eliminate repetitive hiding, resizing, filtering, and other changes for producing various printed reports. Each set of options can be saved as a view. Then, all you need to do is apply the view before you print.
To access the feature:
· From the View menu, click Custom Views.
· From the Custom Views dialog box displayed, you'll see a listing of saved views, and you can add or delete views.
When adding a view, you have the option of including print settings, hidden rows and columns, and filter settings. The new view even remembers where the active cell was when the view was saved, so when you open that view, it will "jump" to the spot in your sheet that corresponds to that view.
3.) How About Those Functions that Exceed the 30-argument limit?
Excel has a 30-argument limit for statistical functions. It's easy to get around, though, if you group some of your arguments within parentheses.
Instead of entering AVG(A1,A2,A3…,A33), you can enter AVG((A1,A2,A3)A4…,A33), and Excel will accept the grouped arguments as a single argument within the formula.
4.) Why Conditional Formatting?
Conditional formatting refers to the ability to format a cell based on the contents of the cells. Conditional formatting makes it easy to highlight certain values so that they stand out visually. For example, you may set up conditional formatting so that if a formula returns a negative value, the cell background displays yellow, etc.
The best part is that conditional formatting is easy to set up. Just click the cells you'd like to format and select Format Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur. You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used.
Each cell can have several conditional formats. For example, you might say that if a certain cell's value is between 10 and 30, the text should be bolded red on a yellow background.
5.) Catching Data Entry Errors
A workbook can be shared so that different peoples can access the same file at the same time. But it's a fact that shared workbooks generate more errors than workbooks with only one owner. Here's one way to flag errors as they occur:
· Select all cells in which data will be entered.
· From the Format menu, click Conditional Formatting.
· From the Conditional Formatting dialog box displayed, specify which cell values to flag.
· Click the Format button.
· From the Format Cells dialog box displayed, click the Patterns tab and select a color to signal errors.
· Click OK.
Now, when someone enters an error, it will be flagged with your selected color.
6.) How to Customize "division by 0" Error Messages?
Working in a complex worksheet, you may have encountered that annoying "#DIV/0!" error when the divisor of your formula is a zero. Using the IF function, you can create your own message for display when you divide by zero.
The IF function will be like this:
=IF (DIVISOR = 0,"Your Desired Text", DIVIDED/DIVISOR)
The IF function evaluates the first parameter (DIVISOR = 0). If it's TRUE, it displays the second parameter ("Your Desired Text") in the cell. If it's FALSE, the function displays the third parameter (DIVIDED/DIVISOR) in the cell.
7.) How to Ensure Proper Data Entry with Validation?
Data validation rules in Excel automatically check an entry made by a user against rules you have preset in the worksheet. This is helpful when you have formulas that depend on certain expected input from the user. These formulas can often return errors if the format of the entered data is wrong.
To set data validation rules, follow these steps:
· From the Data menu, click Validation.
· From the Data Validation dialog box displayed, click the Input Message tab.
· In the Input Message tab, insert the messages to display in the Title: and Input Message: boxes.
· Click OK.
This message will appear in a pop-up, note-style display. It can be used to provide users with information about what data you expect them to enter. You can also provide a message from the Error Alert tab if the data entered does not meet your rules.
8.) Link from Web Page to a Specific Worksheet Cell
Did you know that you can control which worksheet is displayed when you open a particular workbook in your Web browser. For example: http://www.msoffice-tutorial-training.com/test.xls#Sheet3!C2 will open your file called test.xls and then select cell C2 on Sheet3.
Using this kind of linking, you can specify exactly where you will enter the workbook. This can be helpful if you insert the link to a Web page that asks the user to enter data in a particular cell or area of a sheet. You can select the cell for them when they click the link.
9.) How to Express Yourself with Comments?
If you received a workbook and the formatting was a mess, and there were formulas everywhere. How do you feel if all those formatting and formulas are not properly explained?
It's time to get your users into the habit of documenting workbooks with comments. A cell with a comment is marked with a small red triangle in the upper-right corner. You can easily add a comment to a cell by clicking Insert menu, choose Comment.
Then enter the text descriptions into the cell. These descriptions are visible only if you hover the mouse over the cell.
Excel's Reviewing Toolbar facilitates working with the comments in a workbook. The options on this toolbar allow users to add and delete comments and move from one comment to another in a workbook.
10.) Few Clicks Clear Out All Excel Comments!
As handy as comments can be in annotating the information in a worksheet, you may sometimes want to delete them once they've served their purpose. The fastest way to handle this is:
· From the Edit menu, click Go To.
· Click the Special button and then select the Comments option.
· Click OK button.
· Go to the Edit menu again and point to Clear and to select Comments. Excel will delete all the comments and their text from the current worksheet.
11.) Several Users Work on the Same Workbook Together!
If you have several users who need to work from the same workbook at the same time, familiarize yourself with Excel's Shared Workbook feature. This feature can be a lifesaver when workbooks must be updated by multiple users.
You can set options that control when changes are updated and how to handle conflicting edits by different users. You can also choose to save updates when a user clicks Save, or you can specify a time interval for Excel to automatically save user updates.
To use this feature:
· From the Tools menu, click Share Workbook.
· From the Share Workbook dialog box displayed, in the Editing tab, check Allow changes by more than one user at the same time option.
· Click the Advanced tab and do the necessary settings.
You can decide how changes should be logged by the Track Changes feature, which integrates with workbook sharing. That way, you can see exactly what changes were made to cells, when sheets were added or deleted, how conflicting edits were resolved, etc. Users can also see the names of all the other users who have the workbook open for editing.
12.) Copying Styles from One Workbook to Another?
Formatting workbooks to your desired styles can really take time. So why should you waste time re-creating these styles in multiple workbooks? Just copy the style from one workbook to another:
· Open the source workbook and the destination workbook.
· Make sure that the destination workbook is active, go to the Format menu, and choose Style.
· From the Styles dialog box displayed, click the Merge button.
· From the Merge Styles dialog box displayed, select the source workbook from the Merge styles from: list box.
· Click OK twice.