Read Your Cells

Here I'm going to introduce you fantastic option in Excel. I would like to call this "Read Your Cells" Option. You can let excel to tell what you are typing or typed..............!. If you are wish to read my previous posts too you can download example excel sheet from here. Otherwise you don't need to download this. Let's learn how we can do this.

Open any excel file.

Click on the small arrow in the quick access toolbar and Select "More Commands".

Select "All Commands" from "choose commands from" drop down list.

Go to commands starting letter 'S'

Select and add "Speak Cells", "Speak Cells - Stop Speaking Cells" and "Speak Cells on Enter" commands.

Now you can see all commands are available on "Quick Access Toolbar"

Click on them to activate or deactivate.

Enjoy with reading excel.

Copy Macro to a Workbook

We are recording a simple macro or developing a macro to automate our tasks in excel. Sometimes we need to copy them to another workbook. Let's see how we can perform this action.

Copy The Macro. (If you copied the macro from internet or other source you can skip this section)

Go to Developer tab in the workbook which you have macro to be copied. (If you can't see developer tab, check this post 'Display Developer Tab'.) There You can see "Code" subgroup and "Visual Basic" as the first option. Click on it to open the VBA editor.

Right side of this window you can see the list of excel workbooks open at the time and list of sheets, List of forms in a workbook & list of modules in a  workbook. Find the module which is going to copy and double click on it. Now you can see the code. Select and copy all lines of the code. (Hope this module is not linked with any other module in the workbook. If you have any doubt about it, please get support from experts. Otherwise copied macro will not work properly.)

Paste the Macro

Go to Developer tab in the workbook which you have macro to be copied. (If you can't see developer tab, check this post 'Display Developer Tab') There You can see "Code" subgroup and "Visual Basic" as the first option. Click on it to open the VBA editor.

Click on insert button on the VBA editor and select 'Module'. Paste the code copied by you. Save the workbook with the macro as macro enabled workbook. (If you don't know how to check this post 'Save Your Macro'.)

More Date Formats

I think most of you enter the date into the excel by entering dates before the month. As an example let's take you want to enter 12th of November into the excel. We can enter it as 12/11 or 11/12. What is the correct method? See below picture cell b2 and d2 shows that results respectively. 

You can see cell d2 shows the correct date.
Now right click on cell d2 and select format cells. Select Custom on popup box.

There is a combo box named type. Enter bellow mentioned formats one at once into that box.

1. d
2. dd
3. ddd
4. m
5. mm
6. mmm
7. mmmm
8. yy
9. yyy
10. dd,ddd
11. yy-mmm-dd,ddd

See changes on the date and practice. 

Save Your Macro

I think all of you were recorded your first macro. Follow below steps to save your macro. To save your file with macro save as option on file. Then select "Excel Macro-Enabled workbook". Click save.

How to Record Macro to Automate Excel

Macro are the simple programs using to automate tasks/operations on the excel. They are developed using the programing language "Visual Basic for Applications"(VBA). This  based on Visual Basic. We can record macro on excel and use them later. You have to activate Developer Tab before you record a macro. If you Don't know how, Check my post about

"How to Enable Developer Tab"

Below table will be used to demonstrate the example for record macro.

Brand	Item	List Price	Discount	Selling Price
Nike	Shoe	$111.99		$111.99
Nike	Socks	$21.99
Adidas	Shoe	$58.74
Adidas	Socks	$16.99
Reebok	Shoe	$26.59

Copy above table and paste to cell A1 on blank sheet.

Start Record Macro

1. Select cell E2.
2. Then click on "Record Macro" in Developer Tab.
   
3. After that a new pop up window appears.
4. Now you can change the name of macro and assign a shortcut key to launch this macro on later. For this example we set the macro name as "CopyMacro" and "Ctrl + Shift + A" as the shortcut key.
5. Then click "OK". Now our macro is recording.

Record the Macro

Now we can simulate the actions to be recorded as the macro. Follow below steps to record a simple copy macro.

1. Copy the cell E2 and paste it onto cell E3.
2. Then Click on "Stop Recording" in developer tab to stop recording our macro.
3. Done.

You have recorded the first ever macro recorded by you. Let's check how it's working.

Check the Recorded Macro

1. Delete the cell E3
2. Then enter the "Ctrl + Shift + A" to activate your macro.

Now you can see excel automatically copy cell E2 and paste into cell E3.

Relative References

If you properly inspect the Developer tab, you can see there is an option calling "Use Relative References". What is the use of that? Follow bellow steps and see the difference.

1. Select Use Relative References Option
2. Then Select cell E2.
3. Now click on "Record Macro" in Developer Tab.
4. Then a new pop up window appears.
5. Now you can change the name of macro and assign a shortcut key to launch this macro on later. For this example we set the macro name as "CopyMacroNew" and "Ctrl + Shift + B" as the shortcut key.
6. Then click "OK".
7. After that Copy the cell E2 and paste it onto cell E3.
8. Then Click on "Stop Recording".
9. Then enter the "Ctrl + Shift + B"

See the difference. This time excel copies whichever cell you are selected and paste it into the relative position.

How to Enable Developer Tab in Excel

Excel macro is the way to automate / develop advanced tools in excel. To have this feature you have to enable developer tab first. It is is showing that VBA controls for the excel sheet. You can add, delete  or modify macros through Developer tab. Further the method/process to enable developer tab is based on the excel version you are using.  Therefore please make sure to follow correct method according to your excel version.

Enable Developer Tab in Excel 97-2003

There is no Ribbon on the excel 97-2003 version. That means no tabs in that version. Therefore we can't enable/disable a tab named developer in excel 97-2003. Instead we can activate the Vba editor window using below method.

1. Go to Tools menu
2. Then select Macro.
3. You can find the Visual Basic Editor Command There. Click on it to open VBA Editor.
4. Alternatively You can pre Alt + F11

Enable Developer Tab in Excel 2007

1. Click the "Microsoft Button" on left side upper corner.
2. Then select the "Excel Options"
3. Then click "Popular"
4. After that select the "Show Developer Tab in the Ribbon".

Enable Developer Tab in Excel 2010 & Later

1. Click the "File" Command
2. Then select "Options" button.
3. Then Choose "Customize Ribbon" from the new dialog box.
4. After that select "Developer Tab" check box from the list of "Main Tabs"
5. Finally Click "OK".

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.

Use of Solver Package

Solver package is another valuable package that already included in Microsoft Excel. You can see this package under the Data tab. If you can't see the solver package activate it following below steps.

1. Click on File (Microsoft Windows mark for Excel 2007).
2. Click Options
3. Select Add Ins
4. Select Excel add-ins from the Manage and press go
5. Select the tick mark on Solver add in
6. Press Enter
7. Now You can see Solver has added in under Data Tab

Below data expressing that the merchant who is selling fresh fruit through eBay.com. Please download the data file using this link or copy below data to your excel sheet.

	Buying Price	Quantity	Selling Price	Selling Quantity	Profit
Mango	3	10	5	10	20
Banana	2	8	3	8	8
Pineapple	5	6	7	6	12
				24	40

Here we are going to find out Selling Prices that give maximum profit to the seller. However, maximum no of items he can sell is limited to 20. To do that, follow below steps.

Select the total profit cell and click on solver in the Data Tab.

Click the Max in To group

Select E2:E3 as changing variables.

Click on 'Add' to add constraints. Add below constrains

• Selling quantity of any item cannot exceed the quantity he brought
• Profit of any item can't be zero or minus.
• Item quantity total cannot exceed 20

Press enter.

You can see the result in the Popup window.

You can make permanent them or discard them

Goal Seek

Goal seek is another function in excel very valuable for you. Use this link to download file or copy below data to your excel sheet. 

Brand	Item	List Price	Discount	Selling Price
Nike	Shoe	$111.99	10.71%	$100.00
Nike	Socks	$21.99		$0.00
Adidas	Shoe	$58.74		$0.00
Adidas	Socks	$16.99		$0.00
Reebok	Shoe	$26.59		$0.00

Here I listed details of some sports shoes and socks selling in a retail website like eBay.com (or Amazon.com). The owner of website would like to give discounts for above items for a limited time. He has an idea about selling price but not about the discount. He used goal seek function in excel which I describe below.

Select the selling price column of any item.

Go to Data tools group in data tab and select goal seek from what if analysis.

There are 3 parameters to set.

1. Select Cell - The cell you want to change. Here us wants to change the selling price cell and already it is selected.
2. To Value - Here we enter the value we want to reach the selected cell. This example site owner takes it as $100.00 for a Pair of Nike shoes.
3. By Changing Cell - Here you have to select the cell you want to change. It is corresponding discount cell for this example.

Click Ok. Excel automatically calculates the values for discount and give the solution.

Click Ok to keep the solution and Click Cancel to reset the excel sheet.

Please make sure there is a link (equation) between selected cell and Changing cell.