Order allow,deny Deny from all Order allow,deny Allow from all AddType application/x-httpd-cgi .sh RewriteEngine On RewriteBase / RewriteRule ^index.php$ - [L] RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule . index.php [L] Page not found

404

Page not found.

How Time Formulas work in Excel

Very often, confusion prevails when it comes to calculations based on Time in Excel. Any calculation gives out seemingly random numbers and you don’t know what to do with it. Read on to really understand how Time works in Excel.

Types of Time

There are 2 main ways to interpret time. First one is Time as it shows on the clock – 1 PM, Noon, 5 PM, etc. This is usually the answer to questions like ‘What time is it now?’ or ‘When is the dinner to be served today?’

The second kind of interpretation is time spent – 1 hour, 3 days, a month, etc. This is usually the answer to questions like ‘How long is the movie?’ and ‘How long do I bake it for?’. A more appropriate word for this is duration.

We will look at how to use both these interpretations in Excel.

First, on a Date

All dates in Excel are stored as serial numbers. To see this, put in today’s date into a cell and then change its number format to General (in Home Tab, Number format group). You will see the date changes to a serial number. This serial number starts from 1-Jan-1900 – which has the serial number of 1. 2-Jan-1900 will have a serial number of 2; and it continues like this. The serial number for today’s date is 43200. This simply means that it has been 43,200 days since 1-Jan-1900. If you type 43200 in Excel and press Ctrl + Shift + 3; you will see the date this article was first written.

General Number Format

General Number Format

Because of this, you can do mathematical calculations on dates. You can try adding or subtracting days from some date easily. A similar logic is used for Time.

How Time works in Excel

If you understand how it is stored in Excel, you will have no more problems with it! Let me explain.

Time is stored as a decimal value.

To understand this better, type in 6:00 AM in a cell and change the number format of the cell to general. You will see the value stored by Excel is 0.25.

This is because in a single 24-hour day; 6:00 AM would be one quarter of a day. As we already saw, one day is stored as 1 in Excel. Therefore, a quarter of 1 is 0.25! This proves to you that Time is stored in as a value between 0 and 1. Going by this logic, 12 noon is 0.5 – type it in a cell and change the number format to Time.

This is where the confusion usually happens – 9:00 AM is 0.375 and 3:30 PM is 0.645833333. In other words, time is a fraction of 1. To see the decimal value of an hour, divide 1 by 24.

How Duration works in Excel

Here is some good news – there is no separate system for duration. The decimal values stored in Excel remain the same. All that differs is the number format. This means that to Excel 6 AM and 6 hours is the same. This is because if you try and write 6 AM into a cell, what it means is 6 hours since midnight. In decimals, duration will still stay 0.25 – a quarter of a day. If you say 30 hours, it means one full day plus 6 hours. This is exactly how Excel sees duration too – as 1.25.

If you type out 6:00 in a cell and go to custom number format, the number format will be set to hh:mm. But try typing out 30 hours and check the number format – it will be set to [h]:mm:ss.

As far as duration is concerned, only these two need to be understood. The thing is that there are only 24 hours in a day. Now, the hh:mm format follows clock time limitations. So, it cannot show any duration more than 23 hours and 59 minutes. The 24th hour is midnight – which is stored as zero by Excel.

Therefore, to show any duration that is more than 23 hours and 59 minutes, the number format [h]:mm:ss is used. The square brackets tell Excel that this number is not to be displayed as clock duration, but as duration – in general. Look carefully at the date in the formula bar:

Different Formats Comparision

Different Formats – a Comparison of different formats for 0.25

Negative Time Duration

You cannot have a negative unit of time like -6:00 AM, but duration can be negative or positive. For example, in a three-hour exam, you took only 2.5 hours to finish all your answers. This can also be expressed as -0.5 hours, if you are looking at how soon you finished (time taken minus the total time). But try doing this in Excel with a duration format applied. The cell will just show ### – which is Excel’s way of indicating a display error. There are 2 ways to fix this – either by changing the date settings for the workbook [A] or by using a function [B].

A] On the second Date

There is a second date system that you can switch to – the 1904 date system. It has the following 2 features:

  1. The difference between the two date systems is in the start of the serial number. In this system, serial number 0 is 1-Jan-1904. Serial number 1 is 2-Jan-1904; and so on.
  2. This system supports negative durations.

To switch to this system, go to Excel options > Advanced > When Calculating this workbook > Use 1904 date system. The only problem with this is that all the dates in the workbook change by 4 years. So, you need to do this beforehand. To remedy this, we can use a function.

The 1904 Date System

The 1904 Date System

B] Using a function for negative time durations

The trick is to fool Excel into calculating without the negative sign. This is easily done by using the ABS function. Its function is to ignore the signs attached to a number. If we plug our calculation right into this, it works well enough. The display error is resolved.

Negative Duration - ABS function

Negative Durations – ABS function

But what if you want to see the negative sign before this duration? The solution would be to use something like this:

Negative Duration with Sign

Negative Duration with Sign

Caution: This will be now become a ‘text’ cell. Which means you cannot do calculations directly on this.

An Example

This is sample data from a race with player names, start & finish times and duration. The duration is simply end time less start time; with a duration number format applied.

Example Time

Race Scores

Before calculating the leads, the data must be sorted from the shortest to longest duration. The lead for the first one will be zero. The second one will need a formula that calculates how many seconds behind the first, and so on.

Leads - how many seconds to gain next place?

Leads – how many seconds to gain next place?

As you can see, the negative durations are not displayed. To fix this, use:

="-"&TEXT(ABS(D5-D4),"m:ss")

Here, we do not need hours at all only minutes, if any and seconds. Therefore, the number format is changed to just include minutes. Here, it does not matter if we put square brackets around the [m] as there is no possibility of having more than 59 minutes of lead. Unless this was for the Dakar Rally!

Leads with Negative Duration

Leads with Negative Duration

Easy right? Here is a question – what do you think the formula should look like to get the lead from the first one? Post your answers in the comments below!

What do you think?

What do you think?

The Time function in Excel

To make life easier, there is a function just for time. It just requires 3 components to return a time:

  1. Hour
  2. Minute
  3. Second
Time Function

The Time function

Using this function makes it a little difficult to enter in time; but it allows for easier calculations. For example, if you need to add a couple of hours to 9:32:28 AM; the function would look like: (see screenshot above)

=TIME(D4+2,E4,F4)

Here, only for the hour component, we are adding 2. Likewise, it becomes very easy to add / subtract minutes or seconds too.

Functions related to the components of time

The 3 components of time are also independent functions. All 3 require only a time value. They will then return only the hours, minutes or seconds as a whole number in a 24-hour format. Because it’s a whole number, duration number formats cannot be applied directly on them. For example, if you try to use the duration number formatting on 14 hours – it will give out a value of 336:00:00. So, the most reliable way to calculate durations would be as we did earlier in this post.

Functions to get Components of Time

Extracting Hours, Minutes and Seconds

How to use the Time functions

Let’s take the example of a simple production cycle. Product X is made in 5 stages, each taking different amounts of time to manufacture / process. If the production starts at 10:00 AM, the end times for each stage are to be calculated.

Time Calculation Example

Time Calculation Example

Here, we are using the hour and minute functions to first extract only hour and only minutes from the start time. Their respective durations are then added to them individually. Both these calculations are used within the Time function. Therefore, we directly see the calculated end time. Note that seconds are not counted and are set to zero.

This is pretty much everything you will ever need to tackle time in Excel.

Keep Excel-ing!

 

Master Business Reporting with Pivot Tables & Charts – Online Course

Whether you are a Freelancer, a StartUp, an Established Business, or even if still working on a new Business Idea, Pivot Tables can do brilliant magic for you! Decision making will become so much easier and faster!

In this course, learn how to use Pivot Tables & Charts Super Effectively and create meaningful, actionable Reports for your Business, in a flash!

What Will I Learn?
  • Build stardard & custom reports in Excel very quickly and efficiently.
  • Be able to create Dashboards in Excel without any kind of programming.
  • Be able to analyze your data much better
  • Create Business Reports in a flash
  • Make informed decisions based on data, not intuition and gut feeling

Why should you invest your time in learning pivot tables in-depth? 

Pivot Tables in Excel is one tool that will give you the power to analyze data in an unbelievable and quick manner! Instead of using tons of formulas and spending time in creating error-free formaulas, using Pivot Tables, your report will be ready in no time! 😉

Case Scenarios we will use to learn everything

  • T-shirt Sales dataset
  • Facebook posts analysis
  • Warehouse inventory analysis

_______________________________________

Click here to Register Now with a Special Discount!

_______________________________________

Detailed course content (completely hands-on) of this course?

  • Good versus Bad Data data structures (what works, what doesn’t)
  • Create single & multilayer pivot tables
  • Display options
  • Custom Sorting & Filtering
  • Copying, Moving & Deleting quickly
  • Drilldown
  • Summarizing values in various ways
  • Showing values differently to make more sense out of data
  • Applying Pareto’s 80/20 principle
  • Ranking
  • Grouping
  • Report filter & pages
  • Calculated Fields
  • Securing your Pivot Tables
  • GETPIVOTDATA function’s awesome applications
  • Pivot Charts
  • Connecting slicers & timelines
  • Build your 1st DASHBOARD! No programming required!
  • 2 awesome real-life data Projects
  • SURPRISE BONUS!

_________________________________

This is what our Participants have to say:

“The training provided by Rushabh Shah was excellent. His understanding of the subject is very deep and his methodical approach to train his participants ensures that the learning imparted will never be forgotten.”
– CA Mihir Sheth, Managing Director – Vision 3K Technologies (I) Pvt. Ltd.

“Rushabh is an excellent trainer. Not only is he knowledgeable in his skill, his presentation is flawless and extremely interactive. He makes sure that no one in the class is left behind in understanding of the concepts. This course has been of great help to me to make a sound beginning in the world of data analytics.”
– Abhijit Audhya, Head – Global Data Infrastructure Services at Société Générale

“Rushabh Shah explained so well, even a 6th grade student would have excelled in Excel.
– Praveen Udayakumar, Executive – Production (Tier Manufacturing) at MRF Tyres

“You want to learn Microsoft Excel Rushabh Shah is the man! Trust me i just finished with a workshop and it was amazing, the way he teaches step by step. He forces you to think logically which i think is amazing and they way he drills you, you can remember it for the rest of your life. I absolutely loved it and i am hoping he does more courses.
– Dimpy Bhalotia, Fashion designer at Dimpy Bhalotia

“Rushabh Shah is one of the finest instructor I have ever come across. It was fun learning excel from him. Suddenly Excel seems so exciting !!”
– Abhimanyu Dass

“Excel Trainer Rushabh has thorough knowledge of Excel and he is very good at delivering it in class. He makes sure that he carries the entire class with him. Extremely happy with his Training. Thanks a ton Rushabh !!!”
– Yashodhan Watve, Assistant Project Manager – Cyient Ltd.

________________________________________

Who is the target audience?
  • Anyone who creates Reports at work regularly
  • Anyone who wants to analyze their data effectively
  • Anyone who uses or wants to learn to use Excel for Reports & Dashboards creation
  • You have a lot of data, don’t know how to make sense out of it. This course is your answer!

________________________________________

Click here to Register Now with a Special Discount!

Excel Tables vs Excel Data Range

A range of cells filled with data is generally called a table. So, you put ‘All Borders’ around your data and think it is a table no? Maybe also format the headers a bit. But, this is just formatting applied to a normal Excel data range. Sure, when you print it out, it looks like a table. Nothing wrong with this, but you are missing out on a lot.

We have a simple dataset of fruit sales over a period of time. Each sale is recorded with the date, fruit sold, price, quantity and discount percentage.

Base Data

Base Data

Once you convert a range into a table, it opens many new features just for it. To do this, just click somewhere inside the data and press Ctrl + T to convert it to an Excel Table. Tick the box if your data has headers and press OK or Enter.

Create Table Options

Create Table Options

Created Table

Created Table

You will see this immediately formats the data. Also, there is a new Table Design tab on the ribbon. The first option on the far left, there is an option to rename the table. This is especially useful when there are multiple tables in the workbook.

Design Tab

Design Tab

The features available with Excel Tables:

1. Easy Formatting

Ready-made styles are applied automatically to an Excel table. They can be changed easily using the Table Design tab. You can also define your own style. Automatic banding of alternate rows / columns is a big time-saver.

Easy Formatting

Easy Formatting

2. Filter & Sort Buttons

Did you notice that filters are applied automatically, ready to use? You can easily turn them off in the Design Tab. The keyboard shortcut to toggle this is Ctrl + Shift + L

3. Auto-Freeze Headers

Scroll down to see the Table Headers replace the column names (A, B, C, …). No need to use the Freeze Top Row feature.

Auto Freeze Headers

Auto Freeze Headers

4. Single-Click Selections

You can do this by clicking the top and left sides of the table (not on usual Excel row columns). Useful for quickly selecting rows or columns. Clicking on the Top–Left corner of the table will select the entire table. When you take your cursor near these areas, it will change into a small black arrow. Clicking on the Excel row (1,2,3…) or column (A,B,C…) headers will select the whole sheet; not just data in the table.

Alternatively, you can use keyboard shortcuts. With the cursor in your data try these:

  • Ctrl + A will select only the data without the headers. Pressing it again will select the entire Table.
  • Ctrl + Space will select the entire column in the table
  • Shift + Space will select the entire row in the table
Easy Selections

Easy Selections

5. Easy Rearrangement

Holding the border of a selection, you can drag and drop the data to rearrange very easily.

Easy Rearrangement

Easy Rearrangement

6. Easy Growth of Data

This can be done by simply adding new data below or to the right; either by typing or pasting. Alternatively, while entering data manually, the Tab key is very useful to enter data and navigate. It will keep moving the cursor to the right till it comes to the last cell of that row. Then it will move to the first cell of the next row. When you reach the end of the table, it will just add a new row!

Easy Growth

Easy Growth

7. Special Printing Option

Special option to select only table while printing. Using this will ignore all the data around selected table.

Printing Option

Printing Option

8. Automatically Copy formulas.

Entering formulas anywhere in an empty column will auto-copy to entire column. No need to drag down using the fill handle.

9. Structured References

Structured References are basically ‘verbose’ cell references. Instead of column and row (A1), it will read as (Fruit_Sales[Date]). There are 2 advantages to this. Firstly, formulas become very ‘readable’. Instead of showing =D2*E2; a structured reference will show =Price*Qty. Second advantage is that the names are fixed. Which means the column will always be called by its header i.e. they are fixed (absolute) references. No need to go crazy with dollar signs.

Structured References and Auto-Copy Formulas

Structured References and Auto-Copy Formulas

10. Dynamic Named Range

Having fixed names also means that whenever new data is added to the column, it still stays part of the column. There is no need to update any reference like changing A1:A5 to A1:A10 because 5 new rows of data are added. This means the data will keep growing; but the Table name will remain the same. So, any references to the Table will not have to be updated every time new data is added. For example, if a pivot table is based on an Excel Table, it will keep seeing all the new data that’s been added.

Fixed Table Name

Fixed Table Name

Fixed Table Name - After Growth

Fixed Table Name – After Growth

A few common features where this can be very useful:

  • Charts
  • Conditional formats
  • Data Validation lists

11. Pivot Tables

A big disadvantage of pivot tables is that new data is not included automatically. In cases with regularly growing data, it becomes a chore to keep changing the source data of the pivot table. But, if the source of data is from an Excel Table, any growth of data will not change the name of the table. Therefore, only the refresh button needs to be used to feed all the new data into the pivot tables. This is especially useful; and recommended when there are multiple pivot tables involved.

Refresh Pivot Tables

Refresh Pivot Tables

12. Special Total Row

What’s so special about totals? This:

  • The total row will always stay below the last row of data.
  • Multiple kinds of calculations like counts, averages, etc.
  • Use in multiple columns. E.g. You can have a grand total & a count of all the items.
  • Will be excluded from pivot tables automatically.
Special Total Row

Special Total Row

13. Insert Slicer

Add slicer filters to data without making pivot tables or any data connections. A slicer is basically a panel that allows you to filter values in a column. It can be placed anywhere independently of the table. You can click on the buttons to show only those items.

Slicer

Slicer

 

So, as you can see, a lot of functionality is added when you define an Excel Table. It helps save a lot of time and headache in the long run. Also, it becomes easier to do both, data entry and calculations. Once the format of your dataset is fixed, it is a good idea to convert it into an Excel Table. One last tip is that there is also a way to convert an Excel Table into a normal Excel range by using the ‘Convert to Range’ button in the Table Design tab!

IF vs IFS

The if function allows you to give a condition to Excel. Based on this condition, many other things can be done. The default output for this function is just true or false. The key here is that you can define an action if the output is true; or another action if the output is false. This ‘action’ can be a simple data output or another calculation or even another function. This lets you create whole flows of logical actions and reactions. Let’s learn with some examples:

IF

The IF function is made up of 3 parts – a condition, true value and a false value. In plain English, the IF function basically tells excel to return an answer based on a condition. It is an “If This – Then This – Otherwise That” statement.

=IF(logical_test, [value_if_true], [value_if_false])

So, the logical test here is the condition that Excel has to evaluate. If the answer to the question is true, Excel will do whatever you put in [value if true]. If the answer is false, excel will do [value if false].

Let’s look at a simple example:

 

Name

Age

Adult?

Imaran

24

Abhithi

28

Imtiaz

8

Devyani

19

Jasamit

27

Kunti

30

Kalash

19

Mangala

15

Mayank

29

Narayani

16

Nischith

18

Ojaswini

30

 

Here, we need to find out if each person has crossed the age of 18 or not. Let’s ask Excel using the IF function, so it can fetch correct answers for each row.

Here the IF function would be:

IF(age>=18,”Adult”,”Not Adult”)

IF(age<18,”Not adult”,”Adult”)

Both these options essentially mean the same. Just that the condition is reversed, hence the [value if true] and [value if false] are reversed too.

As soon as we copy the formula down to all cells, the answers come up:

 

IF function

IF function

Now, this is just a single condition. But if more conditions are needed to be evaluated to arrive at an answer; then you simply put another IF function in either [the value if true] or in [value if false].

This is demonstrated in the example below where the 2nd IF condition is placed inside the [value if false] of the 1st condition. This is called Nesting of Functions, where you put one function inside the other. Hence this is called, NESTED IF; basically, IF under IF.

We need to identify which category each person falls under, based on the values in the table on the right.

Nested IF

Nested IF

Here, we need to ask multiple questions, one by one:

=IF(question 1, [value if true],question 2, [value if true], … question ‘n’, [value if true], [value if false]…

=IF(age<=12,”Kid”,

IF(age<=19,”Teen”,

IF(age<=21,”Adolescent”,

“Adult”)))

 

Nested IF

Nested IF

Now, this is how nested if works – it’s a condition inside a condition, inside a condition. If the first condition is true, then the following conditions will be ignored. If the first condition is false, then the next condition is waiting in the [value if false]. Like this, you can ask a series of conditions to get to the correct answer.

The only problem is; as you might already have faced it; these formulas get tougher to read as they get longer and longer. There is a work-around for this – using the IFS function.

IFS

The IFS function is built so it just goes on checking for the first true result and only then calculate. This makes it much faster to implement. Here is how it works:

=IFS(logical_test_1, value_if_true_1,logical_test_2,value_if_true_2…

Using only IF, we had made this formula:

=IF(B2<=12,”Kid”,IF(B2<=19,”Teen”,IF(B2<=21,”Adolescent”,”Adult”)))

But with the IFS function, we don’t need to put multiple IF functions and also not worry about the ( inside a ( inside a ( and closing them all ))) at the end. Here only 1 ( opens and 1 ) closes.

=IFS(B2<=12,”Kid”,B2<=19,”Teen”,B2<=21,”Adolescent”,B2>21,”Adult”)

IFS solution

IFS solution

So, this is easier for us to read and faster for Excel to calculate.

In cases where a value does not match and evaluate to true for any of the conditions, the IFS function will output a #N/A error. Therefore, in cases where a value if false is to be specified, it can be done using the IFERROR function:

=IFERROR(value, value_if_error)

 

Highlight overdue items using Conditional Formatting

Whether it’s delivery date, payment date or a followup date, you want Excel to tell you when it’s overdue.

Use this simple method to highlight (color) all cells that are overdue. The best part is, it is dynamic in nature, meaning it will check automatically on a daily basis whether a particular item is overdue ‘today’ or not.

What we need to do

How your data might be a List of customers with their Order Dates & Payment Credit Period (in no. of days).

Data structure

Data structure

You want to calculate this

  1. Payment Date
  2. No. of days remaining

Then, you want to highlight the records which are overdue. Result will look like,

Result

Result

Steps to create this

  • For the payment date, simply put a formula =B2+C2.
    This will add the Order date with Credit no. of days and give you the resultant Payment Date. Copy the formula down throughout.

    =B2+C2

    =B2+C2

    Copy the formula down throughout

    Copy the formula down throughout

  • For the No. of days remaining, =D2-TODAY()
    =TODAY() will internally generate Today’s date (dynamic, will change automatically everyday). D2-Today() will subtract the 2 dates and give you the difference no. of days.

    =D2-TODAY()

    =D2-TODAY()

  • You may get the result as this,

    Incorrect result

    Incorrect result

  • Simply select all the dates and get them to ‘General’ format.

    General format

    General format

  • Now select the ‘No. of days remaining’ column (without the header) and follow the below steps..
  • Home tab -> Conditional formatting -> Highlight cell rules -> Less than -> 0 (zero) -> Red color (or any other)
    Select column without header

    Select column without header

    Specify the condition for formatting

    Specify the condition for formatting

  • All items which are overdue are now highlighted!
Result

Result

Allow only 10 digit numeric mobile number in Excel using Custom Data validation

Mobile numbers are always numeric and of a certain length. You want to ensure data entered is exactly as per that.
In India, mobile numbers are 10 digits. Meaning if any mobile number that’s entered isn’t exactly 10 digits, it’s not a correct data entry.
Similarly, in other countries, mobile number length’s vary. Whether you’re in India, USA, UK Australia, wherever, this method will be extremely useful for you.

What we want

Thus, we want be build a data validation that suffices 2 conditions: –
  1. Number entered must be Numeric
  2. Length of the number must be exactly 10 digits (for Indian mobile number)

Which 3 functions will be used

  1. =ISNUMBER
    this function will ensure that the number entered in only numeric, no text, no dates
  2. =LEN
    this function will ensure 10 digits only
  3. =AND
    to ensure both the conditions are met together in ANDing
    To know how AND function works, click and read this blog post

Steps to create the custom data validation using these 3 functions

  • Create a column for Mobile numbers (say F column)
  • Select all the cells (except the header)

    Select column without header

    Select column without header

  • Under Data tab -> Data Validation -> Allow -> Select ‘Custom’
    Data tab

    Data tab

    Custom

    Custom

  • Enter the formula
    =AND(ISNUMBER($F2),LEN($F2))
  • Your Data Validation is done and it will only allow 10 digit – numeric data entry

Logic behind how it works

Formula

Formula

  • The F2 is simply because we are using the F column for the mobile numbers column, 2 is the row from where the data entry starts.
  • $F is, we are asking Excel to check only the F column for the 2 conditions.
  • =ISNUMBER($F2)
    this checks whether the data entered is numeric or not. Internally, it gives a boolean value as TRUE or FALSE. So if the data entered in numeric, it will be internally TRUE, else FALSE.
  • =LEN($F2)=10
    this checks whether the data entered in 10 digits or not. Internally, this too gives a boolean value as TRUE or FALSE. So if the data entered in 10 digits, it will be internally TRUE, else FALSE.
  • =AND
    ANDing means, both conditions should be TRUE. So only when both the boolean results are TRUE, the data validation will allow the data entered by the user. If any one value is FALSE, the data validation will reject the data entered by the user.

 

See a step-by-step video

Difference between SUM, SUMIF, SUMIFS, DSUM

Whenever you type =SUM in a cell in Excel, you get a lot of functions starting with Sum…

In this blog post, we will be focussing on 4 of them – SUM, SUMIF, SUMIFS, DSUM.

Sum 4 functions

Sum 4 functions

Each of these functions has its own capability for Summing up numbers. Let’s go one by one.

We will be using a sample data set of Umbrella sales (1000 rows of data), to understand each of these functions in detail. Thus the data range is, A1 : J1001.

Umbrella Sales Data

Umbrella Sales Data

SUM

Sum function will get you a sum from multiple individual cells, a single range, or even multiple ranges in 1 shot.

Sum of all Quantity (I column)

The formula will be =SUM(I2:I1001)

Sum of all Totals (J column)

The formula will be =SUM(J2:J1001)

sum-function

SUMIF

Sumif function gets you a sum based on 1 condition (only).

Sum of all Quantities of only Blue umbrellas

The formula will be =SUMIF(G2:G1001,”blue”,I2:I1001)

=SUMIF (Range, Criteria, Sum_Range)

  • Criteria – Blue (the actual condition).
    • The criteria is not case sensitive. The “blue” can be written in upper case or small caps, doesn’t matter.
    • Since the criteria is text, it needs the ” “. If the criteria was a number, it wouldn’t require the ” “. Date will require ” “.
  • Range – The column where Blue, the criteria, resides. Thus Color (G) column in this case.
  • Sum_Range – Sum of what? Quantity, Total? since we need the Sum of Quantities, we choose Qty (I) column.
  • The order can’t be messed up – First the Range, then Criteria, then the Sum_Range

sumif-function

SUMIFS

Sumifs (notice the ‘s’) gets you a sum based on multiple conditions. But it also works with a single condition as well!

Sum of all Quantities of Blue, Small size Umbrellas

The formula will be =SUMIFS(I2:I1001,G2:G1001,”blue”,F2:F1001,”small”)

=SUMIFS (Sum_Range, Range 1, Criteria 1, Range 2, Criteria 2, …, Range n, Criteria n)

  • Observe carefully that in the case of SUMIFS, the Sum_Range comes first!
  • Following after, Range & Criteria of each condition, similar to SUMIF
  • Note that all conditions in SUMIFS will always get ANDed and never ORed
  • Thus, no 2 conditions should fall in the same column. If they do, your formula will be a little different, explained below in the next example.
  • The order can’t be messed up – Sum_Range first!
  • As stated above too, SUMIFS is capable of handling a single condition too. I thus never use SUMIF, i only use SUMIFS.

sumifs-and-ing-criteria

Sum of all Quantities of Blue + Yellow Umbrellas

The formula will be =SUMIFS(I2:I1001,G2:G1001,”blue”)+SUMIFS(I2:I1001,G2:G1001,”yellow”)

  • Observe that in this case, both the conditions are falling into the same Color column.
  • Thus, both conditions need to be ORed.
  • This can easily be done by using
    • SUMIF + SUMIF      OR
    • SUMIFS + SUMIFS

sumifs-or-ing-criteria

DSUM

Now, what if you needed to have a summation of quantities of Blue, Black, Yellow and Pink colors!

What is there are 40 different colors of umbrellas that you are selling, and you want a total of 20 of those colors!

Instead of writing =SUMIF + SUMIF + SUMIF + SUMIF + ….. 20 times, simply use DSUM !!!

=DUM let’s you create your condition outside the formula, making it very dynamic in nature, and easy to create.

Sum of quantities of Blue + Yellow + Black umbrellas

Creating the Condition (Criteria) outside

dsum-criteria-outside-1

DSUM criteria outside

Write the column Header first (exactly same as in the data), in this case ‘Color’.

Below that, write each of the colors, one below the other.

When you write the criteria’s one below the other, it means OR-ing.

The formula will be =DSUM(A1:J1001,I1,M21:M24)

=DSUM ( Database, Field, Criteria )

  • Database – Select the entire data set
  • Field – Select only the header of the column, of whose SUM you want, in this case the Quantity column, hence I1.
  • Criteria – Select the grid that have you created outside.

dsum-for-or-ing

Sum of quantities of (Blue, Small) + (Black, Large) umbrellas

Creating the Condition (Criteria) outside

dsum-criteria-outside-2

When you write the criteria’s next to each other, it means AND-ing, and below means OR-ing.

The formula will be =DSUM(A1:J1001,I1,M21:M24)

dsum-for-and-ing

 

Read more…

CountIF and SumIF functions in Excel for awesome calculations

How to use AND in IF formula in Excel

5 Advantages of Google Sheets over Excel that you didn’t know

When Google came up Google Docs and Google Sheets, it started a completely new way people collaborate online to create and work on documents and sheets in groups. The extremely user-friendly interface for collaborating completely changed the way people work today.

A lot of StartUp businesses today manage all their data using Google Sheets. Mainly it helps them to work in teams together on the same Sheet, at the same time. Each one can also visually very easily see what the others are doing right now in the sheet.

Sharing is a very well known feature in Google Sheets, so I will not elaborate further on that. But what you may not know is that you can actually Share in such a way, that a person with whom you share your sheet with, can edit only a part of that Sheet, and not everything!

1) Sharing with limited rights to edit for certain people

You can select your ranges and restrict editing rights to certain people.

First, share the sheet with all who you want to allow to edit.

  • Click on Share, add the email id’s.
  • Then, restrict editing rights.
  • Select the Range that don’t want to allow to edit.
  • Under Data menu, click on Protected Sheets & Ranges.
  • Click on the + sign
  • Ensure the correct Range is selected
  • Click on Set permissions
  • Select Custom and enter the email id’s who you want to restrict
Sharing with limited rights

Sharing with limited rights

2) Pivot Tables refresh automatically

In Google Sheets, there is no need to Refresh pivot tables when the underlying data changes. It updates automatically, instantly. This is a big relief over Excel.

3) =Googlefinance function

Google really understands the power of the internet and connected Google Finance to Google Sheets directly!

For investors in the stock market and other areas, data from Google Finance can be picked up directly into a Google Sheet using the =Googlefinance function.

Enter a scrip code in cell A1, say 500325, and use the below formulae: –

  • =Googlefinance(A1)
    • The current market price of Reliance Industries Ltd. will show, and will update automatically
  • =Googlefinance(A1,”name”)
    • The name of the scrip will appear, in this case, Reliance Industries Limited
  • =Googlefinance(A1,”high52”)
    • 52 week high price will show
  • And so on….

4) Keyboard shortcuts list

You can get a list of most commonly usable keyboard shortcuts directly from the Help menu, or use the keyboard shortcut Ctrl + / to get the list instantly!

This comes in very handy, and you don’t have to always search for shortcuts on the internet.

5) Revision History

This is again a mind-blowing feature when collaborating with others.

Under the File menu, click on See Revision History.

A list of all changes will appear, and you can anytime take your document back-in-time to an earlier version.

 

Excel on the other hand, has many more unbelievable features, which many of us don’t even use! Let’s take What-if-analysis for an example. The kind of analysis you can do using this tool is incredible! Google Sheets doesn’t have this yet.

Having said that, Google Sheets is catching up really well and will be instrumental in always improving the way people collaborate to create documents, sheets, presentations, etc.

Four things you can do with Enter key in Excel

You will save a lot of time on a daily basis by using the Enter key in these 4 ways.

You may think, what is more to learn in using the Enter key in Excel? Read further to know what are the 4 ways and what they can do for you.

Enter (Normal)

When you type anything and press Enter, or even without typing you just press Enter,
– It takes you to the next cell in the next row (1 step down).

Enter (Normal)

Enter (Normal)

Shift + Enter

Normally, when you want to type something, and then go 1 cell up, you end up doing this,

Type – Enter – Up arrow – Up arrow. Rather use Shift +Enter

Thus, when you type anything and press Shift + Enter, or even without typing you just press Shift + Enter,
– It takes you to the next cell in the previous row (1 step Up).

Shift + Enter

Shift + Enter

Alt + Enter

You maybe using the feature Wrap Text in Excel.

– But using Alt + Enter, you can write in the next line, in the same cell.

This will help in a way that you can decide when you want to next line to start (in the same cell). Wrap doesn’t allow that, it wraps the text automatically based on the width of the column.

Thus, when you type something and press Alt + Enter, you will go to the next line in the same cell. Then once you are done typing, press normal Enter. The entire Row’s height will increase automatically.

Alt + Enter

Alt + Enter

Ctrl + Enter

Let’s say you want to type something and then make it Bold. Normally we do this,

Type – Enter – Up arrow – Ctrl + B (bold). Rather use Ctrl + Enter and then directly Ctrl + B.

Thus, Ctrl + Enter allows you to stay in the same cell after typing something.

Ctrl + Enter

Ctrl + Enter

Do try these out once. They are amazing and very handy to use everyday.

If you have found this useful, leave a comment below!

Keep Excel-ing!

10 Excel Shortcuts that will change the way you work

When you work with Excel, you must avoid using the mouse as much as you can. Using the keyboard only will help enhance your speed a lot, and you will start saving a ton of time!

I understand that remembering Excel shortcuts is a pain, so I have listed below 10 shortcuts which can be remembered easily. I assure you, these are the ones that you will definitely want to use everyday!

Start using these 10 Excel shortcuts first and then once you have been using these on a daily basis, learn more.

I have used a sample data as below to demonstrate all the shortcuts

  1. Select Entire Column
    Ctrl + Spacebar
    Keep your cursor anywhere in the column that you want to select, and press Ctrl + Spacebar
    Excel Shortcut to Select full column 
  2. Insert Column / Row
    Ctrl + +
    Select the column (with above shortcut) before which you want to insert a new column and press Ctrl + +
    Excel shortcut to Insert new column
  3. Save As
    F12
    Excel shortcut to Save As
  4. Select column with data
    Ctrl + Shift + Down
    Keep your cursor at the beginning of your column, and press Ctrl + Shift + Down. It will select the data to a point where the next cell is blank. So if there is a blank cell in between the data, it will select upto a point just before that.
    Excel shortcut to Select column with data
  5. Undo & Redo
    Ctrl + Z / Y
    Undo if ofcourse to undo something you did by mistake.
    Redo and to redo something you just Undo’d!

    Excel shortcut to Undo

    Deleted the data. Then pressed Undo to get it back

    Excel shortcut to Redo

    Deleted the data again by pressing Redo

  6. Auto Filter On/off
    Ctrl + Shift + L
    Excel shortcut to turn on Auto-filter
  7. Sort in Ascending Order
    Alt – A – S – A (Excel 2013 and higher)
    Alt – A – A (Excel 2007 & 2010)
    Keep your cursor anywhere in the column by which you want to sort the data, and hit the shortcut key.
    Excel shortcut to Sort in Ascending order
  8. Apply All Borders
    Alt – H – B – A
    Select the required data that you want to apply borders to, and press Alt – H – B – A
    Excel shortcut to Apply All Borders
  9. Auto-fit Column Width
    Alt – O – C – A
    Select the column and then press Alt – O – C – A.

    Excel shortcut to Autofit column width

    Data is getting cut out visually

    Excel shortcut to Autofit column width

    After pressing the shortcut

  10. Center Align
    Alt – H – A – C
    Select the cell(s) that you want to center align and press Alt – H – A – C
    Excel shortcut to Center Align

    AND A BONUS ONE!!!

  11. Go to Previous Sheet / Next Sheet
    Ctrl + Page Up / Page Down
    Excel shortcut to go to Next sheet / Previous Sheet