Removing Grid lines from a Section in Microsoft Excel

You can switch off Gridlines completely, or you can have them on. But many people are amazed to learn that you can turn them off or on from a certain section of their Excel spreadsheet. And this trick works in Microsoft Excel 2003, Excel 2007 as well as Excel 2010.

How do you achieve this… Very simple trick that doesn’t take a minute to do… Watch this step-by-step video on how to remove gridlines from a section of the Excel Sheet.

Let me know if this works for you…

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
!


Do you have any other problem in using Microsoft Excel? Simply write to me using the comments box below, and I will assist you.

And check out my other Microsoft Excel Tips & Tricks videos on YouTube.

Additional Resources:

Cheers,
Vinai

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!

Excel Tutorial – Combine Multiple Text Columns Into a Single Column

Some of the attendees at my Excel Boot Camp trainings have been thrilled to find that there’s this nifty function, which can combine many text columns into a single column.

Let’s say you have a column with First Name, and a separate column for Last Name, and you want to combine them to form the full name. How would you do it?

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
!


I have recorded a short video on how to achieve this, using 2 different methods. Check it out below:

Let me know if this works for you. And if you have any other method to achieve this, I’ll be glad to know it…

And check out my other Microsoft Excel Tips & Tricks videos on YouTube.

Cheers,
Vinai

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!

Simple Functions in Excel – Video

Most beginners to Microsoft Excel are not aware how easy it is to use some simple functions.

I have recorded a detailed, step by step video, which shows how to use the Sum, Count, Average, Minimum and Maximum functions in Excel, to add, count numbers.


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
!


Do let me know if this helps you in using Microsoft Excel. The techniques applied here are same for Excel 2003, Excel 2007, Excel 2010 or Excel 2013, so it does not matter which version you are using.

And check out my other Microsoft Excel Tips & Tricks videos on YouTube.

Additional Resources:

Cheers,
Vinai Prakash

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!

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!

Show Buttons
Hide Buttons