Fixed Deposits Tracker – Across Banks Across Accounts

November 11, 2015 By rushabhs in Conditional Formatting, Formulas, Tips No Comments Tags: Fixed deposit

You have 3 bank accounts and 3 members in your family.
You have fixed deposits across the 3 banks for all 3 of your family’s members.
How do you track all the fixed deposits in 1 single place? This 3 video series will show you how simple it is.

Part 1

Part 2

Part 3

SUBSCRIBE!

Click now to Subscribe to the YouTube Channel.

Pie-Chart, Explode 1 slice !

October 19, 2011 By rushabhs in Charts 2 Comments

Let’s look at tip for pie-charts today! A visually useful one.

So you have some data – city-wise sales, from which you have made a simple 2-D pie-chart.
I have added Data Labels and removed the Legends for a better visual experience.

Visually, this chart is simple to read as it only has few data.
What if sales like this of say 30 cities were put into the pie?! It would look like this..!

Now from only this chart, if someone asks you, which slice in the pie is the smallest?! Almost impossible to say immediately right.. Better, just highlight the slice in a way that it comes out.. HOW?!

Let’s take the earlier example to explain this.
We know that Hyderabad is the smallest slice.
Then follow these steps: –

So 1st click on the entire chart – all slices will be selected.
Then click 1 more time on the Hyderabad slice – only that 1 slice will be selected.
Right-click and go to Format Data Point.
Under the Point Explosion, make it 40%.
Click Close.

What you will get is this.

Now you clearly know that Hyderabad needs attention!

Enjoy Excel-ing with Excel-lent charts 🙂

Goal Seek (Seeking your Goal)

June 28, 2011 By rushabhs in What-If-Analysis No Comments

Ever came across a situation where you are given/giving an invoice on which you have calculated say 10.3% tax, and then want to have a discount of some amount?
Let us see this with an example.

Invoice Amount = Rs. 1000
Tax = 10.3%
Total = Rs. 1103

Now you want a discount of Rs. 53, so ideally the Grand Total will become Rs. 1050.

Discount

But the question is, how much should now be the invoice amount, so as to amount to exactly Rs. 1050 as the Grand Total after adding 10.3% tax.
Little time consuming isn’t it?

Invoice Amount?

Let’s save our time by using a feature in Excel called Goal Seek.

Suppose say the Invoice amount is in cell B1,
the Tax % is in cell B2,
the Grand Total formula is in cell B3.

Go to the Data tab on the ribbon, Under the Data Tools section, click on What-If Analysis, and then on Goal Seek.
In Set Cell, select cell B3.
In To value, type in 1050.
In By changing cell, select cell B1.
Press OK.

Goal Seek

A dialogue box will open with a summary of what calculations Excel has done, press OK again.

Final Invoice Amount

You will see Rs. 951.9492 in cell B1, which is your required amount to be then put as Invoice amount.

See these 2 videos to understand it even better.

Enjoy Seeking your Goals 🙂

Select cells with similar formatting

March 22, 2011 By rushabhs in Cell & Data Formatting 6 Comments

This is very simple in MS Word !
But how do you do this in MS Excel?

A little longer way but simple once you get it.
Suppose you have data like this: –

Notice that every alternate cell has text in Bold.
So now you want to select all cells in the sheet that have the formatting as of that is there in cell A1 (Bold in this case).

1. Go to Home tab on the ribbon – Under Editing section click on Find & Select – Choose Find.
2. Click Options.
3. Click on Format, select Choose Format from Cell.
4. Click on A1.
5. Click on Find All.
6. Click on 1 of those results and then Ctrl + A (Select All).
7. Close the Find dialog box.