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!

Creating a Two Axis Chart in Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2016 & Microsoft Office 365

Up to Microsoft Excel 2003, there was a in-built Custom Chart Type called the 2-Axis Chart. This was a pretty useful chart type, in which I used to display very small numbers and very large numbers – all on the same chart. You can see in this chart below, the numbers on the Primary Y Axis on the left are from 0 to 16. The numbers on the Secondary Y Axis (on the right side of the chart), are from 20,000 to 180,000.

2 Axis Chart in Excel - Final2 Axis Chart in Microsoft Excel

But this 2 Axis chart type magically disappeared from Excel 2007, Excel 2010 & even Excel 2013. So today, I will show you how to create a 2 Axis chart in any version of Excel – whether you are still using Microsoft Excel 2007 or Microsoft 2010, or the latest Excel 2016, 2019 or Office 365 (Cloud version of Microsoft Office).

What is a 2 Axis Chart?

The default Bar Chart or a Column chart of Excel has One X Axis (The Horizontal side), and One Y Axis (represented Vertically). This is generally useful most of the time.

However, once in a while, when you want to display 2 different sets of numbers in the same chart, and one set of numbers (Quantity) comprises of very small numbers, and the other set of numbers (Sales figures) are very large –  in Thousands.

If you plot a normal Column chart, the Sales figures are so high that the bars are quite long, and the scale is set in Thousands. So the Quantity figures are too small to be even seen in the Column Chart.

A 2 Axis chart is slightly different from a normal, standard Column Chart.

The 2 Axis Column Chart uses TWO Y Axis in the same chart. The one on the Left of the chart is used to display one scale (Small Numbers), and another Y Axis is added on the Right Side, which represents the (Large Numbers) in Thousands.

In this way, both small numbers and very large numbers are seen in the same chart – using both the Primary Axis and the Secondary Axis.

However, do note that the Columns of the Chart will overlap each other, and it will be difficult to see one set of values. To overcome this situation, we convert one of the bars into a Line Chart.

In this way, even if the scale is different, both axis can be seen clearly, and a useful 2 Axis chart is created easily in Microsoft Excel.

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
!

How To Make a 2 Axis Chart in Excel 2007 to Excel 2013

If you wish to make a 2 Axis Chart in Microsoft Excel 2007 or Excel 2010 or Excel 2013,  just follow this  fairly simple process:

  1. Select the Data to be plotted. You can use this Example Worksheet to practice creating the 2 Axis Chart.
  2. Click Insert > Column > 2-D Column Chart. You will get a normal Bar Chart in Excel.
  3. Click on one of the bar charts. The entire series gets selected.
  4. Right Click, and select Format Data Series.
  5. From the Format Data Series Popup Menu, Choose Secondary Axis.
  6. You will see that the selected data series has moved to the Secondary Axis.
  7. Format the chart to your liking.

You can have a beautiful looking chart, displaying data on 2 axis – the Y axis is listed on both sides. And it hardly takes a minute to build… Enjoy!

How To Make a 2 Axis Chart in Excel 2016, Excel 2019 or Microsoft Office 365

For the newer versions of Microsoft Excel, the steps are slightly different.

STEP 1: Select the Data to be plotted in the chart.  You can use this Example Worksheet to practice creating the 2 Axis Chart.

STEP 2: Click on Insert > Recommended Charts. Choose the second Tab in the popup, All Charts.

Inserting Recommended Chart in Excel
Inserting Recommended Chart in Excel

STEP 3: Go to the bottom of the Chart List, and select the Combo Chart.

Select Combo From All Charts in Excel 365

A combo chart is created. We can’t see both quantity and Sales, as the sales figures are too high. So we need to modify this.

STEP 4: What we really want is to move Sales on the Secondary Axis. So check the checkbox  next to the Quantity.

Checkbox to move Sales to Secondary Axis in Excel 365

Sales moves to the secondary axis, and is visible.

You can see that Quantity Sold is in Columns (showing as vertical bars), and Sales is displayed in a Line Chart.

2 Axis Combo Chart in Microsoft Office 365, Excel 2016 & Excel 2019
2 Axis Combo Chart in Microsoft Office 365, Excel 2016 & Excel 2019

STEP 6: Now simply add an appropriate Chart Title.

Your 2 Axis Chart in Excel is ready for Microsoft 365. The same technique works for Excel 2016 & Excel 2019 also.

Additional Useful Tips & Tricks on Excel at our Website

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 on Charts, Pivot Tables techniques, simply Subscribe to the ExcelChamp Weekly Excel Tips Newsletter.

Cheers,
Vinai Prakash
Founder ExcelChamp.Net

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!

Not Ascending… Not Descending… Just Plain Custom Sorting in Excel

It is pretty easy to sort data in Excel.

The sorting option is available on the Home Tab of Excel 2007 or Excel 2010. See image below from Excel 2010.

Home Tab on Excel 2010

Just select the data, and click on the Sort & Filter Button on the Home Tab.

Sorting Menu in Excel 2010

You can sort A to Z (Ascending order) of the selected column, which is the default Sorting option in Excel 2003, 2007 & Excel 2010 too.

But you could also sort in the Descending Order first (Sort Z to A), just by selecting this option from the Sort button on the Home Tab.

Between these two Ascending & Descending options, you can get almost all kinds of data sorted.

However, you will come across situations, when neither of these options will give you the desired result. This happens when you want to sort in a particular fashion – which is neither ascending nor descending.

What do you do then?

Here’s where Custom Sort comes into play. This hidden gem has been available in Excel since ages (a.k.a. since Excel 95, 98… days). However, most people are stumped when it comes to using it.

Say for example, that Department wise Spending are provided in an Excel file below.

Sorting Unsorted Data in Excel

You want to sort on departments, and you want them in this order listed below.

  1. Sales
  2. Marketing
  3. Service
  4. Human Resources
  5. Accounts
  6. Information Technology

Clearly, this can’t be done using Ascending Sort or Descending Sort.

Custom Sort comes to the rescue.

Go to Sort & Filter button on the Home Tab of Excel 2010. Click on Custom Sort from the drop down menu. A new popup window will open.

Custom Sort Popup in Excel 2010

If your data has headings, you can select the check box “My data has headers“. If you do not have column heading, then do not choose the “My Data has headers” option.

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
!


In the Sort by Column dropdown, choose Department as the option to sort on.

In the Order dropdown, choose “Custom List…“.

Custom List Popup in Excel 2010

Click on NEW LIST, and press Add.

The cursor will move to the Empty Box in the popup. Key in the list of departments here – one per line, in the SORT order you need. Then Click OK.

List of Sort Order Entries in Custom Sort in Excel 2010

Click OK on the Custom List Popup, and close all pop ups.

You will now notice that the list of Departments has been sorted, as per your specified order – which was neither ascending nor descending.

List of Departments Sorted in Custom order in Excel 2010

As you can see, using the Custom Sort is pretty easy. In fact, Microsoft Excel has been using this trick to sort Months, Weekdays, Quarters etc. since ages too. It’s time you learnt about this nifty technique too.

Do try it out, and let me know about your experience with Custom Sort. I’ll be glad to receive your comments and suggestions.

Additional Resources:

Cheers,
Vinai Prakash
Founder & Editor, ExcelChamp.Net

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