If you are a data warrior, chances are that you use Excel extensively in your day to day work.
And there are many ways to boost productivity within Excel. Use these nifty tricks and improve productivity.
1. View All Formulas in Excel With a Single Click: I wrote about this trick some time back. It allows you to view all the formula with a single click of Control + ` Key. This acts like a toggle key. This can save a lot of time in finding where are the formulas to edit/view them.
2. Clean up Your Data – Identify the Duplicates, and Remove Duplicates: There is no point in having duplicates in your tables. There are multiple ways to do this. You can identify the duplicates, and then choose to remove them manually, or you can request Excel to remove the duplicate rows completely, automatically.
3. Colour Alternate Rows and Make it Easier to Read Large Data Sets: Prior to Excel 2007, we used to write formulas to colour alternate rows in Excel. This makes it easier to read the data.
This function would tell us if the row is an even row or an odd row, and then we could colour it.
But since Excel 2007, we can do this in multiple ways. We can convert the data into a table. And in the table options, you can then colour alternate rows or columns as bands.
And then you can also use the formula above, and use conditional formatting.
4. Learn The In-Built Functions in Excel: Many people are amazed at the plethora of in-built functions available in Excel. But you must know that they exist, and also know the syntax to make a good use of them.
- Numeric Functions in Excel – Video on using Simple Numeric Functions
- Text Formulas – Left, Right, Mid, Proper etc.
- Date & Time Functions
- Find the Difference Between Two Dates
- Finding The Second Largest or Second Smallest Value
5. Learn and Use Pivot Tables Effectively: Pivot tables summarize data quickly. Learn to use them effectively. Simple techniques like displaying both values and percentages in a Pivot Table adds extra value.
Similarly, learn to show different grouping for different pivot tables, even though it is based on the same data source. Also, creating a calculated field in a pivot table adds extra value.
If you are new to pivot tables, this introduction on how to analyze data using Excel pivots will be useful.
6. Charting Techniques Can Make People Notice Your Work: Creating nifty charts like the 2 Axis chart, or the PivotChart can be useful in the board room. Learn these techniques. Similarly, using Slicers in Pivot Charts can make dynamic Charts that can wow the audience. They are simple to use.
7. Get Back to Your Selection: Many times you will select or highlight some cells, but then move about the Excel file to find something. Then your are lost. Now you don’t remember where exactly is your selection. Simply click Control + Backspace. It will take you to the highlighted area in a blink!
8. Use Range Names to Refer to Cells: Instead of using G3, C3 or F3, you can give each cell a name. Not only single cells, you can actually define a name for a group of cells too. Then you can refer this group of cells by a particular name.
So instead of saying =G3+C3-F3, you can now calculate Salary as
=Basic_Pay + Allowance – Tax
This is much easier to read, and comprehend. No need to look up where the formulas are coming from, as the names are self evident.
9. Learn Excel Shortcuts: There are hundreds of shortcut combinations in Excel. It is impossible to remember all of them. But you must know a few to really speed up your work. Some of my favorites are:
- Control N: New workbook
- Control `: View All Formulas
- Control Backspace: Jump to selected Cells
- Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
- Alt + =: Auto sum formula is inserted
- Ctrl + Shift + Enter: Enter an Array Formula
- F9: Calculates all the worksheets in all open workbooks
10. Learn to Use Dates Effectively: In Excel, dates are stored as numbers. They can be displayed in a wide variety of Formats. Understand these formats to display them in the way that suits the applications.
Also, you can calculate using dates, for example, you can find the difference between any two dates. You can add a fixed number of days to a date.
Hope you enjoyed these tips, and will use them to improve your productivity in using Excel. Do post a comment below to talk about your favorite tips in Excel.
If you wish, you can get a good book on Excel from the Excel BookStore. These books are usually quite inexpensive, and will give you many ideas to improve too.
Vinai Prakash, PMP, MBA, ITIL, GAP, Six Sigma
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!
P.S. : You can contact us to conduct a workshop on Excel Tips and Tricks at your office.
And I hope you subscribed to the ExcelChamp Excel Tips Newsletter already. 🙂