Loading...

Follow Excel Champs Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Using a filter in Excel is not a #rocketscience. Everyone knows that they can do a lot of things with filter option.

But, the bad news is they are not actually using it to the best or maybe they forget to make a habit to use it.

And, the good news is you can be one of those excel users who knows how to use a filter in a smart way.

So, today in this post, I’ll show you eight amazing things which you can do with filters and save a ton of time.

Click here to download the sample file which I have used here in examples. And now, let’s get started to explore these amazing options.

1. Filter By Color

The most creative way to filter values.

Whenever you have some cells in your worksheet which are highlighted with a color, you can easily filter them.

Let’s have a look at the below sheet where you have some highlighted cell with red color in which you have some errors and you want to filter all those cells.

Here are the steps:

  • First of all, apply a filter to your data.
  • After that, open filter drop-down.
  • Go to Filter by color -> select the color for which you want to filter.
  • Once you click on the color, it will filter all the cells with the red color.

The best thing about this option is you can filter all those cells as well where you don’t have any color.

Quick Tip: When you need to search for a date you can use the little drop down from the right side of the search bar to select whether you want to search for a year, month, or a date.
2. Type in the Search Bar

Simple and Effective.

If you are using excel 2010 or earlier version then you have a search bar in your filter drop down. You can use this search bar to find a value you want to filter.

Now, check out the below data table where you need to filter employees whose first name is “John”.

Here are the steps:

  • Make sure to add filters to your data.
  • After that, open the filter drop down and click on the search bar.
  • Now, type the word “John” in the search bar.
  • As soon as you do that, it will filter all the values where word “John” is there.
  • In the end, click OK.

The best thing about this option is it is so dynamic that it will give you results right away when you are typing your search.

 Quick Tip: When you need to search for a date you can use the little drop down from the right side of the search bar to select whether you want to search for a year, month, or a date.

Want to learn some time saver tips? Then you should download this e-book.

3. Add a Condition with OR

Filter with more than one criteria.

When you need to filter values if one of two given conditions is met or both of the conditions are met, you can do this by using OR in filters.

Just think this way, you have data for all the employees in your company and you want to filter only for people whose name is “Jon” and “John”.

Now here, the condition is to filter cells in which have the name, Jon or John. So, follow these simple steps and make sure you have sample file with you.

  • First of all, add filters to your data.
  • For the column where you want to filter data, open the filter drop down.
  • Now, go to -> Text Filters -> Custom Filters.
  • Here, you have a window to select filter options.
  • So, from this window, select “contains” from both of the drop downs.
  • And after that, in the input bar enter “Jon” and in second input bar enter “John”.
  • Select “Or” from the option buttons.
  • In the end, click OK.

Now, all the cell where you have “Jon” or “John” are filtered.

Quick Tip: Let’s say if you want to filter values based on two conditions, For Example: if you want to filter cells where you have both “Jon” and “John” in a single cell, you can use AND instead of OR.
4. Filter Top 10 Values

Get top 10 values in a few clicks.

If you have a large dataset and from that data, you want to check top 10 values then all you need a few clicks.

Here are the steps:

  • Apply filters first.
  • And after that, open the filter drop down for the column where you want to filter values.
  • Go to -> Number Filters – > Top 10.
  • This will give a pop-up window. And, from that window, you have to select following things.
  1. Top or Bottom Values: You can select top or bottom values to filter.
  2. Number of Values: You can also specify numbers of values you want to filter.
  3. Type of Filter: This is a smart option where you can select the way you want to filter values.
  • Once you select your preferences, click OK.

So, once you click OK, all the values which are in top 10 will be filtered.

5. Using a Wildcard Character

Think out of the box.

Wildcard characters are all about partially matching and finding a text. And, you can also use wildcard characters for filtering values.

Let’s say from a name list you want to search for the names which are starting from alphabet “J”.

Here are the steps you have to follow.

  • Again, apply filters to your data.
  • And then, open filter drop down.
  • Now, in the search bar type “J*”.
  • Once you type your text, it will instantly filter all the names which are starting from the letter “J“.
  • After that, click OK.

You can also use this method with custom filter option as well. And, there are total three wildcard characters which you can use in Excel.

6. Pre-Defined Date Parameters

A smart way to filter dates.

You know, when it come to dates, Excel is super smart. There is already you have a lot of options to filter dates.

Just have a look at below list.

You can use any of them, they all are super useful. But, let me tell you few of them which I am sure will be a game changer for you.

  1. Year To Date: Super useful if you want to filter all the date from the starting of the year to till date.
  2. All Dates in a Period: It will help you to filter all the dates within a particular month or quarter.

You have total 21 + custom filter options for dates. And, if you want to filter a custom date you can use a custom filter.

7. Filter Above/Below Average Values

A handy option to check data insight.

Let’s say you want to filter values which are above average. What will you do? Add a formula? Well, you have a decent option for this.

Here are the steps:

  • Apply filters on the column where you have values.
  • After that, open filter drop- down, go to “Number Filters” and click on “Above Average”.
  • And, once you do that it will filter all the values which are above average.

By using the same method you can also filter values which are below average. Just click on “Below Average” instead of above average.

8. Filter Values in Pivot Tables

The best way to filter values in a pivot table is by using a slicer. You can link multiple pivot tables to one filter with a slicer.

  • Once you create a pivot table click on any of the cells in it.
  • Now, go to Analyze Tab -> Filter -> “Slicer”.
  • It will instantly give you a pop-up window to select the field you want to use in the slicer.
  • Click OK.

Now, you can use this slicer to filter all the values in your pivot table.

The best part about slicer is you can check anytime how many values you have there to use as a filter.

Sample File

Download this sample file from here to learn more.

Conclusion

As I said, there are a lot of options you have in filters which probably you are not using yet.

If you start using some of them which are highly relevant to your work, you can increase your productivity by 5X.

And, the options which I have mentioned above are a super time saver for you.

Now, it’s your time to speak your mind. Do you have any other filter option which you use frequently? Please share with me in the comment section, I'd love to hear from you.

And, please don't forget to share this tip with your friends. 

The post The Best 8 Tips to Filter Data in Excel Like a PRO appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In Excel, there is no direct function to get quarter from a date.

But you can create a formula for this.

Yes, you can use a formula for the quarter calculation from a date.

Even there is more than one method which you can use.

Alright, as you know there are total four quarters in a year but the thing is you can get a quarter from a date in different ways.

The first is, assuming year on the normal calendar basis or the second one on the fiscal calendar basis.

And in today’s post, I’m going to show you methods to get the quarter number using both of the calendars…

..and I’ll also share more than one method with you for this.

So let’s get started.

5 Different Ways to Get Quarter from a Date

Here I’ve listed top 5 ways which you can use to get a quarter from a date. Each of these methods is unique and you use any of these according to your need.

Get Better at Excel in 2018

A bundle of E-Books you need to thrive in Excel this year.

Yes, Send me Now
1. Get Quarter by using ROUNDUP and MONTH Functions

Using a combination of ROUNDUP and MONTH is the best way to find the quarter of a date. It returns quarter as a number (like 1,2,3,4).

Here’s the formula.

=ROUNDUP(MONTH(A1)/3,0)

Here we are using 26-May-2018 as a date and the formula returns 2 in the result. You can use below formula if you want to add a Q before the quarter number.

=“Q-”&ROUNDUP(MONTH(A1)/3,0)

How this formula works

This formula works in three parts.

In the first part, we have used the MONTH function and referred to the date which returns the month number.

After that, in the second part, we have divided that month number with 3 which gives us a number with decimals.

At last, in the third part, we have used ROUNDUP function to round (that number with decimals) which gives us the actual quarter.

Here’s is the real thing:

You know there are 4 quarters and twelve months.

Right?

And if you divide all the month numbers with three (yes, just with 3) you’ll get something like below.

And when you round them up you’ll always the get the quarter number for the month.

2. Get Quarter by using Fiscal Calendar

In the above example, we have used a simple formula to have a quarter from a date, but sometimes you need to use a fiscal calendar.

What is a Fiscal Year?

A fiscal year (FY) is a 12 month period that a company or government uses for accounting purposes and preparing financial statements. A fiscal year may not be the same as a calendar year…

…learn more

And for a fiscal calendar quarter calculation, we need to use a different formula and in this formula, we need to combine CHOOSE and MONTH function.

Here’s the formula:

=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)

(Here I’ve used Indian fiscal year which starts on 1 April and ends on 31 March)

It returns 1 in the result as the month is May and our year starts from April and May is the second month in the year comes under the first quarter.

How this formula works

Here we have combined two different functions and it works in two parts.

In the first part, we have used MONTH function to get month number from the date and here it returns 5.

After that, in the second part, we have used choose function to get a number according to the month number return by the MONTH function.

You can learn more about CHOOSE from here.

If you look, in CHOOSE function we have specified 12 different numbers (4,4,4,1,1,1,2,2,2,3,3,3) so when MONTH returns a number CHOOSE returns a number from this list.

Here’s the deal:

For the first three numbers, we have 4 that means if it returns 1,2, or 3 (Jan-Mar) we will get 4 and for next three months, we will get 1 (That’s because our fiscal year is starting from April).

3. Convert a Date into a Quarter using VBA Code

Here we are using a VBA code to simply convert a date into its quarter number.

Here ‘s the code:

Sub convert2Quarter()

Dim Rng As Range

For Each Rng In Selection

If IsDate(Rng.Value) = True Then

Rng.Value = DatePart("q", Rng.Value)

Rng.NumberFormat = "General"

End If

Next Rng

End Sub

To use this code all you need to do is to add it into your VB editor.

And then simply select the cells where you need to convert a date into a quarter number and finally run this code.

How this Code Works

When you run this code, firstly it checks whether the cell has a date or not, and if it’s a date then it converts it into the quarter number.

We have used two different VBA functions here:

  1. ISDATE: To check whether the cell has a date.
  2. DATEPART: To convert a date into quarter.
4. Get Quarter Start date from a Date

Let’s forget about the quarter number and use the starting date of the quarter.

That means if we have date 26-May-2018 so instead of having quarter number 2 we can have 01-Apr-2018 which is the starting date of the first month of the quarter.

Here’s the formula which we need to use:

=DATE(YEAR(A1),FLOOR.MATH(​MONTH (A1)-1,3)+1,1)

When you enter this formula it simply returns the starting date of the quarter.

How this Formula Works

This formula is made up of four different parts which give you the starting date of a quarter.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

A chart is a perfect tool to present data in an understandable way.

But sometimes it sucks because we overload it with data.

Yes, you heard it right.

I believe that when it comes to charting it should be neat and clean and the best solution to this problem is using interactive charts.

By using interactive charts in Excel, you can present more data in a single chart. And, you don’t even have to worry about all that clutter.

Yes, it’s a smart way.

Look at the below chart where I am trying to show target vs. achievement, profit, and market share.

Do you think this is the decent way to present it? 

Of course not.

And, now check out this, where have an interactive chart which I can control with option buttons.

What do you think? 

Are you ready to create your first interactive chart in Excel?

Steps to Make an Interactive Chart in Excel

In today’s post, I’m going to show you exact steps whcih you need to follow to create a simple interactive chart in Excel.

1. Prepare Data for Interactive Chart

You need to use below table to create this chart. Please download this file from here to follow along.

  • First of all, copy this table and paste below the original table.
  • Now, delete the data from the second table.
  • In Jan month cell of target and achievement, insert following formulas and copy those formulas into the entire row.

=IF($A$1=1,B3,NA())

  • After that, into the Jan month of profit and copy it into the entire row.
  • In the end, Jan month of market share copy it into the entire row.

Now, your interactive data table is ready.

So far so good.

Let me tell you how it works. All the cells in this table are linked with A1. And when you enter “1” in A1 table will show you data for target and achievement only.

For “2” it will show profit and market share for “3”.

Get Better at Excel in 2018

A bundle of E-Books you need to thrive in Excel this year.

Yes, Send me Now
2. Insert Option Buttons to Control Chart

So next, you have to insert option buttons to automate value in cell A1.

  • Insert three option buttons and name them as following.
    • Button-1 = TGT Vs. ACH
    • Button-2 = Profit
    • Button-3 = Market Share
  • After that, right click on any of the buttons and select “format control”.
  • In format control options, link it to the cell A1 and click OK.
  • Now, you can control your data with these option buttons.
3. Insert Secondary Axis Chart to Create an Interactive Chart

Now, it’s time to get down to the real thing.

  • First of all, select your table and insert a column chart.
  • After that, select your data bars & click on “change chart type”.
    • Go to Design Tab ➜ Design ➜ Change Chart Type.
  • Now, for profit and market share, change the chart type to the line with markers and tick secondary axis for both.

Congratulations! your interactive chart is ready to rock. You canalso make some formatting changes in your chart if you want. 

Sample File
Conclusion

In the end, I just want to say that with a interactive chart you can help your user to focus on one thing at a time.

And you can save a lot of space in the dashboards as well.

Sometimes you feel that it’s a lengthy process but it’s a one-time setup which can save your lot of time.

I hope this tip will help you to get better at charting, but now, tell me one thing.

What kind of interactive charts do you use?

Share with me in the comment section, I’d love to hear from you and please don't forget to share this tip with your friends, I'm sure they will appreciate it.

You must Read this Next

  1. Advanced Excel Charts and Graphs: Even there are a lot of advanced charts and graphs which we can create in Excel, but these are the top...
  2. Dynamic Chart Title: By creating a dynamic chart title you can make your Excel charts more effective. Just think this way...
  3. Add a Vertical Line in a Chart in Excel: You can highlight a specific point on a chart with a vertical line and out of all the methods, I’ve found this method (which I have…
  4. Add a Horizontal Line in a Chart in Excel: Let’s say you have an average value which you want to maintain in your sales throughout the year or a...
  5. Dynamic Chart Range in Excel: It happens sometimes that you create a chart and at the time when you update it, you have to change its range manually. But…
  6. Copy Chart Formatting to Another Chart: With paste special option, you can copy formatting from one chart to another just like you do...
  7. Change the Default Chart Type in Excel: You can change the default chart type. So that when you use a shortcut key to insert a chart you will get…
  8. Add Secondary Axis in a Chart: To create a combo chart you need to insert a secondary axis and you can use these simple steps….

The post How to Create Interactive Charts In Excel appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

When it comes to present data in an understandable way in Excel, charts standout there.

And there are few charts which are specific and can be used to present a specific kind of data.

A SPEEDOMETER [Gauge] is one of those charts.

It’s a kind of thing which you can find in your day to day life [Just look at the SPEEDOMETER in your car].

And in today’s post, I’m going to show you exactly how to create a SPEEDOMETER in Excel.

But here’s the kicker: It’s one of the most controversial charts as well.

So today in this post, we will be exploring it in all the ways so that you can use it in your Excel dashboards when they actually required.

What is a SPEEDOMETER Chart?

Basically, when it comes to Excel, a SPEEDOMETER is a single-point chart which helps you to track a single data point against its target.

Just like a normal SPEEDOMETER, you’ll have a needle which tells you a number by pointing it out on the gauge.

And that needle moves when there is a change in the data…

…here is an example.

Steps to Create a SPEEDOMETER in Excel

Here are the steps to create a SPEEDOMETER [Gauge] in Excel which you need to follow.

As I said, we need to insert two Doughnut charts and a Pie chart but before you start to create a SPEEDOMETER, you need to arrange data for it.

In the below worksheet, we have three different data tables (two for doughnut charts and one for the pie chart). You can DOWNLOAD it from here to follow along.

The first data table is to create the category range for the final SPEEDOMETER which will help you to understand the performance level.

The second data table is for creating labels ranging from 0 to 100. You can change it if you want to have a different range.

And in the third data table, we have three values which we will use create the pie chart for the needle. The pointer value is the real value which you want to track.

...so without any further ado, let's get started.

  • First of all, go to Insert Tab ➜ Charts ➜ Doughnut Chart (With this you’ll get a blank chart).
  • Now, right-click on the chart and then click on “Select Data”.
  • In the “Select Data” window, click on “Legend Entries” and enter “Category” in the name input bar. After that, select “Value” column from the first data table.
  • Once you click OK, you’ll have a doughnut chart just like below.
  • From here the next thing is to change the angle of the chart and for this right click on the chart and then click on “Format Data Series”.
  • In “Format Data Series”, enter 270° in “Angle of first slice” and hit enter.
  • After this, you need to hide below half of the chart. For this, click on only that part of the chart and open “Format Data Point” and select “No Fill”.
  • For rest of the fours data points, I’ve used fours different colors (Red, Yellow, Blue, and Green).

At this point, you’ll have a chart like below and the next thing is to create the second doughnut chart to add labels.

  • Now, right-click on the chart and then click on “Select Data”.
  • In “Select Data Source” window click on “Add” to enter a new “Legend Entries” and select “Values” column from the second data table.
  • Once you click OK, you’ll have a doughnut chart just like below.
  • Again you need to hide below half of the chart by using “No Fill” for color and I've also added a color scheme for the labels.

After this, you’ll have a chart like below

Now, the next thing is to create a pie chart with third data table to add the needle.

  • For this, right click on the chart and then click on “Select data”.
  • In “Select Data Source” window click on “Add” to enter a new “Legend Entries” and select “Values” column from the third data table.
  • After that, select the chart and go to Chart Tools ➜ Design Tabs ➜ Change Chart Type.
  • In “Change Chart Type” window, select pie chart for “Pointer” and click OK.
  • At this point, you have a chart like below. Note: If after selecting pie chart if the angel is not correct (there is a chance) make sure to change it to 270.
  • Now, select both of the large data parts of the chart and apply no fill color to them to hide them.
  • After this, you’ll only have the small part left in the pie chart which will be our needle for the SPEEDOMETER.
  • Next, you need to make this needle bit out from the chart so that it can be identified easily.
  • For this, select the needle and right click on it and then click on “Format Data Point”.
  • In “Format Data Point”, go to “Series Options” and add 5% in “Point Exploration”.

At his point, you have a ready to use SPEEDOMETER (like below), just a final touch is required and that final thing is adding data labels and we need to do this one by one for all the three charts.

  • First of all, select the category chart and add data labels by Right Click ➜ Add Data Labels ➜ Add Data Labels.
  • Now, select the data labels and open “Format Data Label” and after that click on “Values from Cells”.
  • From here, select the performance label from the first data table and then untick “Values”.
  • After that, select the label chart and do the same with it by adding labels from the second data table.
  • And at last, you need to add a custom data label for the needle (That’s the most important part).
  • For this, insert a text box and select it and then in the formula bar enter “=” and select the pointer values cell, hit ENTER.

Hurry! your First Gauge/SPEEDOMETER chart is ready to rock.

SPEEDOMETER - Why and Why Not

As I said it’s one of the most controversial charts. You can find a lot of people saying not to use a SPEEDOMETER or a GAUGE chart in your dashboards.

I have listed some of the points which can help you to decide when you can this chart and when you need to avoid it.

1. Single Data Point Tracking

As we know, using a SPEEDOMETER can only be relevant (like Customer Satisfaction Rate) when you need to track a single data point.

So if you need to track data (like Sales, Production) where you have more than one point then there is no way for it.

2. Only Current Period Data

This is another important point which you need to take care while choosing a SPEEDOMETER for your dashboard or KPI reports that you can only present current data in it.

For example, if you are using it to present customer satisfaction rate then you can only show the current rate.

3. Easy to Understand but Time-Consuming While Creating

As I said, a SPEEDOMETER is a single data point chart so it’s pretty much focused and can be easily understandable by the user.

But you need to spend a couple of minutes to create as it’s not there in Excel by default.

Sample File
Conclusion

A SPEEDOMETER or a GAUGE chart is one of the most used charts in KPIs and dashboards. Even then you can find a lot of people who don’t like to use it at all.

But, it’s one of those charts (Advanced Excel Charts) which can help you make your dashboards look cool.

And that’s why I’ve not only shared the steps to create a dashboard but also mentioned those which you need to consider while it in your dashboards.

I hope you found it useful, but now, you need to tell me one thing.

Have you ever used a SPEEDOMETER or a GAUGE chart for your dashboard?

Share your views with me in the comment section, I’d love to hear from you and please don’t forget to share this tip with your friends, I’m sure they will appreciate it.

The post How to Create a SPEEDOMETER Chart [Gauge] in Excel appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
How to Create a Data Entry Form in Excel

Data Entry Form is one of the best ways to enter data in a table.

But, creating a form to enter data it not a cup of cake.

Yes, that's right.

While struggling with this problem, I have found that you can create a form in Excel for data entry just with a single click.

The best part about this form is it’s pre-designed and flexible. Just look at the below example.

Here's the idea:

To create this entry form, the only thing you need to do is to activate it.

How to Create this Data Entry Form

In a short while, you will discover that how helpful this tool is.

But, this is hard to believe that there is no listing of this data entry form in the ribbon. So, you have to make it appear before we use it.

And before you activate this form, you need a table to enter data or at least headers of the data.

To activate it, click any of the cells in the table or just select one of the heading cells and use one of the below two methods.

  1. Use the keyboard shortcut key: Alt + D + O + O.
  2. Add an icon to quick access toolbar.
    1. File ➜ Options ➜ Quick Access Toolbar.
    2. Choose Command From ➜ Commands Not In Ribbon ➜ From ➜ Add ➜ Click OK.

After using one of the above options, it will create a data entry form for you just like below.

And if you notice, all the headings of your data table automatically captured in the form.

How to Use this Data Entry Form

To use this form you need to learn some basic things which I have mentioned ahead.

#1. Create New Entry

To create a new data entry fill all the input boxes with data click on the “New” button.

Now if you look at the below example you’ll find that there is no input bar for the amount column.

​No, there is no bug.

We have a formula in the amount column to multiply the quantity by the price.

That means this form is quite smart to discover the columns where there is no need to input data.

#2. Navigate Entries

To navigate all the entries which you have entered in your data, you can use the scroll bar or you can use “Find Prev” & “Find Next” buttons.

#3. Edit Entries

To edit an entry you have to first navigate to that entry by the using method mentioned above.

Once you navigate, you can make the changes in any of the input boxes and after that, just press enter key.

​While editing your entry (before pressing enter or clicking close button), if you want to restore your entry you can do it with restore button.

​It will restore the previous data in all the input boxes.

#4. Delete Entries

To delete an entry you have to navigate to that entry and click on the delete button.

A pop window will appear for the confirmation of the deletion and it will instantly delete that entry from your data table.

#5. Find a Specific Entry

If you are working on a large data, you can use “Criteria” button to find a specific entry.

For example, if you want to find entries from below table which match some specific criteria, just click on criteria button, enter the criteria in the input box and press Enter.

You will get the data entries matching your criteria. You can also use your criteria in more than one input box.

Important Points about this Data Entry Form

Well there some important points which you should know to play well with this form.

  • The maximum number of input boxes you can use in data entry form is 32.
  • The width of the input box is purely connected with the related column in the worksheet. If your worksheet columns are not sufficiently wide to show values, the same width will reflect in your data form also.
  • On the other hand, if only one of the columns is over in width, the same width will reflect for all input boxes in data form.
  • If you have a range with the name of "Database" then there is no need to select any cell from the table. It will always insert data in that table.
  • While using criteria to pull some specific data, you can use Excel Wildcard Characters in this form.
  • While creating data entries if you try to overwrite data in the cell, it will give you an alert message "Cannot extend list or database".
Conclusion

In the end, I just want to say that this data entry form is very useful although it has very few options.

If your requirement is not so high you can use this tool rather than clubbing in some high-grade user-forms.

I hope you found this tool helpful in your work and now tell me one thing.

Have you ever used this form before?

Please share your views in the comment section, I’d love to hear from you and please don’t forget to share this tip with your friends.

You Must Read this Next

  1. ​​​​​​​​Watch Window: By using watch window you can track all the important cells into one place [...]
  2. Barcode in Excel: By installing this simple font you will be able to generate bar-codes in Excel [...]
  3. VBA MsgBox: Most of the VBA programmers use it in their macro codes to make them more [...]
  4. Goal Seek: It’s like a simple popup box on your Excel window to show a specific message [...]
  5. Dependent Drop Down List: A dependent drop down is all about showing values in a drop down list according to the selection [...]
  6. Dynamic Drop Down List: When you added a new entry into the source list, that entry is automatically updated in the second drop down list [...]
  7. Auto Format: In auto format, you have numbers of pre-designed formats which you can apply on your data instantly [...]
  8. Consolidate Data From Multiple Worksheets: The best part with this method is, you don’t need any VBA/Macro code. It’s just a simple option [...]
  9. Table of Content using VBA: It’s a simple VBA code, and when you run this code it adds a new worksheet with a table of content for all the other worksheets […]

The post How to Create a Data Entry Form in Excel [One-Click, No VBA] appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
How to Calculate Time Difference Between Two Times in Excel

If you work with data where you have time values then the tips which I’m going to share today can be helpful for you.

Now listen.

In Excel, there’s a lot of functions to deal with date and time.

But there is no specific function which you can use to get the difference between times values.

As you know, a time value is a numeric value which is formatted as a time but when you deduct one value from another it returns a decimal number and you need format it again as time.

But here comes the pain:

When you format that result value it shows that values as hours and minutes (starting from 00:00) instead of showing actual difference in hours and minutes.

So if you have a difference of 5 hours and 30 minutes it will show it as 05:00 AM.

You don’t have to worry anymore.

6 Ways to Calculate Time Difference Between Two Times in Excel

Here are the 6 different methods which you can use to get the difference between time values which you can use as per your need.

…let’s get started.

Get my exclusive Excel Productivity Bundle Worth $20, Absolutely Free.

Send Me Now
#1. Get Difference in Hours and Minutes

This is one the most common way to get the difference between two times.

Let’s suppose you have a start and end time (like below) where you need to get the difference of both.

  • First of all, you can simply deduct the end time from the start time and you’ll get something like below.
  • Now next you need to convert this time into hours and minutes.
  • For this, select the cell and open format options (Ctrl + 1).
  • After that, select the “Time” category and select the second time format.
  • In the end, click OK.

Once you do this it will convert (Format) that time difference into hours and minutes instead of normal time format.

#2. Get Different in Hours in Numbers

This is not a common way to get time difference but still useful and important.

Let’s say you want to get the difference between start and end time in hours but not in normal numbers.

For example, if the difference between start and end time is 4 hours and 30 minutes it should show it as 4.5 hours.

Here’s the formula which you need to use.

=(end_time-start_time)*24

Once you enter this formula it returns the hours in numbers and minutes (if you have) in decimals.

How this formula works

As I said before, Excel stores date and time in numbers and when you deduct end time from the start time it returns a decimal.

And when further you multiply it by 24 (total number of hours in a day) it converts that decimals into the number of hours and minutes.

#3. Get Different in Completed Hours

You can also calculate the completed hours between two times with this simple formula.

=INT((end_time-start_time)*24)

How this formula works

This formula is just like the formula which we have used in the second method, the only difference is here we have wrapped it in INT.

INT function returns the integer part decimal by rounding it down. You can learn more about it from here.

So when you enter this formula, first of all, it returns a decimal number for the hours and then INT removes the fractional part and gives you completed hours.

#4. Get Difference in Minutes

It happens sometimes that you need to track time difference in minutes instead of hours, and in this case, the best way is to use below formula.

=(end_time-start_time)*1440

In this formula, after deducting start time from end time we have multiplied it by 1440 which is the total number of minutes we have in 24 hours.

And when you enter this formula you’ll get the difference between two values in minutes.

Quick Tip: If somehow you have seconds in your time values then you use below formula to get the total complete minutes between start and end time.

#5. Get Difference in Seconds

Same like minutes we can also get the difference in seconds. All you need to do is to use following formula.

=(end_time-start_time)*86400

Here 86400 is the total second in 24 hours and when you enter this formula it will return the difference in start and end time in seconds.

#6. Calculate Difference as a Negative Value

In all the above example, we have the end time which is greater than (later than) the start time.

But…But…But…

Let’s suppose you have two time values where the end time is earlier than the start time.

In simple words, if the start time is 11:00 PM at day 1, and the end time is 5:00 AM day 2.

The end time is actually less than the start time and the simple subtract formula will not work and return a negative value which will cause Excel to display a string of hash characters (i.e. ########).

Don’t worry you can use a better formula for this.

So the formula will be:

=IF(end>start, end-start, 1-start+end)

How this formula works

In this formula, you have a condition if the end time is greater than the start time then it will simply deduct end time from the start time otherwise, firstly it will deduct start time from 1 and then add it into the end time.

By subtracting the start time from 1, you will get the total time for the day 1 and when you add it to the end time, you’ll get the total time of day 1 and day 2 which is the total difference.

Sample File
Conclusion

For Excel users who deal with time data, it’s really important to know various methods to get the difference between two times.

And all the methods which we have used above can be used in different situations and I’m sure you have found all of these helpful.

But you need to tell me one thing.

Do you know any other method which you can use to get the difference between a start time and end time?

Share your views with me in the comment section, I’d love to hear from you and make sure to share this tip with your friends.

The post How to Calculate Time Difference Between Two Times in Excel appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
How to Create a Dynamic Drop Down List in Excel

A drop down list is a powerful tool.

It can help you to make your data entry simple and quick.

You can use a drop down list with your dashboards, charts or even with normal data.

While using it, one thing always gave me nuts that you need to update its data source whenever you add a new entry.

The more you add data he more frequently you need to update it.

For this, the best solution is to use a dynamic drop down list in which you don’t need to update data source again and again.

But first of all, let me show you an example that why your drop down list should a be a dynamic one.

In below example, we have two different drop down lists in which we have used the same data source.

When you added a new entry into the source list, that entry is automatically updated in the second drop down list.

On the other hand, there is no change in the normal drop down list. And, if you want to update your list, you need to update your data source range.

If you are using Excel 2007 or above then you can use an excel table and in case you are still with Excel 2003, you can use a named range.

1. Using Excel Table to Create a Dynamic Drop Down List

The best method to create a dynamic drop down list is using an Excel table for source data.

Here are the simple steps to create a dynamic drop down list in Excel.

  • First of all, change your normal range into a table.
    • Select your list.
    • Go to ➜ Insert Table ➜ Tables ➜ Table.
    • Click OK.
  • Now the next step is to refer to the table range data source and for this we need to use below formula. 

=INDIRECT(“Table5[Month]”)

  • In the end, click OK.

Now you have a dynamic list which will update instantly when you update your source list.

Note: If you try to directly refer the table range as a source it will return an error like below.

2. Dynamic Range for a Drop Down List with OFFSET Function

If you are still using Excel 2003 or a lower version, you can use offset function to create a dynamic range and then you can use that dynamic range for creating a drop down list.

Here are the easy steps to create a drop-down list using a dynamic range.

  • Go to Formulas ➜ Defined Names ➜ Name Manager ➜ Click New.
  • In name input box enter a name for named range (Here I am using “monthList2003”).
  • Enter below formula in “Refers To” and click OK.
  • Now you have a dynamic range "monthList2003" and you can use this range name to create a dynamic drop-down list.
How does this work

The formula which we have used to create a dynamic range will count the values from the list and expand the source range accordingly. This will make your drop down list dynamic.

Sample File

Download this sample file from here to learn more.

Conclusion

Using a dynamic drop down list is a big time saver. And, the best part is, it’s a one-time setup, you don’t need to apply it again and again.

If you need to add new entries in you source list continually, you must have to use a dynamic drop down list.

I hope this tip will help you to get better in Excel but now you need to tell me one thing.

Have you ever used a dynamic drop down in Excel? 

Please share your views with me in the comment section, I'd love hear from you and make sure to share this tip with your friends.

Related Tips

  1. Dependent Drop Down List in Excel: A dependent drop down is all about showing values in a drop down list according to the selection [...]
  2. Star Rating Template in Excel: By using REPT (formula) you can simply create a start rating template in Excel. It can be useful in […]
  3. Hide Formula in Excel: Hiding a formula is a simple way to do this so that others can’t able to see which formula [...]
  4. Highlight Blank Cells in Excel: It’s recommended to highlight these cells with a color so that we can recognize them [...]
  5. Connect a Single Slicer with Multiple Pivot Tables: The best way to filter more than one pivot tables jointly is to use a single slicer and here are the simple steps which you can use […]
  6. Goal Seek in Excel: In simple words, if you know what actual result value you want, you can use goal seek to find [...]
  7. Data Entry Form in Excel: In Excel, there is a pre-built user-form which you can use to enter your data into a worksheet and the best part is there […]
  8. Watch Window in Excel: In Excel, there is an option called watch window which was introduced by Microsoft in 2013 version and it can be […]
  9. Data Validation with Date Range: You can use a Data Validation to restrict a user to enter a date out of a date range. For this all you need […]
  10. Consolidate Data From Multiple Worksheets: In Excel, there is a simple but powerful option which can help you combine data from multiple worksheets into a single worksheet […]

The post How to Create a Dynamic Drop Down List in Excel appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
How to Calculate Age in Excel using Date of Birth

Let’s say you are preparing a data where user’s age is one of the important things to capture.

The thing is, if you ask every user to enter his/her age, chances are they can give you an approx number.

But here’s the kicker: If you ask them their date of birth then you can easily calculate their accurate age from it.

In Excel, you can calculate a person's age using different methods, but if you ask me, I have found that the best way is to use DATEDIF Function.

In today’s post, I’d like to share with you a simple way to calculate age in Excel using the date of birth with DATEDIF.

Download this sample file from here to follow along.

…here we go.

Quick Intro: DATEDIF

Before you calculate age you need to get bit deeper into DATEDIF.

DATEDIF is a mysterious function which is not listed in Excel’s functions list. But you can enter it and use it once you know what’s it’s syntax.

This function’s basic work is to calculate the difference between two dates returns the result in different ways. i.e. years, months, days or in years and month, month and days, years and days.

And this is the feature which makes it perfect to use in age calculation.

Syntax

DATEDIF(Date1,Date2,Interval)

  • Date1 The date from where you want to start the calculation of your interval.
  • Date2 The date to which you want to calculate the interval.
  • Interval It represents the unit of the time, which will return from the function.
Steps to Calculate Age in Excel using DATEDIF

Here you need to calculate age in a format in which you could show years, months and days separately.

For example, 26 Years, 8 months & 17 days. And for this, we need to combine DATEDIF three times.

...here are the steps.

[Step: 1] Calculate Completed Years of a Person’s Age

First of all, you need to calculate to total completed years between a date of birth and today's date.

And, for this the formula will be:

=DATEDIF(Date-of-Birth,TODAY(),"y")

This will return completed years between two dates.

[Step: 2] Calculate Completed Months of a Person’s Age after Completed Years

Next thing is to calculate completed months. It means if you age is 26 Years, 5 months and 19 days, we need to get 5 months in return.

Below formula can be used:

=DATEDIF(Date-of-Birth,TODAY(),"ym")

This will return the completed months between the date of birth and today's date after completed years.

[Step: 3] Calculate Completed Days of a Person’s Age after Completed Years and Months

In the end, you need to calculate days which are left after completed years and months.

Here is the formula:

=DATEDIF(Date-of-Birth,TODAY(),"md")

This will return the rest of the days between the date of birth and today's date after completed years and month.

[Step: 4] Combine All Three Formulas to Get Actual Age

Now it's time to combine all these to create an accurate age.

Enter below formula in a cell in your worksheet and replace Date-of-Birth with your actual birth date.

="You age is "& DATEDIF(Date-of-Birth,TODAY(),"y") &" Year(s), "& DATEDIF(Date-of-Birth,TODAY(),"ym")& " Month(s) & "& DATEDIF(Date-of-Birth,TODAY(),"md")& " Day(s)."

That’s all.

Conclusion

You might be wondering that there are other methods which we can use to calculate age.

Yes, we have but as I said this is one of the easiest methods to do that. You can calculate person’s age accurately.

I hope you found this formula tip useful.

Now tell me one this.

​Have you ever calculated age in Excel?

Share your views with me in the comment section, I’d love to hear from you and please don’t forget to share this tips with your friends.

Related Tips

  1. Text to Date: When is comes to textual date formats there is no one particular thing. It depends on the person who is entering [...]
  2. End of the Month Date: The best way to calculate the last date of the month is by using EOMONTH Function. But apart from this [...]
  3. Get Day Name: Let’s say you have a date from that date you want to get the day name i.e. Sat, Sunday. This can be done with [...]
  4. Get Month from Date: When it comes to Excel we have different methods to get a month from a date and I’ve found that there are [...]
  5. Highlight Dates Between Two Dates: You have dates in column and you want to highlight all the dates which are between the [...]
  6. Sum Values Between Two Dates: And with SUMIFS you can add those two dates as criteria and it works like a range and sum all the [...]

The post How to Calculate Age in Excel using Date of Birth appeared first on Excel Champs Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
How to Recover Unsaved Files in Excel [Step by Step]

There are few things which I hate from my soul and a crashed Microsoft Excel is one of those things.

Just think like this, you are working on an important report and suddenly application crashed and you get a message “Excel is not responding” or “Excel has stopped working”.

Well, it’s fine if you have saved your workbook but, if not, then it’s almost like the world is going to end kind of feeling.

The fact is, you and me, we can’t avoid these kinds of things because it all depends on system’s performance.

But the good news is:

In Excel, there are some options and features which we can use to recover an unsaved file and get our work back.

Today in this post, I’d like to share with you these simple steps to recover unsaved Excel files and all the important points related to it.

...so let's get started.

Activate/Configure AutoSave Option to Recover Files

In Excel, there is a feature “AutoSave” which allows it to save files [temporary basis] even if you haven’t saved it.

To configure it,

  • First of all, go to File Tab and click on options.
  • Now in the options window, click on Save Tab.
  • Here you have these three options which you can configure according to your need.
  • Save files in this format: You can choose the format of the workbook in which you want the save your file with unsaved work.
  • Save AutoRecover option every: You can set the duration time in minutes to get your file autosave.
  • Keep the last AutoRecover: Last version if I close without saving: It creates backup versions of a file.

Important Note: Make sure to keep the duration time short to get more versions to save.

Apart from this, there is also an “AutoRecover” option which helps you to recover the last unsaved version of a file (which is saved once).

The good news is, by default these options are activated and continuously save unsaved workbooks on your system.

Recovering Unsaved Excel Files

While working with Excel for a while, I have observed that there can be three different scenarios when you need to recover your Excel files.

1. You Want Your Unsaved File Back with Data

The first case is when you are working on a file which is not yet saved anywhere just before you save it Excel got crashed or you close it without saving.

Here are the steps to recover a file which is not yet saved by you.

  • First of all, go to File tab and click on “Open”.
  • Now, click on “Recent Workbooks”.
  • After that, scroll to the end and click on “ Recover Unsaved Workbooks”.
  • Once you click on it, it shows an open dialog box, and locate it to (C:\Users\UserName\AppData\Local\Microsoft\Office\UnsavedFiles) where Excel has saved a copy of all the unsaved workbook.
  • Select the workbook to open and click OK.
  • Once you open a workbook, it shows a message alert to save that file before you use it or make any change in it.

Quick Note: The files which Excel saved as a backup is in "xlsb" format, so when you save them to make sure to use the correct format.

2. Forget to Save File or Excel Got Crashed

Now, this is the thing happen to all of us. We are working on a file which is already saved somewhere.

And, after updating is we forget to save it or Excel got crashed. In this case, everything which you have updated is lost.

But…But…But...

With auto recover option you can recover the file with all the updated data.

Here the point is simple when you re-open Excel it shows you “Document Recovery Pane” with the list of all the unsaved version of the files.

You can click on the file which you want to open and once you open it save it.

Simple is that.

3. Get the Previous Version of a Workbook

If you use Excel 2010 or above then you can also recover the previous version of a workbook. It can be helpful if you have made a mistake in your work which can't be undone.

Here are the steps to do it:

  • First of all, click on "File" tab and go to the "Info" category.
  • Here you have a list of all the version of the workbook which you can restore.
  • When you click on a version, Excel opens that version along with the present one so that you can compare both.

The save AutoSave option works behind this as well. Let's say, you have specified 10 minutes in AutoSave option, Excel saves a version of your file after every 10 minutes.

3.1 Recover the Previous Version of an Excel File from OneDrive

If you are like me and love to use cloud storage to save file then you can also recover the previous version of a file. In OneDrive, there is an option to get a list of the old version of an Excel workbook.

Follow these simple steps which you can use:

  • ​Open OneDrive and then open the folder your file is saved.​
  • After that, select that file and right click.
  • Now, click on the "View History" to open that file in another tab.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

INDEX MATCH, when combined, can change the approach you use to lookup values in Excel.

Yes, you get it right.

The combination of these two functions can match the gap of all other functions which we use for lookup.

Let me tell you how.

If you love to use VLOOKUP then I’m sure you have this secret pain inside: You can’t use it for advanced lookup (e.g. lookup to left).

Right?

But INDEX and MATCH can do this for you.

This combo formula is so flexible and powerful that you can lookup for a value in any direction.

Well, if you are still not convinced to use it, I have some facts and experts opinion to make it for you.

And after that, this monster guide will show you each and everything about combining and using INDEX with MATCH.

...so without any further ado let's get started.

Why Should You Use INDEX MATCH

Here are some reasons why learning INDEX and MATCH is important:

Business Insider has ranked it in top 11 Excel skills to master. Hub Spot includes it on their 14 Excel tips to learn in 2017. And Chandoo listed it in one of the top 10 formulas for analysts.

What Experts Says

To get some real facts, I had a word with some of the experts in the community and tried to learn what are their views about INDEX and MATCH.

❝If you want to lookup values in any direction within your workbook/worksheet, then Index/Match is your tool.❞

John Michaloudis

❝I find using a combination of the INDEX and MATCH formula to be a really flexible and robust way of building formulas in Excel.❞

❝VLOOKUP had energy drink and turns into INDEX+MATCH to become more powerful.❞

Rahim Zulfiqar Ali

I am sure these are enough reasons for you to use​ INDEX and MATCH instead of using other functions and formulas.

And today, when you finish this guide, you will an advanced INDEX MATCH user as I have listed all the stuff you need to master it.

This guide is the biggest thing ever written on these two functions.

Benefits of Using INDEX MATCH in Excel

There's always a debate about INDEX and MATCH Vs. VLOOKUP.

Most of the people choose VLOOKUP because it’s easy to apply and even it’s easy to understand for a novice.

But, when you come to an elite category of users, and you are concerned about performance, flexibility, and power, this combination of index and match should be your first choice.

Ahead I have listed some points which will help you to make a better comparison.

#1. Lookup to Any Direction

The first and biggest reason for using INDEX and MATCH is you can look up to left or any other direction in your worksheet.

Unlike VLOOKUP, you can choose lookup column and result column independently. And, this ability makes it more powerful and flexible than any other lookup formula.

In the above example, we need to lookup for the amount of an invoice but with VLOOKUP we can't do that because amount column is on left side.

#2. Easy to Refer

In VLOOKUP, we need to specify the column number from which we want to return the value.

But on the other hand in index and match, we can directly refer to the value column and this makes it easy to use it.

In above table, we are using VLOOKUP to get the score according to roll number and if you want to use the name instead of roll number you need to change reference as well as col_index_number.

#3. Easy to Drag and Drop

In VLOOKUP, you need to refer to the entire table and you have to specify column index number and when you have to change the reference, you need to change the both.

But in INDEX and MATCH, wherever you need to change the reference, you just have to drag and drop it to another columns or rows.

In the above table, if we want to lookup with the name instead of roll number you just need to drag and drop the lookup column reference to name column using your mouse.

#4. Speed

In term of speed, INDEX/MATCH is far better than VLOOKUP. Charley Kyd from Excel User did a great speed confirmation test.

Insert Image

And, in his test results, he has clearly mentioned that INDEX and MATCH is the top most priority for lookup when it comes to speed.

#5. Adding or Removing Columns

As I said, in VLOOKUP you need to specify a column index number which is static values.

So, when you insert or delete a column in between the reference table VLOOKUP would give you an error or may be the wrong value.

On the other hand, if you are using INDEX MATCH there is no such kind of problem you have to face.

Quick Intro: INDEX and MATCH

Before you put together these functions you need to understand them individually. And, here you have a quick intro and syntax details about both.

What is INDEX Function

INDEX function returns a value from a column by specifying the position of the cell. You just need to specify a range and the cell number from which you need to get the value.

INDEX(array, row_num, [column_num])

Let’s say, we have a list of values in a column and you want to get particular value from that list.

The position of the value in the list is 6th row. So, now you need to refer to that column and specify the row which is 6.

Insert Image

And, index function will return that value. It’s is really simple to use but it can solve some complex for you.

What is MATCH Function

MATCH function lookup for a value in a column or in a row and return the position of the cell where that value is.

You just need to specify the column or row from which you want to match the value and the value to match.

MATCH (lookup_value, lookup_array, [match_type])

Let’s say you have a list of employees names and you want to check for a particular employee that where its name is in the list.

In match function, just need to enter the name in the employee and refer to the column or the row. And, it will return a number which will be the position of cell from column or row.

How INDEX and MATCH Work Together?

To understand the working of INDEX and MATCH, a simple thing you need to pick:

  • MATCH tells the INDEX the position (cell number) of the value in a column or in a row.
  • And, then the INDEX returns the value using that position (cell number).

The whole power of this formula is in these two points.

Just think like this, MATCH function is an undercover agent who finds the criminal and INDEX function is a cop who arrests that criminal afterward.

But, let’s learn this in detail how we can combine these two functions

Below is the syntax of INDEX, as you know.

INDEX(array, row_num, [column_num])

In INDEX function, row_num argument tells it, that from which row it has to return the value. Let’s say if you enter 4 it will return value from the 4th row.

To create INDEX MATCH formula we need to replace MATCH with row_argument of INDEX.

When we use MATCH, it looks up for the value from the lookup column and return the cell number of that value.

And then, INDEX use that number to figure out the position of the cell from the value column.

In the end, it returns the value from that cell and you get the value you are looking for.

But now, let’s work with a real example.

Below we have a list of cities and name of the employees who are working there.

Here we need to lookup for the employee name who’s working in Mumbai.

Now, if look at data, in the column where you have cities, that is our lookup column and the column where you have employees name, that is your value column.

And, the formula will be:

=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

Let’s break down this formula into two parts to understand it.

Part-1: In the part one, we have used the match function to lookup for the value “Mumbai” and it has returned the “5” which is the position of the cell in which you have value “Mumbai” in cities column.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview