![hell-275x143[1]](https://www.xlerateur.com/wp-content/uploads/2014/06/hell-275x1431.jpg)
Microsoft Excel is a powerful and widely used tool, but it can also be easily misused.
Here are 7 tips that are sure to make your Excel experience more efficient and enjoyable.
1. Referencing Cells Using the '$' Symbol
The '$' sign is essential when writing formulas. It allows you to lock a row or column, so when you copy the cells down, the referenced cell remains the same.
Tip: Select an address in the formula and use the F4 key to add or remove the '$' symbol.
2. Assigning Descriptive and Symbolic Names to Frequently Referenced Cell Ranges
Whenever a formula uses a fixed cell range, you can assign a descriptive name to that range and use its name in the formula. This will likely make the worksheet more legible and easier to understand. Depending on the situation, this method can replace the use of the '$' symbol.
To quickly add a name, select the cell(s) you'd like to name, enter the name in the Name box, and press Enter. Alternatively, you can also use the Name Manager.
Tip: It's possible to create dozens or even hundreds of named ranges in one fell swoop as shown in the following image.
3. Adding Comments to Complex Formulae
If a formula is complex, it is essential to add comments to allow other users to understand how or what the formula is accomplishing. You can use comments, or even create a separate documentation sheet in the workbook.
4. Making Lengthy Formulae Reader Friendly
Rather than writing a long formula on a single line, you do either of the following:
- ALT + Enter to add a line break to the formula
- Use whitespaces to simulate indentation
5. Cross-Referencing Calculations
Though this method requires a little bit more work, using a double entry data table will allow you to check totals both horizontally and vertically.
Additionally, a useful trick is to use information the information provided in the bottom right hand corner of a workbook, for example, to calculate the sum of a selection of cells.
6. Copying and Pasting Values Instead of References
Since Pivot Tables allow you to connect to an external database, if you aren't careful you might end up copying and pasting all of the data to another workbook.
Double click on the total TCD to see whether or not this is the case. Of course the reverse is also true. If you receive a TCD, double click on a cell.
Tip: Copy paste values and then copy and paste the formatting to make your table more secure. You may lose some interactivity but at least won't be exposing your entire company data.
Alternatively, you can also paste as image.
7. Making Sure Your Data is Updated
It's essential to have the most accurate and recently updated data especially when doing presentations to potential clients.
To refresh an individual pivot table, right click on it and choose Refresh.
Tip: Alternatively, you can also make sure that you're data is updated whenever the workbook is opened. In the PivotTable options, you can choose 'Refresh data when opening the file."
You can also create an event procedure that will refresh the Pivot Table each time the worksheet is activated.
It's up to you to decide on the best solution depending on the size and refresh duration of your Pivot Table.
All the best in avoiding spreadsheet hell, I wish you an Excel-lent day!
Do not hesitate to confess your own spreadsheet sins! I am sure there is enough to write a novel!
Gaëtan Mourmant
PS. The picture above right is from https://www.exceluser.com/formulas/index.htm