Top 10 Time Saving Tips in Using Microsoft Excel

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.

=MOD(ROW(),2)=0

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.

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.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


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.

Cheers,
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. 🙂

Difference Between 2 Dates, Without the Weekends

It has always been possible in Microsoft Excel to find the difference between 2 dates. And there are hordes of other date and time functions in Excel, some of which I wrote earlier about.

Today, let us look at a recently added function in Microsoft Excel 2010, which can help you find the differenc in between any 2 dates, and this function works beautifully, even if your weekend does not fall on a Saturday/Sunday.

For example, recently I was in Cairo, Egypt to conduct a Corporate Training, and found out that their weekends fall on Friday and Saturday.

The regular function that I used earlier – NetworkDays() –  is good  at finding the days between any 2 dates, but it assumes that the weekend is on Saturday/Sunday.

Fortunately, Microsoft has added an International Function in Excel, which helps to work this out andmakes it fairly easy.

The function is NETWORKDAYS.INTL().

It takes 4 arguments – Start_date, End_date, Weekend & Holidays. The last 2 are optional, but that is where the function shines and differs from the Networkdays() function.

The weekends argument can take one of these several values, and when you type the formula, this helpful guide pops-up.

Weekends in Microsoft Excel

As you can see, you can easily select any single day, or a two consecutive days as your weekends.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


For Egypt, Dubai, their weekend of Friday, Saturday has the value of 7.

Thus, I choose the following function:

=NETWORKDAYS.INTL(“1-Sep-2012”, “31-Sep-2012”,7)

and promptly got the result as 21, which is the number of working days in this period, excluding the Fridays & Saturdays.

If you only use =NETWORKDAYS(“1-Sep-2012”, “31-Sep-2012”)

you will get the answer as 20 days, which is based on the Saturday/Sunday weekends.

So there you are. Have your cake and eat it too! You can check out these function in the difference-between-dates Excel file, which you can use to practice and test out these functions.

Anything else you’d like covered here? Do post a comment and let me know.

Hope you enjoyed this Excel Tip. If you would like some more tips, simply subscribe to the ExcelChamp Tips Newsletter here.

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net – A community for Excel Tips & Tricks

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!

Using Date & Time Functions in Excel

There are many in-built Excel Functions that can help you in working with Dates and Time. It can assist you in showing today’s date, the current time, and represent dates as Months, Years, and even calculate difference in 2 dates.

1. Getting Today’s Date

In the cell where you want to display today’s date, key in the following formula:
=TODAY()

Depending on your PC’s Regional Settings (Setable in Control Panel), you will get today’s date displayed in MM/DD/YYYY or DD/MM/YYYY format.

The problem will this approach is that every the Excel file is re-calculated, the date will change and display the latest day’s date.

If you wish to only display today’s date, but do not want it to change in the future, there’s a little known shortcut to do this. Go to the cell where you want to get today’s date, and simply press Ctrl + ;

2. Getting Today’s Date & Time

In you want the date and time, both to be displayed, more like a time stamp, use the following formula:
=NOW()

It will display as 24/7/2012 12:33

If you only want the time, like a time stamp, and do not want the time to ever change, then simply press the following keys: Ctrl + Shift + ;

And if you want to display both the date and time stamp in a single cell, combine the 2 functions

Select a cell and press CTRL+; then press the SPACE BAR to add a space, and  then CTRL+SHIFT+;

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


3. Get the Number of Days in between 2 Dates in Excel

To get the difference in 2 dates, you can simply subtract them, like:

=B6 – A6

If you only want the Weekdays, excluding Sundays, you can use a hidden Excel gem – Networkdays function.

=NETWORKDAYS(A1, A2)

It will calculate the difference between A1 and A2, without counting the Sunday.

4. Get the Month of a Date 

To get the Month, displayed as a number – for example: July will be 7.

=MONTH( TODAY() ) will give you a 7 in July.

= MONTH(A1)

5. Getting only the Year of a Date

If you wish to extract the YYYY portion of a date, simply say:

=YEAR(A1)

For 31-July of the Year 2012, it will yield 2012.

As you can see, Excel’s in-built Date and Time Functions are really useful, and handy. Do master them, and you will save a lot of time.

Let me know how’s your experience in handling dates in Excel. You can post a comment below… 🙂

Cheers,

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!