Error Checking

I would like to discuss how can we trace error in excel formulas. There are several methods to do so. Let's consider some of them. Download the data file from here.

Excel sheets may have lots of equations to proper functioning. If there is a mistake in one of those equations it's very difficult to locate the correct cell manually.  We can use "Trace Precedents" and "Trace Dependents" options to locate correct cell.

Go to "Formula Auditing" group in "Formulas" tab.

You can find  "Trace Precedents" and "Trace Dependents" options left side of the group.

Select the cell which contains the error formula.

Click on "Trace Precedents".

See the arrow lines connecting all relevant cells.

Likewise see the "Trace dependents" option.

We can use "Remove Arrows" option to clear arrows.

If there is an error in formula we can use "Error Checking" function.

Go to "Formula Auditing" group in "Formulas" tab.

Select the cell which containing the error value.

Then click  the small arrow on "Error Checking"

Select "Trace Error"

See red arrow from the cell which is caused to error.

Add Trend Line to Excel Chart

Trend lines are the easiest way to find out trend of a data set. Let's take you are selling shoes using amazon.com and ebay.com. This year sales details listed below. You can download data file using this link.

Web Store	January	February	March	April	May	June	July
eBay.com	12	13	20	26	34	32	36
Amazon.com	15	21	24	25	23	18	12

Before adding trend line we need a chart.

Draw a line chart using the above data.

Now we can add trend line for above chart.

write click on any series.

Select 'Add Trend Line' from the pop up menu.

Then new pop up window appear  We can choose behaviors and properties of trend line from this menu.

In this example our chart is amazon.com line is looks like a parabola. So that we can select "Polynomial" as type and set order as two. If you wish to see equation of the trend line tick "Display Equation on Chart".

Now you can close the window to see your trend line.

Add trend lines for both series and see how your selling pattern changing.

We can consider ebay.com line as linear.

Dynamic Drop Down Lists

One of my previous post show that how to create Drop Down List in excel. Here I'm going to discuss how to create Dynamic Drop Down List. Copy below data or download data file using this link.

Phone	Samsung	Sony	Nokia	HTC	BlackBerry
Samsung	Samsung Galaxy S4	Xperia Z Ultra	Lumia 625	HTC one	BlackBerry Q10
Sony	Samsung Galaxy S3	Xperia L	Lumia 925	HTC Desire X	BlackBerry Z10
Nokia	Samsung Note II	Xperia M	Lumia 1020	HTC One X	BlackBerry Bold 9930
HTC	Samsung Galaxy Discover	Xperia Z SP	Asha 501	HTC One V	BlackBerry Curve 9310
BlackBerry	Samsung Galaxy Ring	Xperia Z go	Asha 210	HTC Butterfly	BlackBerry Curve 9370

Select the range A2:A6 & name it as Manufacture. (See the marked area)

Name the ranges B2:B6, C2:C6, D2:D6, E2:E6 & F2:F6 as Samsung, Sony, Nokia, HTC & BlackBerry respectively.

Select any cell in the sheet & Enter "Manufacture" to that cell & "Phone" next cell.

Select the cell below Manufacture (for this example it's E14).

Add Drop Down List the range "Manufacture"

Then select the cell bellow Phone (or this example it's F14).

Click on Data Validation on Data Tools group in Data Tab.

Select "List' from Allow drop down list.

Enter "=Indirect (E14)" as Source.

Press Enter. If you receive below error message, Press "Yes". Otherwise check your data for any error. This error means you leave manufacture blank. Once you select any manufacture from the list it will not appear again.

Now you have Dynamic Drop Down List. Select any manufacture from drop down list. See the phone drop down list changes as manufacture selected by you.

Two Axis Chart

Most of the time we are using the option excel charts to interpret our data in an organized manner. However we are not using or not known to use two axis charts to do so. Following steps describing this post you can easily create two axis chart in Microsoft Excel. The data in this post are also extracted from a fresh fruit merchant who described in a previous post. Please download the data file using this link from google drive.

Select the range A1:C4.

Click on charts group in inserts tab & select line graph format from it.

This section gives you the basic chart like below.

Select buying price series & right click on it.

Select "Format Data Series" from pop up menu.

Now you can see the pop up window like this.

Select "Secondary Axis" option from that window & click close.

Now you have the two axis chart.

Select "Layout" tab from Chart tools & make any changes you like.

Then final chart will be below.