Top 10 Excel on Steroids Tips & Tricks in 2012

This is a list of the top 10 Excel Tips & Tricks of 2012 based on number of views by you! This list is made up of the Excel tips you and our other readers found the most interesting & useful in 2012.

Top of the list is:

1. How to create a shared workbook so that several people can edit the contents simultaneously

number 1

By using a shared workbook you can allow several people to edit the contents simultaneously whilst tracking the progress of your work and updating the information. View tip

The other top tips:

2. Compare and Merge Workbooks

Using the Compare and Merge command you can verify the updates made by other users of a shared workbook and compile the final version of the document. View tip

3. Use MATCH and INDEX as an alternative to the Vertical Lookup

By using a combination of MATCH and INDEX, you can return values from an array regardless of what information is in the first column of the array unlike with using Vertical Lookup. View tip

4. Quickly rearrange Excel worksheets in alphabetical order using VB for Ms Excel

Instead of dragging and manually arranging the worksheets, by using VB for Ms Excel worksheets can quickly be rearranged in alphabetical order. View tip

5. Conditional formatting – Icon Sets

The Conditional formatting – Icon Sets option creates a visual effect in your data to help you see how the value of a cell compares to other cells. Very useful if you struggle to easily categorize debtors based on how much they owe your company. View tip 

6. DSUM function

Allows you to specify criteria and conditions regarding which cells should be added together.  View tip

7. Using the Subtotal function and the Data Filter option to sum data values based on cell color

If you have allocated colours to different values, this tip shows you how to filter data by selected cell colour.  View tip

8. Advanced Filter Option

This allows you to extract data based on defined criteria across multiple columns of data.  View tip

 9. Applying formulae to a range of data by using Conditional Formatting

Provides a smart way of monitoring the performance of Sales Staff as it formats only the data where the result of the formula is positive. View tip

 10. Sumif function tip

Simplifies the task of summing up all the positives/negatives in a column per GL account and per month. View tip