Excel 2007 Funde

Excel Terminologies

ACTIVE CELL

 Active cell is the cell that you have selected at any point of time. The picture shows that a student has selected the cell A1.

Active Cell

activecell

Fig 1: Active Cell

To check which cell is presently active, look for the dark black border around a cell, as one can see in the figure above. When a cell is active you can perform 3 main tasks on it:

  1. Enter data: When you double click on a cell you can enter the values in that. Various types of data that can be entered in a cell are: Numbers; Characters; dates. However, If the cell is under validation, then you can enter only limited value. Validation is nothing but putting a condition on a data that can be entered. For instance, if the data cell is validated to contain only numerical values, it will not take characters and if you try to try one, it will show error.
  2. Format Cell: Various formatting options are: Creating borders, formatting the value inside the cell, change alignment of the value, color the cell background or the value, and protect the cell. To see what the excel has to offer in formatting, select a cell and right click, you will see an option “Format Cells” click that and a window will pop up with options.

formatcells

Fig 2: Format Cells

  1. Edit Cell: When you double click on a cell, you can edit the value inside, alternatively, F2 key can also be used to get into editing mode. When you are doing any task in the active cell, the value that is being entered or modified can and the address of the active cell can be seen in the formula bar , which is shown just above the worksheet.

fomulabar

Fig 3: 

 

Now, what if you select multiple cells? Will all those cells be taken as active cells? No, the active cell will always be one and it will be shown in white color when you select multiple cells together while the entire range of cells will be highlighted by a black border.

multiplecellselection

 

Fig 4: Multiple cell selection

 

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com

Data Validation for correct Data Entry

Data Validation for correct Data Entry

Data Analysis is a process which is used in every business, whether the data talks about customers or employees or even customers.Before we analyse any figures, first step is to enter the data into database which can SQL, access or excel. Excel is the most used software for all purposes of data including entry, calculation, analysis and presentation. Data entry in excel is very easy as compared to any other database software as we just need to keep typing continuously. However, errors in data entry is very common in excel due to the same simplicity.

Excel provides a brilliant tool to avoid the most common and silly mistakes in entry. For example, if some one has asked you to input your name, you mistakenly type your age or if you are supposed to enter your mobile number, you entered email address. If you are thinking these are too silly mistakes and very rare then think of a field where you have to enter a telephone number, which is supposed to be of 10 digits. What if in hurry, you type 11 numbers? This tool which is called data validation is used to avoid such errors.

You need to put data validation on the cells in which the data is to be entered. Let me explain this with an example.

data validation sample table

Figure 1: Students Data Table

The data table contains the information about students where the data entry operator is asked to enter their name, mobile number, email ID and score on a particular subject.

Now here we are going to do  things:

  1. Ensure that in name and Email ID columns only text is entered
  2. In Mobile and Score columns only numbers are entered
  3. Column score should not contain number greater than 100
  4. Mobile number field should have maximum 10 characters entry

Let’s do this one by one:

  1. Name column Data validation

Here we have to do 1 thing : data has to be text

Select the cells you want to apply validation on: here we have first row, so we select cells from A2 to A11

Go to Data -> choose Data Validation in Data Tools section

data validation in excel

Figure 2: Data validation

Data validation form, default value is Any Value that you will see in Allow section but it will have options to validate data on various other criteria : Whole number, decimal number, list, date, time, text length, custom

Here we select text length, and two more parameters will be asked : data that will ask for different validation criteria: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.

Here we select greater than and type minimum value as 1, as name would obviously have more than one characters.

With these setting the cells will not take any other value than text. Want to test? Try this

Type 1 in cell A10, and press enter

eerror due to data validation

Figure 3: Name Column error

When you enter, an error will appear saying the data you entered is not valid. Now try entering abhi, and you will see that the cell will take the value without an issue.

  1. Mobile Column Data Validation

Select the mobile data column, go to validation again. This time instead of text length, select whole number and enter the values as shown in the figure below:

data validation on whole numbers

Figure 4: Whole number data validation

In this we have allowed whole number that is less than or equal to 9999999999, so our purpose is solved. To test the validation, try typing a wrong value. Type a and enter and you will see the error message of invalid entry. Now type 11 digit number and enter, and the same error message will appear.

  1. Email ID data validation

Select the Email ID data column, and apply the same process as you applied in the Name column and excel will ensure that only text is entered in this column

  1. Score

Use the same process as you used in the mobile column but type the maximum value as 100. So we will have a validation here which allows one to only enter number and that too less than or equal to 100.

 

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com

Related articles

What are scenarios?

Scenarios are very commonly used in business forecasting. To set sales targets, managers of various companies gather the present sales data and forecast the future sales on the basis of the trends in the market but not always the trend remains small. There are many reasons why sales can be lesser than previous year or even it may go beyond expectations.

For example, umbrella sales shoots up during rains and in the same way cold drink sales reduce in the winter. Season can be one factor that affects sales but there can be many others such as inflation, changes in world economy, raw material price rise, sudden disaster, natural calamity, and these can affect any organization. Hence, managers don’t only make one estimate but makes three on the basis of different conditions. A pessimistic sales value may forecast an increase of 10%, while optimistic says it will increase to 30% and the neutral will say it would be 20%. The table and the headings that will be used in all three forecasts will remain same but the values will change. The same process can be used in forecasting or production, expenditure and so on.

Let’s take an example of a production scenario, three different tables below show three different scenarios.

sample table showing three scenarios

Figure 1.  Three tables

This table looks simple and with only few data rows but real production data can have values like 1000-2000 rows. So in that case we need to make 3 huge tables. However excel has a tool which enables us t save the data of these three tables in just one table and it is called scenario. Obviously it saves time and space and makes your table easier to work with.

How scenarios work?

Let’s take the above example and instead of making all 3 tables, lets use only the first one which shoes pessimistic scenario.

Now select Data tab and go to what – if analysis and in that you will see scenario manager, Click that.

open scenario manager

Figure 2: Scenarios

When you click scenario manager, another window will pop out, asking you to add scenarios

scenario manager

Figure 3: Scenario manager

Add new scenario, name it as Pessimistic and when the window will ask for values, do not make any changes but only save original table.

Now follow the same process and add another scenario by name Neutral and choose the cells you want to make changes. Any cells that are dependent on the values in the cells you chose will automatically change as per new values so no need to selects cells with formulas but only select those having constant values. Here you can even select non-consequent cells like A1 and then from C3 to D7.

adding scenarios

Figure 4: Adding scenario

You can check Prevent cells to protect your sheet from any further changes by any other person who uses your sheet.

adding values to scenario

Figure 5: Adding values to scenario

The scenario manager will now show the values already there in the table, Enter the new values you want to have in the table for Neutral scenario and click ok

You will see a new table with new values but original values will remain intact in the original table scenario which you named as pessimistic.

Use the same process to save third scenario as Optimistic.

Now your data is ready and whenever you open scenario manager you can choose the scenario you want to see and table will remain intact but will show 3 different set of values for 3 different scenarios.

 

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com

Excel for Business

This section contains information, explanations and formulas on Excel 2007 that can be very useful in any business. For your understanding, I have explained each concept keeping in mind that the reader is a beginner in using Excel.

Topics that are covered are:

Mean, Median and Mode

Concept of Correlation

What are scenarios?

Goal Seek in Excel

Keep looking for more topics in future…

Understanding Excel VBA Object Model

VBA Object Model consists of various objects under other objects connected to each other. Application Object that is Excel itself is top most in the hierarchy and then it consists of more objects under it. Every object has properties and methods, where properties describe the object and methods are used to perform a certain task.

Application

– Workbook

-Worksheet

-Range

Range Object’s parent is worksheet and the parent of the worksheet object is workbook and parent of Workbook object is Application.

So here if in the application , we want to refer to the range object then, the macro will be coded as:

Application.Workbooks.Worksheets(1).Range(“A1”)

Application object:

Application object is the default object, even if you do not specify , excel assumes that Application object is there.

So here even if we use

Worksheets(1).Range(“A1”), the excel will still recognize the code.

Application object is the parent object which has many parts like worksheets, Cells and Sheets. The object is used to access various settings and tools that are available in Excel.

 

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com

Insert , Delete & Format Cells

IN the home tab, you will find a groups for modification of cells that is grouped under cells, and you can see three options there – Insert , Delete and Format

cells

Insert:

Insert Cells

Insert Cells

Insert here has 4 options, as you can see in the figure above. Insert cells is used to insert single or multiple cells in a table.

For Ex, lets take a list of Names:

insertexample1

If you select a single column and the  try to use the insert cell option, then excel will insert one cell , above that cell (if you choose “Shift Cells Down” or Entire Row), to its left (Shift Cells Right or Entire Column) depending on your choice.

If you select multiple cells, while applying this, a similar operation will occur but instead of single cell, the number of cells that you have selected, same number of cells will be added to the table. For Ex. in the figure above, we have selected four cells and then used insert operation with the option of “Shift Cells Down”, so the result would be insertion of 4 blank cells above the first selected cell.

insertcellresult1

Insert Sheet Rows and Insert Sheet columns perform similar operation except that instead of inserting one cell, it always inserts entire row or entire column, based on your selection

Insert Sheet, adds one more sheet to your workbook.

Delete:

delete

Here also you are given 4 options and just like in insert, it deletes single cell,row, column or entire sheet based on your choice.

Format:

format

Format option allows you to do following operations on the table or range selected:

1. Cell Size

2. Visibility

3.Organize sheet

4. Protect

Cell Size:

Row Height can be changed for any row, you can even have different heights for different rows in your data table

AutoFit Row Height adjusts the row height according to the font size of the text or data that is present in the cell. This is usually a default option and happens every time you enter a data into any cell.

As the row height can be modified, same operation can be done on columns as well and you can use Column Width, AutoFit Column Width here.

If you choose, Default Width, it will allow you to give a standard width to all the columns in the sheet. This data will be used by default until you change the width yourself by using any of the methods mentioned above.

Visibility:

This allows you to Hide the data by hiding rows, or columns or the entire sheet, based on your selection. In case your sheet already contains hidden rows, columns or sheet, they can also be unhidden applying this operation. To hide a row or column, select it and then apply this operation. To  unhide a column or row, select the two rows or columns that are on either side of the hidden row or column and apply unhide. To hide entire sheet, just select Hide Sheet while you are working on the same sheet and to unhide it, use “Unhide Sheet” option from any of the sheets and the excel will show you all the hidden sheets and you can choose to unhide any or all of them.

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com

Sort & Filter

Sorting and Filtering is used for manipulation of data based on certain criteria. For example, one can sort the data in a range, by arrange it in ascending or descending order, or you can put filter which will help you to categorize the table data

Sorting:

Lets take an example to understand the operation

sample table

Lets say, we have an employee data here, working for 3 different companies, at 4 different positions , in 2 different departments along with their salaries

As you can see there is no pattern in which this data is arranged. This data is very small to search for the employee details, but suppose the actual data consists of more than 1000 people, then the table will not look very readable.

Lets make it easier by arranging the names in alphabetical order. This can be done in two ways. Either we arrange the names from A to Z or from Z to A. Lets say we start in ascending order i.e. A to Z

Steps to follow:  Select the table data, Go to Home -> Editing -> Sort& Filter ->Sort A to Z

sortasc

The resultant table will have the same data arranged in ascending order of the first column

sortedtable

A similar process can be used for arranging the same data in descending order.

Now, lets say you dont want to sort this data according to names but according to their increasing salaries. Excel offers flexibilty by allowing you to do custom sort

Steps to Follow: Home ->Editing ->Sort& Filter->Custom Sort

You will see a dialogue box asking you to choose the data column by which you want to sort the table

customsort

Select the column F in Sort by, as the salaries are given in the column F.

Sort on: you can sort the data based on its value, cell’s background color, font color or cell icon as well but here we will choose value as we want to arrange the data as per ascending salaries.

Order: this is Smallest to largest, as default selection but you can change the order by using Largest to smallest. In this order, Excel also provides an option to specify custom list, which we will discuss later. Lets take the default data here

N.B. Make sure you un-check “My Data has headers” if you have selected only the data but if you have selected entire table and your first row has headers then this option will help Excel to differentiate between headings and data and it will sort only the data

customsortdata

As you can see here the data is arranged with reference to the salaries starting from 4Lakhs which is smallest to 6.7 lakhs which is highest

Custom Order:

Lets take another example of a data of employees who have joined in 2012, and we add a data field which tells about their month of joining

Newtable

Now lets see we want to sort the employees as per the Month they have joined, then what would your process?

You can’t do it in ascending order, because then April will com after January, neither you can do in descending order. You want the table to be sorted as per the list : Jan,Feb,Mar…..

Steps to follow: Home -> Editing -> Sort&Filter -> Custom Sort

Here I have selected data along with headings so the the dialogue box that appears will show you the heading so it will be easy for you to select the column.

customerordersort

Sort by: Choose Joining Month

Sort on:Choose Values

Order: Choose custom, so you will see the dialogue box above.

Here you can see the excel has already provided 4 custom lists for your convenience for days and month, choose the list : Jan,Feb… and click OK.

The data will now be sorted as per the joining months of the employees

newtablecustomsorted

Isn’t this simple. Now you can use this custom order for your own lists as well.

Steps to follow:

IN the custom lists dialogue box, select NEW LIST and click add. this will allow you to enter the list in List Entries

newlist

As you can see, you can make any list as per your table data and then can use it for custom sorting.

Filter:

Filter is used to see specific data based on certain rules while hiding the other data in the table, without actually manipulating the table.

For Example, in the table given earlier, of employees, you want to see details of only those employees who have joined in Jan, or only those who have salaries greater than 5Lakhs or say only those employees who are Branding managers. In such cases excel allows you to put the filters to the headers such that you can choose the data as per your requirement.

Lets take an example where you want to see the data for only brand managers

Steps to follow: select the headers, Got to Home ->Editing ->Sort & Filter -> Filter

Once filter applied , you will see sign on every header. Go to Designation and press sign against it.

filterdesign

You can see here, Excel offers you different options for showing the data, you can use all the features of sorting here without changing the actual table.

Lets say you want to select only Branding managers, then un-Check Area Manager and Production Manager and Click OK

Now, what you will see will be the table with only brand managers. You can any time go back to header and change your settings if you want to see the entire table

filteredtable

Look at the table above, where we have expanded the view of designation, you can see one item as “Text Filter”. This filter , press that

textflter

You can see the custom filtering options here. So you can choose the data depending on you  text requirement.

In case you data is not text but numbers like in salary column, the Excel automatically recognizes the difference and change the option to Number filter

numberfilter

Here you can see, again you have many custom filter options as per your data

Such options can be very useful in case you have a huge data and excel provides you a great flexibility in displayed the items you want to see.

SHORTCUTS:

Applying Auto Filter : CTRL + SHIFT + L

To click sign to see items without using mouse : ALT + Down Arrow

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com

Enhanced by Zemanta

LOOKUP, VLOOKUP & HLOOKUP

LOOKUP Function:

It returns a value from a row, a column or an array. It has two syntax:

1. Vector : It looks for a value in single row or column and return another value from another row or column from the same position as the earlier value

LOOKUP(Lookup_Value,Lookup_Vector,Result_Vector)

Lookup_Value : Value that you are searching for

Lookup_Vector: Range where the lookup_value is present

Result_Vector: Range from where you have to find the resultant value in relation to the lookup_value

Example:

lookup

Lets find out the color for code 23 , using LOOKUP formula

LOOKUP(23,A2:A6,B2:B6) will look value 23 in range A2:A6 and will return the value against it in form the row B2:B6, which here is Ocean Blue

2. Array: It searches for a value in the first row or column from the array based on a text, number, logical value or reference. While it searches for the value given by you in first column of the array, the result is given by searching the last column of the array.

LOOKUP(Value,Lookup_Array)

Lookup_Array: The entire range from which you have to find out the value

Value: Value that you know and you want to find out its respective value from the table

Example:

lookuparray

=LOOKUP(“c”,A1:B4) will lookup for value “c” in first column and return the respective value from last column of the array, which in this case is Column B and the value thus returned would be “3”

VLOOKUP Function:

VLOOKUP is similar to Lookup function, it searches for the lookup value, but it is used only for columns. It searches in the first column for given lookup value and returns the respective value from another column specified by you.

Syntax: VLOOKUP(Lookup_Value,Table_Array,Column_Index_number,Range_lookup)

Lookup_Value: Value that is to be searched in first column of the array

Table_Array: Table array from where you need to find out the value

Column_Index_number: Column number from which you need to get the return value. if this value is 1, then value is returned from Column 1, if value is 2 then the value is returned from column 2 of table, and so on…

Range_Lookup: specifies whether you want to search for the exact value or approximate match. If this is FALSE, excel looks for exact match only, but if it is true, excel will first look for exact match and if not found then it will return value closer to it, i.e. approximate value.  If we do not specify this value, then the default value taken would be true.

Example:

vlookup

Lets say we need to find out the selling price of an item here.

Formula: VLOOKUP(“b”,A2:C6,3) will look  for value “b” in first column and return the value from 3rd column, i.e. from C. The result thus obtained here would be 23

As we haven’t specified Range_Lookup value, so this value will be TRUE

HLOOKUP Function:

It is similar to VLOOKUP except that instead of columns, it will refer to different rows in the table array

Syntax: HLOOKUP(Lookup_Value,Table_Array,Row_Index_number,Range_lookup)

Lookup_Value: Value that is to be searched in first row of the array

Table_Array: Table array from where you need to find out the value

Row_Index_number: Rownumber from which you need to get the return value. if this value is 1, then value is returned from Row1, if value is 2 then the value is returned from Row2 of table, and so on…

Range_Lookup: specifies whether you want to search for the exact value or approximate match. If this is FALSE, excel looks for exact match only, but if it is true, excel will first look for exact match and if not found then it will return value closer to it, i.e. approximate value.  If we do not specify this value, then the default value taken would be true.

Example:

hlookup

Lets say we need to find out the cost price of  3rd item here.

Formula: =HLOOKUP(“Cost Price”,A1:C6,4,TRUE)  will look  for value “Cost Price” in first rowand return the value from 4th row The result thus obtained here would be 34

As we have specified Range_Lookup value as TRUE, so it will look for exact or approximate value.So, if you don’t want to type the entire word “Cost Price” you can also type “Cost*” and the excel will still search for the value that contains the word “cost” and certain more characters after that. You can use any symbol here from *, ~,? and you will get the same result.

More LookUp and Reference Functions:

ADDRESS

AREAS, COLUMN & COLUMNS, ROW & ROWS

CHOOSE

GETPIVOTDATA

HYPERLINK

INDEX

INDIRECT

MATCH

OFFSET

RTD

TRANSPOSE

Back to All LookUp & Reference Functions

 

 

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com

Interior Object

Interior property returns the interior object.

Worksheets(“Sheet1”).Range(“A1”).Interior

Interior object has following members:

1) Application: Returns Application object that represents Microsoft Windows Application

2) Color: Sets the Primary color of the object. Whatever color you choose, will be filled in the selected object

3)ColorIndex: The Index specifies a particular color from the color pallet.

4)Creator: It is the hexadecimal number that indicates the application in which the object is created

5) Gradient: Returns gradient property of the object. It can have values like LinearGradient or RectangularGradient (transitions through a series of colors in a linear manner along a specific angle)

6) InvertIfnegative: True if excel inverts the pattern in the item when it corresponds to negative number.

7) Parent

8) Pattern

9) PatternColor

10) PatternColorIndex

11) PatternThemeColor

12) PatternTintAndShade

13) ThemeColor

14) TintAndShade

MACRO: Delete Blank Cells

Suppose you have a data table in Excel 2007 and you want to delete all the blank cells from the selected data.

blank1

As you can see, there are certain blank cells in the data, suppose you want to delete them, than you have to follow below steps

1. Select all cells

2. Go to “Home” – > Editing (“Find & Select”) -> “Go to Special”

special

You can see an option called “Blanks”, select that and enter “OK”

3. Now all the cells that are blank would be selected, Go to “Home” -> Cells (“Delete”) ->”Delete Cells”

delete

Select the option as per your data and press “OK”. BY default it will have “Shift Cells Up” selected and result will be a data table with all blank cells gone and the remaining cells will be shifted up.

result

This operation looks easy but it involves 3 different steps. Now if you want to perform same series of steps for another set of data, normally you need to follow the same procedure.

Recording a Macro however is a better way to perform this as this will create on macro which can be used for any data

How to Record

1. Select Data

2. View -> Macros -> Record Macro

macro1

GIve the name to macro that you can remember and provide it a shortcut key if you want

Press OK for Excel to start recording

Now perform the same steps from 1 to 3 as explained earlier and then stop recording.

This will create a macro once and for all. To see the coding that Excel creates , press “ALT + F11”

You will see Microsoft Visual Basic window

project

You can also see the code created by excel here. Excel will first create a new Module in which the code will be saved.

Lets understand the code:

Sub deleteblank()

‘ deleteblank Macro
‘ delete blank cells in the data selected


    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Range(“A12”).Select
End Sub

deleteblank()…….is the name given to macro by you in first step

Selection. SpecialCells(xlCellTypeBlanks).Select:

As you have selected a data,  the excel recognizes that, then you selected “Go to Special” . In Visual Basic same can be coded as

SpecialCells(xlCellTypeBlanks)

This is method used by excel , which has a syntax: Expression.SpecialCells(Type,Value)

Type: is the kind of data you want to select, this is mandatory to specify.

Here we have selected type as xlCellTypeBlanks i.e. cells with blank data

Value: This used to specify the type of cells to include and it is optional. Its default value is xlCellTypeAllFormatConditions i.e. All cells of any format to be selected.

But here, you can specify a value on which will depend the kind of data that will be selected

xlCellTypeAllValidation: Cells having validation

xlCellTypeBlanks: Empty cells

xlCellTypeComments: cells containing comments

xlCellTypeConstants: Cells with constants

xlCellTypeFormulas: Cells containing formulas

xlCellTypeLastCell: Last cell in the used range

xlCellTypeSameFormatConditions: Cells with same formats

xlCellTypeSameValidation: Cells with same validation criteria

xlCellTypeVisible: All visible cells

Here we have not specified any value so the excel will select all cells with all format and out of those, it will select only those cells that have blank values inside them

Selection.Delete Shift:=xlUp

This will perform the operation of deleting the cells by shifting the remaining cells up. This is done by using a method “Delete”

Syntax: Expression.Delete(Shift)

Shift: It is used to specify how to shift remaining cells to replace the deleted cells

Here we have used xlUp i.e. shift cells up

Other option that can be used here is  xlShiftToLeft, which will shift the remaining cells towards left

Range(“A12”).Select:

This has put the cursor on cell A12, which is the last cell after the complete data

Range is similar to that used in normal Excel 2007 formula which allows you to select the cells specified inside  the brackets

Now you can use this macro to perform “delete blank” operation for any set of data in the worksheet.

This post is written by Pooja Dubey Srivastava, who is the founder of at www.dailystoryspider.com