Change the marker symbol in a chart to your own favorite shape

Change the marker symbol in a chart to your own favorite shape:

All you need is to draw any shape in a worksheet from Insert>>Shape . Copy the shape (Ctrl+C), Select the bars and paste into it (Ctrl+P). And here, you get your favorite shape chart, show it in a presentation and make your boss or teacher happy.

Draw any shape in a worksheet from Insert>>Shape

Copy the shape (Ctrl+C), Select the bars 

and paste into it (Ctrl+P)

Lookup an Item value from a table.

Two way lookup :-
Index function returns a value from within a table or range and Match function finds position for an item.

If you need to find an item value from a table where you have multiple rows and columns, then you need first to find the position of a row and a column for a particular item.

To find the position of row - use match function..
To find the position column - again use match function.
To returns value from relative position of row and column - use index functiin.

As in our example - Finding position of Year 2003. Match function is used. Match 2003 in Range E4:E9 (a single column range) where type is 0 means finding exact match. Result would be 4 which means position of row is 4.

Next, Finding position of Quarter Q2. Match function is used. Match Q2 in Range E4:I4 (a single row range) where type is 0 means finding exact match. Result would be 3 which means position of column is 3.

Next, finding an item value of interestion of row and column . Use Index function where array is E4:I9 which is whole table including headings. Index(table_array, row position, column position). Result will be $350

Thank you!

Tip : Use Wildcard to Sum Cells

Use Wildcard to Sum Cells : 

Here is quick tip:

If you have multiple sheets of different stores sales data and the challenge is to make summary report by summing up all the Delhi stores sales or the Mumbai store sales etc.. Then here is a tip:

Use wildcard "*" in sum formula.

=sum('de*'!G4)

It will sum all state that is started with "De" having sales value in G4 cell of each sheet.

CONVERT function.

CONVERT function:

CONVERT function converts a number from one measurement system to another such as inches to centimeters , days into hours etc.

Syntax:
=CONVERT(number, from_unit, to_unit)

Example:
=CONVERT(A2,"day","hr") : Days into Hours
=CONVERT(A2,"day","mn") : Days into Minutes

Below are the units abbreviations:

Thanks

Convert Days to Hour or Days to Minute :

Convert Days to Hour or Days to Minute :

Excel consist a function called CONVERT function. By using CONVERT function you could convert the days into hours or days into minute or hours into minutes or minutes into seconds or hours into seconds or many more.

=CONVERT(A2,"day","hr") : Days into Hours
=CONVERT(A2,"day","mn") : Days into Minutes

Thanks

Time Difference in Decimal

Time Difference in Decimal

Lots of time, we need to calculate the difference of time where we mistakes a lot.

You can simply subtract the difference, which is correct but not very useful or meaningful.

Here, I covered the time difference specifically to show the minutes part.

1) you can prefer example 30 minutes, which is 00:30. When you sum up two 30 minutes , it will be 60 minutes which is obvious an hour, or

2) you can prefer half an hour in 0.50 format. So, when you sum two half an hour it will show complete one hour (i.e 1)

I personally prefer 2nd one, which is easier for me for further calculations.

Option first formula: TEXT(C2-B2,"hh:mm:ss")
Option second formula: MOD(C2-B2,1)*24

Thank you!!

Sum Without Error

Sum Without Error:

If the Column contains error then summing up with formula
=sum(range), will show an error too.

First, get all the error as blank and then sum it.

For that use array function, 

{=sum(iferror(range,""))}

For array function write in formula bar

=sum(iferror(c4:c17,"")) and press ctrl+shift+enter

Similarly you can do this with average, count, max or min function

Thanks