How to Excel in Excel - tips and tricks even for proficient excel users24.06.2016
Sometimes in life it’s the smallest things that are the most satisfying and time saving. One of the most common requirements for our volunteer placements and provides a huge benefit to our partners is Excel tips and tricks that allow staff to save time on their spreadsheets and concentrate on other tasks.
We’ve compiled a list of pointers and videos that might even be useful to the most proficient Excel users and could half the time spent on your workbooks.
Excel Best Practice:
Before you start putting any spreadsheet together – check out these posts for Excel best practice on aatcomment.org. If followed these tips for good spreadsheet practice will ensure that the integrity of your data on your spreadsheet is not undermined by poor management. Save time fixing your mistakes and follow these guidelines from the get go.
- Part 1: determining the role of your spreadsheet, collaborative ownership, audience appropriate design and adaptability for longevity
- Part 2: consistency in structure and formulae, avoid unneeded complexity, backup, security and protection
Back to basics:
Lifehacker have produced two great guides which takes you back to basics with excel - how many of these simple tricks did you actually know? I bet there are at least two in these guides that you didn’t even know existed and will make your life much simpler! Did you realize Excel was so cool?
- Seven Useful Excel Features you may not be using
- A Guide of 20 tricks that can make anyone an Excel expert
Absolute References:
This quick trick will allow you to avoid entering the formula each time but ensure that the formula is not copying incorrectly. Useful for copying formulas to many rows or columns.
Examples:
- $R$2 – “Locks in the column and row. The formula will only use data found in this cell. An example of when you may want to use data found in only one cell could be when you are using an exact date or percentage in your formula.”
- $R2 – “Locks in the column. As you pull your formula down or across, the formula will always pull the data from column R for the corresponding row”
- R$2 – “Locks in the row. As you pull your formula down / across, the formula will always pull the data from row 1 for the corresponding column”
It’s a good idea to use absolute references in your formulas even if you don’t plan on copying them across as it’s a safe trick to make sure your formula don’t change even if you move the cells.
Copying Visible Cells Only:
You’ve just finished filtering your data on your main spreadsheet but would like to copy this data onto another sheet for more analysis. You select the data you want, copy, new sheet, and paste. But wait – you’ve managed to copy the whole spreadsheet and not just the filtered section. Here’s an easy Excel Tip to avoid this annoyance;
- Highlight the data you want to copy
- Hit F5
- Click “Special” button on the box that appears
- Select “Visible Cells Only” & Ok
- Your data should still be highlighted copy by using Ctrl “C” or Edit > Copy
- Paste your data into another sheet. Only the filtered / “visible” data should copy.
Paste the format, not the data!:
A handy trick for audits, or budget workbooks or any task where you want to present your data in a certain format. A quick trick to avoid playing around with font sizes or border styles: just copy the format.
- Right Click
-
Under the “Paste” options, click the paintbrush logo (formatting)
These are the simpler tricks that can be explained in a few words. Check out these posts and videos for more tricks on how to become an excel wizard:
- How to “spot the doubles” in Excel
- Connecting workbooks
- Handling #N/A errors
- Formula auditing and tracing calculations
- Importing data into Excel
- Data Relationships and Pivot Tables
- Using V-LOOKUP to find information in a table
- Creating basic Macro
- IF Statements & Functions
- 3D maps of the World, tree maps and much more – bet you didn’t know about these different graphs in excel
