excelintoexcel

Formula | Chapter 5 | Workday

2020-04-29T12:22:00.000+05:30

Workday formula

A very simple formula and much usage too.

In simple terms this formula ascertains a date (past or future), may be a due date.

Syntax

=Workday(start_date, days, [holidays])

start_date= A date to represents the start date.

days= A number of working days other than holidays and weekends. Negative days denotes past date.

Above two parameters are required.

holidays = An optional list of one or more dates to exclude from working days.


Formula Workday ILL1

Explanations:

Manual calculations has been made to understand the essence of the formula. Two respective dates in bold (colored) representing two formulas. For the sake of understanding two formulas also written below the dates. I request all to redo the process.

Workday.int Formula

A simple formula with usage.
It determines a date (future or past). May be a due date.
Very much similar to workday formula.
Syntax
=workday.int(start_date, days, [weekend],[holidays])
start_date = A date.
days = numeric value.
start_date and days are required.
weekend = (1-17)This parameter signifies the counting option. Ordinarily weekend comprises of 'Saturday' and 'Sunday'. Default parameter.
But if one wants to vary the calculation of weekend. They can specify.

1. Saturday, Sunday (Default and can be omitted)
2. Sunday, Monday.

3. Monday, Tuesday.
4. Tuesday, Wednesday.
5. Wednesday, Thursday.
6. Thursday, Friday.
7. Friday, Saturday.
11,12,13,14,15,16,17
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday,
respectively.


Formula Workday.int ILL2

'Year' Formula

This function/formula returns the year corresponding to a date.

Syntax
=Year(serial_number)
Lets use ILL2
And use the date in cell C4
=Year(C4)
Returns 2020.

'Yearfrac' Formula

This function calculates the number of whole days between two dates. It returns numeric values with fraction where needed.

Syntax

=yearfrac(start_date, end_date, basis)

start_date= A date. (future or past)

end_date= A date. (future or past)

basis = 0,1,2,3,4

0 - US (NASD) 30/360 (no need to specify can be omitted)

1 - Actual/actual

2 - Actual / 360

3 - Actual / 365

4 - European 30 / 360


Formula yearfrac ILL3

Explanations:

In the ILL1 1.01666667 is the result of the formula =yearfrac(E6, E7, 2) - Basis 2 that signifies Actual / 360.

Formula | Chapter 4 | Time

2020-04-22T11:04:00.000+05:30

'TIME' formula

This formula returns the decimal number of time and return value varies from 0 to .99988426.

Syntax
=Time(Hour, Minute, Second)

Here Hour, Minute and Second is represented by 0 to 32767.
In case of hour, if any value is greater than 23 will be divided by 24 and remainder will be treated as hour value.
Similarly if any value is greater than 59 in case of Minute the same shall be converted into hour and minute.
Again if any value is greater than 59 in case of Second the same shall be converted into hours, minutes and seconds.


Time Formula ILL1

Explanations:
Go through the above illustration and try it yourself.
This illustration is self explanatory.

'TimeValue' Formula

This formula converts a text to a serial number for time, ranging from 0 to .99998426.

Syntax

=Timevalue(text)

Say

=Timevalue("12:00:45 am")

returns 0.000520833

Instead if we replace am with pm

formula returns 0.500520833

'Today' Formula

This formula returns the current date. Actually it picks up the system date.

A widely used formula.

Syntax

=Today()

Returns the current date - say '28-03-2020'.

'Weekday' Formula

This formula returns the day of the week corresponding to certain date.
Syntax
=Weekday(serial number, return type)
We can proceed without specifying the return type.
say, 'Weekday(Date)'
The formula returns 1-7 integer only.
It simply specify the day of the date in a week. And return type parameter specify the counting pattern.
Counting syntax (1,2,3,11,12,13,14,15,16,17)
1 - Default 1(Sunday) to 7(Saturday)
2 - 1(Monday) to 7(Sunday)
3 - 0(Monday) to 6(Sunday)
11 - 1(Monday) to 7(Sunday)
12 - 1(Tuesday) to 7(Monday)
13 - 1(Wednesday) to 7(Tuesday)
14 - 1(Thursday) to 7(Wednesday)
15 - 1(Friday) to 7 (Thursday)
16 - 1(Saturday) to 7(Friday)
17 - 1(Sunday) to 7(Saturday)


Formula 'Weekday' ILL1

Explanations:

Column C only represents the basic formula or default formula.
For instance, 15-03-2020 falls on Sunday (consult calendar). And formula returns 1.
Say, the formula with date 21-03-2020 returns 7. Calculating 7 from 15th March and ends on 21-03-2020 (inclusive).
Now if we deviate the calculation from the default calculation.
=Weekday(serial number, return type)
'return type' specifies different calculation option.
Options are specified in 2nd row. And they are 1,2,3,11........17.

'Weeknum' Formula

This formula returns the week number of a specific date.
The formula returns 1-52 or 53. Since a year is comprised of 52 weeks.
It is bit similar to 'Weekday' function.
Syntax
=Weeknum(serial number, return type)
Return type (optional) determines on which day the week begins.

Formula 'Weeknum' ILL2

Explanations:
Return type here denotes numeric values 1,2,11,12.....21.
For instance numeric value 15 denotes..... week counting starts from 'Friday'. Hence the result varies.

Formula | Chapter 3 | Month

2019-12-01T10:53:00.000+05:30

'MONTH' formula

This formula returns the month 1-12. This formula simply picks the month in the date.

Syntax
=Month(Serial_number)
Observe the illustration below.


Month Formula ILL 1

Explanation:

Under alternative method the formula picks the month from a text string.

'NETWORKDAYS' formula

This formula calculate working days between two dates.

Syntax

=Networkdays(Start_date, End_date, holidays)

Observe the illustration below


Networkdays ILL2

Explanation:
The formula above returns 262 days. (Try It)

NETWORKDAYS.INTL formula

This formula is a bit modification of the 'networkdays' formula.

Extra parameter weekend is added.

Syntax

=Networkdays.intl(Start_date, End_date, weekend, holidays)

while writing formula weekend can be picked up from the auto drop down box.


Networkdays.intl ILL3

Explanation:
The above formula returns 314 days. (Try it)

'NOW' formula

This formula returns the current date & time.

It actually accepts system's date and time. This is widely used formula. It usually don't require any parameter from user.

Syntax

=Now()

This formula above returns the current date and time.

(Since I am writing this blog at the below mentioned date and time.)

01-12-2019 10:23.

(Things to remember: This formula update itself automatically)

There are some variations available to the above formula.

Say (01-12-2019 as base date)

we calculate the 7th day date from the present base date,

formula is

=Now()+7

Returns 8-12-2019 10:23

=Now()-2.25

Returns date 2 days 6 hrs ago. Since .25 represents a qtr. Each qtr comprises of 6 hrs in a day.

'SECOND' formula

This formula returns the 'second' ranging from 0-59.

Syntax

=Second(serial_number)


Second ILL4

Formula | Chapter 2 | Edate

2019-11-27T09:38:00.001+05:30

'EDATE' formula

Edate is used to calculate due date or maturity date.
Syntax

=Edate(start_date, months)
start_date: denotes a date in date format.
months: denotes tenure of months of maturity.
We can quote this formula in different ways.
Say, we are going to calculate 12 months maturity.
A3 cell has date value 25-11-2018 (should be in a date format).
so the syntax is =Edate(A3,12)
Returns: 25-11-2019 (date of maturity).
Alternatively the syntax is
=Edate(Date(2018,11,25),12)
Returns the same value 25-11-2019 (date of maturity)

'EOMONTH' formula

It returns the serial number of the last day of the month before or after a specified number of months.

Syntax

=Eomonth(start_date, months)

start_date: denotes a date in date format.
months: denotes tenure of months of maturity.
We can quote this formula in different ways.
Say, we are going to calculate 12 months date.
A3 cell has date value 25-11-2018 (should be in a date format).
so the syntax is =Edate(A3,12)
Returns: 30-11-2019 (date of maturity).
Alternatively the syntax is
=Edate(Date(2018,11,25),12)
Returns the same value 30-11-2019 (date of maturity)

'HOUR' formula

Returns the hour of a time value, and the result varies between 0 to 24 only (whole number only....no 18.25). This formula works with decimal places. Say, in case of 1.25 it ignores 1 and works with .25 only.

Syntax

=Hour(serial_no)

For instance

=Hour(1.25)

returns 6

Again

=Hour(.25)

returns 6

Actually .25 of 24 is 6........... (25% of 24 is 6)


ILL 1

Explanation:
Observe the ILL 1 carefully.

'ISOWEEKNUM' formula

Returns the week number for the given date.

Syntax

=ISOWEEKNUM(date)

For instance

Cell A3 has date value 1-1-2019 in date format.

=ISOWEEKNUM(A3)

Returns 1i,e first week of the year 2019.

In case of 7-01-2019 formula returns 2.

'MINUTE' formula

The formula returns the minute of a time value. Minute is represented as integer, ranging from 0-59.

The formula accepts decimal places. No whole number.

Representing percentage.

Syntax

=MINUTE(serial_number)

For instance

=MINUTE("12:35 PM")

Returns 35

Formula | Chapter 1 | Date & Time

2019-11-23T09:24:00.000+05:30

Formulas can be accessed by clicking at the 'Formulas' tab.

Function key and 'Insert function' button serves the same purpose.

'Formula bar' keep track of your formula. Not only formula, what you write in a cell is highlighted in the formula bar.

When we click Insert Function / Function, Insert Function window pops open.


Insert Function Window

All type of formula resides in 'Insert Function' window.
And if we click select a category combo box we can find the category of formula in excel.

Date & Time

Interestingly Excel stores dates as sequential serial numbers.

And 1st January 1900 is the base year, and the serial is 1.

Accordingly, 1st January 2008 has a serial number 39448 since 1st January 2008 is 39447 days after 1st January 1900.

'Date' formula

Returns the number that represents the date in Microsoft Excel.

Syntax

=Date(year, month, day)

Example

Date(2019,03,23).

Returns 23-03-2019.


Date function

'Datevalue' formula

Converts the stored date into serial number.

Syntax

=DateValue("text")

Example

Date("1-1-2008")

Returns 39448

This function is mostly useful where worksheet contains dates in text format.

'Day' formula

Returns the day value from 1-31. And it calculates accordingly.

Syntax

=Day(serial_number)

Example

Day(100)

Returns 9
In fact representing 10th April.

'Days' formula

Calculates number of days from a base date. It can calculate backward too. Again the base date should not be later than 1-1-1900.

Syntax

=Days(end_date, start_date)

Days360 formula

Us or European method of approximation of days calculation from a base date.
Syntax
=Days360(start_date, end_date, method)
There are two methods
False=US method of approximation of 30 days.
True= European method.

Formula | Auto Sum | Deduct | Multiply | Divide

2019-11-19T06:11:00.000+05:30

This is the first dedicated post on Excel Formula.
To access formulas click Formulas tab.
Even we can start with formula under Home Tab too.


Under Home Tab

Excel is always formula ready. One can start dealing with formula at any space.

Things to remember

Formula always starts with =
While writing formula ignore upper case or lower case criteria, just start typing.
Instead of typing the cell values while writing formula one can link or denote the corresponding cell by clicking at the cell. Say, want to multiply F2 to G2, only type = and then click at F2 cell then type * lastly link G2 by clicking at G2 cell. Simple......

Lets start

Sum

How to sum up cells with values?
Formula Syntax
=SUM(B3:B14)
(B3 to B14 cells are just imaginary)

Alternatively

=B3+B4+B5+B6.......................so on
Result will be same as before

For instance, we are going to sum up the values of cells F3 to F13
and display the summation at F14.

Just select the cells F3 to F114.
Under Home tab click 'Auto Sum'.

Excel will automatically make the sum of cells F3 to F13 and display the resultant figure at F14.

Alternatively

At F14 cell start writing the formula as =sum(
At this instance just select the F3 to F14 cells altogether.
Close the bracket.
Press Enter.

Deduct

Say we are going to deduct the cell value F13 from F14 cell value.
Very simple logic.
Write the formula
=F13-F14

Again
We are going to deduct 100 from cell F13 Value.
=F13-100

Multiply

In excel or precisely computer recognize * asterisk as a sign of multiplication.

Say, we want to multiply cell value F13 with G13 and display the result in H13.

At H13 cell write the formula

=F13*G13

Division

Excel as well as Computer recognizes division with the sign '/'.

Say dividing F3 cell with F2 cell.

Syntax is

=F3/F2

Very simple logic.

Auto sum function is always very handy in all instance.

If we observe carefully it combines all function at once.

Observe the ILL 1 below.


ILL1

Explanation:
Auto Sum function combines all.

Go to | last cell | visible cells only

2019-11-16T10:09:00.002+05:30

Last Cell

Last cell denotes the cell that contains data and/or formatting.
Even one deletes the data and/or formatting of the cell, Last Cell function identifies that cell.


Last Cell

Procedure

Click any where in the sheet.
Press Ctrl+G and click 'Special..' or Click 'Find & Select' under Home Tab and click 'Go to Special...'.
Tick the Radio button associated with 'Last Cell' function.
Click Ok.

Visible Cells Only

'Visible Cells Only' function identifies those cells that are visible in a range. It helps to identify the filtered rows or columns when filters are enabled in a sheet.

Even it helps to identify any hidden row or column in the sheet.

It is useful.

Conditional Formats

'Conditional Formats' function identifies the cells with conditional format embedded.

Data Validation

Data validation function identifies cells with data validation embedded. Further sub categorized as 'All' and 'Same'.

Go to | Precedents | Dependents

2019-11-13T10:45:00.001+05:30

Precedents and Dependents feature sometime proves useful.

Cell Precedents

This function is applicable to cells that contain formulas only.
Precedent cells are cells that contribute 'directly' or 'indirectly/All levels' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cell G1 has two precedent cells namely E1 and F1.
Indirect/All levels precedent cells don't directly contribute to formula result but used as reference.

Precedent cells are often used to identify irregularities in formula.

How to identify precedent cells?

Select the cell (to identify precedents)
Press F2 - This function colorize the precedent cells but is limited to the active working sheet.
Under Home Tab click Find & Select.
Click Special.
Check the radio button associated with 'Precedents'.
Further check the option 'Direct' or 'All levels'.
Click Ok.

Shortcut Ctrl+[
This shortcut identify precedent cells in the active sheet.

Cell Dependents

This function is applicable to cells that contain formulas only.
Dependent cells are cells that contribute 'directly' or 'indirectly/All level' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cells E1 and F1are dependent on G1. There must be at least one dependent cells in case of formula.

Hence before deleting any formula cell one should go through the precedents and dependents options.

How to identify dependent cell?

Select the cell that you want to identify dependency.

Under Home Tab click Find & Select.
Click Special.
Check the radio button associated with 'Dependents'.
Further check the option 'Direct' or 'All levels'.
Click Ok.

Shortcut

Ctrl+]

So before deleting any cell one should check the dependency too.

Alternatively

Precedents and Dependents can also be found under 'Formulas' tab.


Under Formula Tab


ILL 1

Explanations:

G2 & G3 cell contents are the multiplications of E2 and F2, E3 and F3 respectively.
Active the cell F2 and click 'Trace Dependents'.
Active the cell G3 and click 'Trace Precedents'.
To remove the arrows click 'Remove Arrows' (further sub categorized into 'Remove Dependent Arrows' and 'Remove Precedents Arrows')

Go to | Row Differences | Column Differences

2019-11-09T08:53:00.001+05:30

Row Differences

Excel has a very important function to find out differences between various rows. The function is called Row Differences.
To access the function

Shortcut key - Press Ctrl+G.
Click Special

Alternatively

Under Home Tab click 'Find & Select'.
Click Go to Special.

ILL 1 Row Differences Option

Lets illustrate with a data range.


ILL 2 Data Range

You are requested to go through the above selected data range. Where A2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly row wise. Of course some are common.
After highlighting the data range.

Procedure

Press Ctrl+G (Instead use Find & Select).
Click 'Special'.
Highlight the radio button associated with 'Row Differences'.
Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:

Result

Explanations:
In the 2nd row content of cell C2 is different from cells A2 & B2.
One can argue that the content of cell A3 is different from cell contents of B3 & C3. So why B3 & C3 is highlighted? Why not A3? Since B3 & C3 are similar and A3 is dissimilar.

Lets observe the data range (ILL 1) once again.
We can see that the default active cell is A2 (white), so the basis for comparison starts from column A.

According to the logic since the content of cells B3 & C3 are different from content's of cell A3, hence B3 & C3 has been selected.

The basis can be shifted by pressing Tab key (shifting the active white cell). Result would be different.

Again

Shifting the active cell to cell B3

Result

Explanations:
Since the basis of comparison is shifted to cell B3, result is different from previous one.Here A3 cell's content is different from B3's content (basis) and C3's content, hence A3 has been highlighted.

Column Differences

Excel has a very important function to find out differences between various rows. The function is called Column Differences.
To access the function

Shortcut key - Press Ctrl+G.
Click Special

Alternatively

Under Home Tab click 'Find & Select'.
Click Go to Special.

ILL3 Column Differences

Lets illustrate with a data range.


ILL4 Data Range

You are requested to go through the above selected data range. Where B2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly column wise. Of course some are common. After highlighting the data range.

Procedure

Press Ctrl+G (Instead use Find & Select).
Click 'Special'.
Highlight the radio button associated with 'Column Differences'.
Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:


Result

Explanations:
In case of the column C the contents are different C2 cell's content is different from cell's content of C3 & C4. Hence C3 & C4 has been highlighted.

In case of column differences the basis of comparison is row wise.
If we observe carefully the data range (ILL4) we will see the default active cell in the selected data range is cell B2 and the cell lies in 2nd row. As a result the basis of comparison is the 2nd row.

But if we move the active cell (white cell in ILL4) to 3rd row (by pressing Tab key) and follow the procedure result will be different.


Changed Result

Go to | Special | Blank

2019-11-06T08:40:00.001+05:30

Microsoft Excel provides a dedicated dialogue box from where you can access some particular group of cells.

Shortcut Ctrl+G.
Click 'Special'.

Alternatively

Under Home Tab click 'Find & Select'.
Click 'Go to Special'.

Comments

You can find radio button associated with 'Comment' is by default selected when you access the Go to special dialogue box.

Click Ok.

The cells in the range with comments will be greyed out.

Constants

This option refers to cells that contain constants.

There are other sub categories.

Numbers.
Texts.
logical.
Errors.

Formulas

Formula options refer to cells that contain formulas.

There are other sub categories

Numbers.
Texts.
Logical.
Errors.

By default all are usually checked.
Say if you want to find only formula that relates to numbers check mark only 'Numbers'.

Blanks

This function denotes those cells that are blank within the data range.

It also recognizes only space/spaces within a cell but the cell is also blank. It will not consider this cell while selection. Since it is not blank technically.

Typically what I believe this function recognizes data column wise.

If the datasheet contains any cell that is non contiguous to other cells but has data in it, it will consider entire column of that cell as blank. Observe the exhibit below.


Blank Cells greyed out

The cell marked with Red square has spaces, Blank function didn't consider the cell while selecting blank cells. Also the column J has been considered as it has a single cell of data (Binder). Again the column I has been considered as blank as no data in it but contiguous to main chunk of data.
But columns K, L, M etc are not considered.

Current Region

Current Region here denotes the entire list or data.

Click any where in the data range and select Current Region, excel will grey out the entire data range. Very effective way to select a large piece of data.

Current Array

It will select entire array (we will discuss array in later posts) if active cell is contained in an array.

Objects

Go to function with 'Objects' selected denotes graphical objects including charts and graphs in the worksheet.

Go to | Name Range

2019-11-02T10:43:00.001+05:30

Go to lets you move swiftly to a specific line, range, cell, row, column in the workbook.

How to activate 'Go to' window? (Shortcut Ctrl+G)

Under the Home Tab click 'Find & Select'.
Click Go to.


Go to window

Instance 1
Selection of Cell D15.

Under Reference text box type D15.
Click Ok.

One thing to remember D15 signifies the cell name as per excel.
Cursor will move to the intersection of D column and 15th Row.

Instance 2
Selection of Cells A1 to D15.

Under Reference text box type 'A1:D15'.
Click Ok.

Go to function can even recognize customized name of cells, range and columns etc.

Instance 3
Selection of non contiguous cells A3 to A4 & C9 to G9 & A11 to G17.

Under the Reference text box type A3:A4,C9:G9,A11:G17
Click Ok

Instance 4

Selection of a particular row say 9

Under the Reference text box type 9:9.
Click Ok.

Again selection of multiple row say 9,10

Under the Reference text box type 9:10
Click Ok.

Instance 5

Selection of a particular column say D

Under the Reference text box type D:D
Click Ok

Again selection of columns say D & E.

Under the Reference text box type D:E
Click Ok.

Even Go to function can identify any name range which user provides.

Select the range.
In the name box type the desired name (advised to ignore digits and put underscore in between). (for instance excel_excel is the desired name selected for the sake of discussion).
Press Enter.
You can see in the name box 'excel_excel' has appeared as the customized name of the range previously selected.

Just highlight the desired name from the above box.
Click Ok.

Find & Replace | Replace

2019-10-30T10:54:00.003+05:30

Replace

Replace is an important function in Excel.
May be it is convenient to alter the cell content where the number of cell is less, but when data is huge Replace function excels.

One can alter/change/remove the content of cells by use of Replace function.

It is located in 'Find & Select' function under Home tab.
Short Cut 'Ctrl+F.'

Easy and simple format just 'Find What' and to 'Replace With'.

Replace is an intelligent function to remove or delete any particular content of cells from the data sheet.

Say we want to remove or delete the text 'Binder' from the datasheet.
Just type 'Binder' in 'Find What' text box, and keep 'Replace with' text box blank. Press the button 'Replace All', it will just replace or delete all text 'Binder' from the data sheet and keep the cells blank as we didn't specify anything to fill in.
Now the problem may arise that it will delete or replace the text 'Binder' from all cells but where there may be a text called 'Hard Binder' it will remove text 'Binder' and text 'Hard' will only be left in the cell.

To overcome this problem

Click the 'Options<<' button.
Tick the box 'Match entire cell content'.

Now the replace function will ignore the text 'Hard Binder'.

Replace Format

It is an intelligent option to change the format of cells.

Say we want to make 'Binder' text into 'Bold' in all the cells where the content is 'Binder'.

Press Ctrl+F (Find & Replace Dialogue box pops up).
Activate Replace Tab.
Click 'Options > >' button.
Write 'Binder' in both the textbox (Find What & Replace With) since we are going to replace 'Binder' with 'Binder' just with different format.
Click 'Format' button relative to 'Replace with' (Extreme Left).
In the 'Replace Format' dialogue box.
Activate 'Font Tab', highlight 'Bold' under Font style.
Again Activate 'Fill' tab.
Click 'Fill Effects'.
Under Shading Style highlight any Variant.
Click Ok

Click 'Replace All'.

All the 'Binder' text will be replaced with a special format you chosen.Under preview button one see the format that will be incorporated.
One can clear the selected format by clicking 'Format' button and Click 'Clear Format' option.

Find & Replace | Find

2019-10-27T13:37:00.003+05:30

Find

Find is an option in Excel with the help of which we are going to find anything in the data sheet/worksheet.
Find is a search function inbuilt in Excel.

One simply needs to specify the search criterion. Excel will do the rest.

It is located in 'Find & Select' function under Home tab.
Short Cut 'Ctrl+F.'

Previous post's data sheet is used for demonstration.


Find & Replace

Find Window

How to find?
Say we want to find 'Smith'.
Type Smith in 'Find what' text box.
If you press Find Next, you can view cells one at a time where content of the cell is Smith. In fact it is not the view, the cursor goes on making each Smith's cell active when you press Find Next.

If you press 'Find All' a separate window pops below where number of find can be seen with respective cell names.
And if you click each row in the window the cursor will move to the respective location in the sheet.

Results of Find

3 Cells found.
Now where the results are located?
In the SalesOrders(2) sheet of Filter.xlsx file.
And the respective cell numbers are C15, C21, C24.

Links has been created for each find. Cursor transforms into 'hand' when we hover the mouse cursor above each entry. If we click any entry excel makes the respective cell active in the corresponding data sheet.

Here excel ignores upper case and lower case sensitivity.
Since we put 'smith' for search criterion but the contents are 'Smith'.

But interestingly excel accepts any blank space into consideration.
Say if we put smith with a blank space after h (by pressing space bar) excel will fail to find any. Since no cell contained with Smith has any blank space after h. Of course if any content of cell with smith is with a blank space after h, excel will find that cell.

Again excel can find any blank space 'only'. We put a blank space in the Find What text box (by pressing space bar once), and we know a cell with Smith has a blank space after h.
Excel can find that cell along with other cells where there are blank spaces.
Excel can find special characters. Say #, @,. etc

Even excel can find digits or alphabets in any content. Say we put 2 for search, and a date 10-12-2019 is there. Excel will take into consideration this date along with other content say 72,727 etc.

Again if we put '-2' for search, excel will find this date only with other dates where search criterion matches.

You can also use wild card characters like *,? for search criterion.
Type p*n to find string of characters say pen & pencil.
Again type c*l to find central and pencil.

Use question mark ? to find a single character.
Put p?n to find pen.
Put 4?9 to find 449.10.

Now if we want to find ? or * what will we put as a search criteria?
Put ~ (tilde) just above the tab key in keyboard.
~? or ~* to find ? and * respectively.

Advanced find options

Click 'Options>>' in the Find & Replace window.

Advanced Option

In advanced option One can specify the sheet or workbook to search, by clicking at 'Within' combo box.

One can specify the pattern of search say by row or column wise. If column wise is specified excel first reads the first column then the next and so on. In row wise search excel search rows one after another.

Lookin combo box specifies whether to search formulas also including text. And if one specifies text in the combo it ignores formulas. Say 2 is multiplied with the content of the cell G1 and the formula thereof would be G1*2. Now if we search G and specifies 'text' in the drop down combo it ignores the G in the formula cells. It may be handy.

Now one can search contents of comments by specifying 'comments' in the look in combo box. Where in the search ignores all cell that are not commented.

Another important aspect of Find is, one can specify the area of the search in the worksheet. Say we need to search a telephone number within a column of telephone numbers. We can select the column only and search. Search results will be limited to the column only.

Match Case:

As it has been said that excel ignores uppercase and lowercase in case of ordinary search. But if we tick the check box associated with Match Case, excel will be very specific about its search. Excel will make the difference between SMITH & smith. If we search for 'smith' it won't find 'SMITH'.

Match Entire Cell contents:

If we tick the check box associated with 'Match Entire cell contents' excel will again be very specific about its search. In the ordinary scenario if we want to search for 'Binder' we can put the search criteria as 'bind'. We the box is ticked it won't find 'Binder' with search criteria 'bind'. It will consider those cells where entire search criteria matches.

Find Format:

We can search and find a particular format in the worksheet or workbook. Say we want to find all the 'bold' characters in the data sheet.

Find Format

Click 'Format' (as above).

Under 'Find Format' dialogue box click Font Tab.

Under Font Style click or highlight Bold.

Click Ok.

'Find What' text box should remain empty because we are not searching any particular text or digit. We are simply searching the bold characters and digits in the sheet.
Click 'Find All'.
Even you can search for colored cell, font style, italics, underlined etc. in this manner.

Sort and Filter | Chapter 2 | Filter

2019-10-24T10:42:00.003+05:30

Filter

To access Filter function click 'Sort & Filter' option under Home tab.

Filter function is also available under 'Data' tab.

How to incorporate Filter function?

Click anywhere inside the source data where you want to incorporate Filter function.
Click Filter (as marked with Red above).


Filter option enabled

We are going to use simple data sheet to understand Filter function.
Excel Table has default Filter option enabled.

Download the data sheets here.

We are going to understand filter function with the help of some examples.

Instance 1

Filtering the data according to Central Region.

Click drop down arrow of Region cell (B1).
Uncheck '(Select All)'.
Check 'Central'.
Click Ok.

Explanations:
Drop down arrow of Region (B1 Cell) has been changed to a 'Filter' sign. It symbolizes that the column has been filtered. Again in respect of rows it can be seen that some rows has been hidden only. Original data is intact.
If you want to view the entire sheet without filter.

Click the filter symbol on Region cell.
Check (Select All).

Instance 2

Lets filter out representative Smith's performance in the Central region.

Central Region to filter as above.
Click drop down arrow of Rep cell.
Uncheck 'Select All'.
Check 'Smith'
Click Ok

Explanations:
Region and Representative column has been filtered as drop down arrow has been changed to a filter symbol.
Now if we want to revert to the original view, then we have to remove filter from Rep first and then from Region. It is better to remove filter from the last filtered column first and then proceed accordingly.

Instance 3

Excel automatically recognizes date format.

It changes it filter box accordingly.

Lets find out the performances in Jan & Feb for 2019 only.

Click the drop down arrow Order Date cell.
Again Uncheck '(Select All)'.
Click the '+' button associated with 2019.
Check Jan & Feb.
Click Ok.

Alternatively

Lets find out a different fashion for achieving the same result as above.

Click the drop down arrow in Order Date cell.
Guide the mouse pointer to 'Data Filters'.
Click 'Between'.
Check the radio button 'And' if not checked.
Put the start Date 1-01-2019.
Put the end Date 28-02-2019.
Click Ok.

Custom auto filter dialogue box

Explanations:

Adjust dates by click the icon marked with Red circle.

Instance 4

Lets find out in respect of the item 'Binder' the representatives who failed to achieve triple digit sales figure.

Click the drop down arrow of 'Item' cell.
Uncheck Select All.
Check Binder.
Click Ok.
Again click the drop down arrow of 'Total' cell.
Hover the mouse to option 'Number Filters'.
Click 'Less Than.......'
Put 100 (since any amount less than 100 is double digit figure ignoring decimal places of course).
Click Ok.


Custom Auto Filter

Result as follows:

Slicers & Timeline can also be used to filter data.

Sort and Filter | Chapter 1 | Custom sort

2019-10-21T18:05:00.001+05:30

Sort & Filter

Meaning of Sort in Excel.

Rearrangement of rows on the basis of a content of a particular column.


Sort & Filter

Located under Home Tab
By clicking the Sort & Filter Drop down arrow


Sort type

We are going to discuss with a data sheet. If required one can download here.


DATA Sheet

There are immense opportunities for filtering and sorting.

Excel automatically understands the sorting opportunity accordingly.
For instance when the active cell is under Date column the sorting field will change from 'Sort A to Z' or 'Sort Z to A' to 'Sort Oldest to Newest' or 'Sort Newest to Oldest'.

By default it is 'Sort A to Z' or 'Sort Z to A'.

How to sort?

Active any cell of the column on the basis of which you want to sort.
Click 'Sort & Filter'.
Sort A to Z or lowest to highest or newest to highest accordingly.

Excel effectively understands column headers. Excel won't include column headers such as Order Date, Region, Rep, Item etc as the item for sort.
Now if we delete the header row excel will sort the entire data as it cannot identify any header row as such.
Again in the above data sheet if we select the entire data (including header row) excel will sort according to date, since it is the first column in the data sheet.

If we consider each row of the data as a 'Data Record' Excel will sort each data record on the basis of your choice. You can try this by coloring a data record with a specific color of your choice.

Custom Sort

Custom sort is interesting and I always prefer one to go with custom sort. May be it is complex.


Custom Sort window

In the case of the above data sheet Excel automatically finds the header row as you can see 'My data has headers' check box is checked. If there no header row then excel will sort column by column name say 'A', 'B' etc. Even one can de check the associated check box of 'My data has headers' and perform sort by column name.

Add Level is very interesting options in custom sort. Say, you want to first sort the data sheet by date (say Oldest to newest) and then by Region (say A to Z).

The sort window is somewhat as follows.

Click Ok
You can also shift levels up or down in multi column sort.

Custom List

Custom List is another very important aspect of Custom Sort.

For instance, in the case of the concerned data sheet if we want to sort the 'Item' in the order of Desk, Pen, Pencil, Pen Set, Binder.

That means neither A to Z nor Z to A, just an order of our own.

Under Order there is an option called Custom List.

Here we can find other orders that Excel Follows. Default lists cannot be deleted.

Click Custom List

Write in the 'List Entries Text Box'
"Desk, Pen, Pencil, Pen Set, Binder"
Alternatively first write Desk and press Enter then write Pen and press Enter and so on. As it is said 'Press Enter to separate list entries' in the window itself.
Click Add and the new series will be shifted to the left under Custom List window.
Then Click Ok.
This custom series will be highlighted in the custom sort window 'Order'.
This list will remain in the excel unless one deletes the same.

Fill Data | Chapter 3 | Fill Handle

2019-10-18T17:38:00.002+05:30

Fill Handle

In the previous chapters we dealt with filling data in excel. In this chapter we will understand the shortcut method of feeding data in excel with Fill Handle or Cross Hair. Truly speaking most excel users are much comfortable with this approach.
A very small length video.

Explanations:

The plus '+' sign or cross hair is the Auto fill handle.
After left click pressed and dragged, at the end of the selection another dialogue box emerges called 'Auto Fill options'.

We will discuss each and every aspect of Auto Fill option box. Except Flash Fill already discussed in the immediate previous chapter. Click here.

Copy Cells: Only the cell content is copied by click and drag. Since the content is copied the relevant radio button is already checked.

Fill Formatting Only: Say at the above display if we check the radio button 'Fill Formatting Only', format of the source cell will be copied to other cells (The texts will be erased and formatting will be set in from the source cell).

Fill Without Formatting: Alternatively texts only be copied to other cells. No formatting will be transferred from source cell.

Since auto fill handle and option is an operational procedure it is better to understand the procedure with the help of a video.


Some query for auto fill

Result as follows:

Explanations:

Instead of Click & Drag one can double click on the Fill Handle. Excel automatically fills the cells below it with the required data. In case of Fill Text, format is also copied along with the content of the source cell. And with the Auto fill options box one can change the fill.
Double click wont work if there is no corresponding data that tells excel the extent of the fill. Here Formula column already been extended to 14th row so excel understands and assumes that the length of the fill may be up to 14th Row. Even if there is a blank row in between, excel will stop at the blank row. Say if 10th row is all through blank then excel will calculate up to 10th row.
Another trick - instant of Left Click and Drag what about Right Click and Drag of the Auto Fill handle?

Say in case of the date series as above if we Left Click and drag the Auto fill handle another window appears that has other options too. And when you click any options in the window excel will fill the column accordingly.

Disabling Auto Fill Handle & Options

By default the option is enabled but if any one wants to disable the option one can follow the steps below.

Click File Tab
Click Options
Click Advanced (Left Pane)
Check out 'Enable Fill Handle and Cell Drag & Drop'.

Fill Data | Chapter 2 | Series | Flash Fill

2019-10-15T12:03:00.000+05:30

Series

We are going to understand in detail series function under 'Fill' option.
Click Fill option under Home Tab.
Click Series

Series Window

Lets understand different type of series with their respective videos.

Linear Type

Lets understand linear series with the help of a video.

Explanations: When we select row space or column space excel automatically adjusts accordingly in 'Series in' option in series window. Observe the series window above.

Growth Type

Each factor is multiplied by 'Step Value'.

Say, if Step Value is 2 and with 4 initial value the series will be 4,8,16,32....

Same as linear type we can limit the series by 'Stop Value'.

Date Type

Lets understand Date Type series with the help of a simple video.

Autofill Type

Again we are going to understand the above type with a video.

Trend Type is similar to Autofill Type but it goes with any trend if there is any. Even it changes the source data to go with the trend.

Justify

Here justify option simply fits or spreads the text in multiple cells only.
In the below display the text is outside the cell. The text is originally in cell G3. Now if we increase the cell width Justify won't help any.

Instead of increasing the cell width if we click Justify under Fill function then the result will be as follows.

Excel will justify a long text and spread it somewhat evenly through out the rows.
Excel after all is not a Word processor.👍👍👍

Flash Fill

Flash Fill is an interesting idea and it originated in Excel 2013.

It works well when the source data is absolutely consistent.

Any inconsistency results in error rather fallacy, and I personally do not advice to work on the basis of this when source data is large.

You are advised to observe this video. This is important.

Want to learn Across Worksheet function under Fill option.
Click here.

Fill Data | Chapter 1 | Across Worksheets

2019-10-12T11:09:00.000+05:30

Click the Fill button.

Cross Hair & White Cell of selection

Cross Hair/ Auto Fill Handle & White Cell

When you hover the mouse on the small Green solid square inside the Red Circle, mouse cursor transforms into '+' this plus sign is often called cross hair or auto fill handle.

Now one can left click and drag the cross hair (with left mouse button pressed) and fill the vertical or horizontal cells with the data that has been selected.

And white cell is the default active cell within the selection.

You can deactivate auto fill handle option.

Click File.
Click Options.
Click 'Advanced'.
Check out 'Enable Fill handle and cell drag & drop.'

With the help of the following video we are going to understand Down, Right, Up and Left and Across Worksheet (Greyed above) function. And also observe how cross hair / auto fill handle is used.

Explanations:

Across Worksheets... function works after the working is carried on a single worksheet. The required sheets can be grouped and the workings can be transferred to the other sheets in the group. So when group is created Across Worksheets function is highlighted.

Instead if we create group of worksheets beforehand and any workings in a single sheet will be automatically posted into other sheets in the group. Say a text or a formula can be transferred. But format wont be transferred. Of course any change of format in a single sheet after the group creation will be contaminated to other worksheets in the group. Try it yourself.

Auto Fill selected Cells

Select the blank cells where you want to fill with a.... say text 'excelintoexcel'.
After selection start writing excelintoexcel and you can see it is written on a cell (Default white) of the selection.
Press Ctrl+Enter
All the cells of the selection is filled with the text excelintoexcel.

Alternatively

You can achieve the same result by dragging with Cross Hair/Auto Fill Handle.

A simple video.

Alternatively

One can go with copy paste too.

Replacing the contents of the cell.

The best way to replace the content of a cell is to activate the cell and start writing the alternate or new content. The cell automatically fills with the new content but the format of the cell will remain and applies to the new entry.

Editing the contents of the cell.

Best way to edit the contents of the cell is to replace with the new one. But if the content is textual say multiple line text.... then

DblClick on the cell and the cell will activate with the cursor starts blinking inside the cell.
Guide the cursor where you want to edit with Navigation keys in the key board.

Alternatively.

Activate the cell.
Press F2 in the key board.
Guide the cursor where you want to edit with Navigation keys in the key board.

Alternatively you can also edit the content in the Formula Bar.

Moving selection inside the sheet.

Sometime it happens that we want to move the workings in a different region in the sheet.

Select the content.
Hover the mouse over the perimeter of the selection.
Mouse cursor head is tagged with a Four Headed Arrow.
At that instance Press and Hold left mouse button start dragging the selection in the required region.
One can see that the content along with format is also shifted to the new region.
Source region is left blank.

Some Data Entry Techniques

After entering a data into a cell when we press enter in the keyboard the cell pointer automatically moves to the next cell down (by default). Now if you want to change this scenario.

Under File tab > click Options > select Advanced
Under Editing Option
Uncheck the related check box under, 'After Pressing Enter, move selection' if one wants to disable this option. Or one can change the direction by selecting the drop down combo box.
Turning it off or on or change the direction is a matter of personal preference.


Marked with Red Box

Instead of pressing Enter after entering data in a cell one can use navigation keys to move to the next desired cell.

Selecting a range of input cells for entering data

One can select a range of cells where the data entry is to be performed. After entering in the default white cell (of selection) if one presses Enter the cell pointer moves to the next cell inside the selection. You can skip a cell by simply pressing Enter again instead of entering anything in the cell. You can revert back to the previous cell by pressing Shift+Enter. This is to remember the cell pointer will remain inside the selection.

Entering decimal points automatically

If one is going to deal with lots of numbers with decimal places. Excel provides an easy approach to handle the situation.
If one specifies 2 decimal places and enters 2587 then excel interprets this entry as 25.87. Very easy and interesting.
By default this option is not enabled.

For enabling it.

Under/Click File Tab.
Click Option.
Click Advanced.
Tick the check box associated with 'Automatically Insert a decimal point'

Automate data entry with auto complete feature.

A very useful feature of Excel.

Easily you can enter same text in multiple cells.

The cells should lie in the same column where the entry is already been made.
There should not be any blank cells in between new entry and previous entry. There could be other entry but no blank cells.
The feature takes into account the lower case or upper case too.

In the new cell if we start typing only the first few characters or alphabets of the text (previously written) excel automatically recognizes your try and give you an option to fill the cell with the previous entry. By pressing enter one can enter the previous entry in the new cell.

It enhances your accuracy of posting. You don't have to misspell.

Say when you enter the text 'excelintoexcel' excel remembers it.
Then when you start typing ex.. etc in the second entry excel provides you an option to fill the cell with 'excelintoexcel'.

You can disable this option of excel

Click File tab.
Click Options.
Click Advanced.
Check out the 'Enable Auto Complete for Cell Values'.

Pivot Table | Chapter 7| Calculated Field Calculated Item

2019-10-10T12:17:00.003+05:30

Calculated Field And Calculated Item

Creating Calculated Field and Calculated Item is bit confusing sometimes.

We deal with Calculated Field and Calculated Item once the Pivot Table is created.

Download the sheet used with calculation.....Here

Calculated Field - A new field created from other fields in the Pivot table. Simply speaking a column is calculated from other columns in Pivot table. After creation it resides in the value area of the pivot table. One cannot shift this field to other areas. Alternatively and much easier is to create a column and add the same to the Pivot Table. But when we deal with any external database, Calculated Field is handy and only option left.

Calculated Item - Similarly a new row is created instead of column with the help of Calculated Item option.This new row resides in the Column area, Filter area and Rows area in the pivot table. And it cannot resides in the value area of the Pivot Table.

The formula we use to create the Calculated Field and Calculated Item is not the standard excel formula. Of course the math and logic is same.

We will understand both with the help of a video.

Explanations:

After ungrouping the OrderDate it is advisable to select the region's cell (say central) before writing the formula in case of Calculated Item. (Though it is not full proof but I find it convenient)

Some important tips on Pivot Table
Pivot Table has some limitations, it is flexible enough but you cannot add new rows or columns or enter formulas within the Pivot Table. It is true you can enter column or row with the help of Calculated Field & Calculated Item.
Again if one wants to manipulate a Pivot Table data one can copy the same and paste else where to manipulate. When Pivot table is pasted else where it detaches itself from the Excel Table. Here Clipboard copying is the best option.
And copied information is not pivot table and it is no longer linked to the source data.

pivot table | Chapter 6 | multiple sheet pivot table

2019-10-07T22:15:00.000+05:30

Today in this chapter we are going to understand how to incorporate multiple sheet's data into a pivot table.

We have already created tables with the data of respective sheets.
Order Date data will bind the three sheets together and acts as a primary key in Database structure.


Order Date with Total


Order Date with Region


Order Date with Representatives

Table names are as follows
Table_Region for region
Table_Rep for representatives
Table_Total for Unit and Total column

Download the worksheet here

Relationships Diagram under 'Manage Data Model'

We are going to understand the entire procedure with the help of a video.

Explanations:
Some time the relationship needs to be deleted.
Steps:

Under the Data Tab.
Click Manage Data Model.
Right click on the tab and click Delete.

pivot table | Chapter 5 | frequency distribution

2019-10-05T08:09:00.001+05:30

we are going to understand how to deal with frequency distribution in pivot table. For reference we are going to use the table below.

Download the table & working here

In simple word we are going to create some range to understand how different figures under the Total column fits in the different ranges. It is called 'Frequency Distribution'. This type of analysis is very useful.

Steps:

Click inside the Data Table.
Under the Insert Tab click Pivot Table.
Select the table range if not selected.
Highlight the radio button for New Worksheet.

click ok.
Drag the Total column under Values Area.
Left Click on 'Sum of Total' (Total Field) under Value Area.
Navigate down to 'Value Field Settings'.
Change the Value Field to 'Count'.
Again drag the Total field to Row area.

Left Click inside the Pivot table on any Total Figure.
Under Analyze Tab click 'Group Selection'.

Starting & Ending figures are already incorporated, based on Highest and lowest. 'By' sets the distribution range. We can change it. But here we will proceed with 100 as default.
Click Ok

Explanations:
Range difference is 100. Count column states the occurrence of different 'Total' values within different ranges.
Say between 519.96 to 619.96 there is only one occurrence. Check Table, i,e, Gill's performance under Central region 539.73 '

Pivot Table | Chapter 4 | Grouping Ungrouping

2019-10-02T21:02:00.000+05:30

Grouping and un-grouping with pivot table data.


Grouping & Ungrouping Buttons

Download the datasheet that we are going to use for demonstration in this post.

Basic Pivot Table from the Datasheet

Explanation:

Region field is placed on rows
Rep (Representatives) are placed on column.
Value total is displayed.

Now lets introduce Order Date On rows.

Order date Grouped

Explanations:

As usual two group fields has generated - 'Quarters' and 'Years'.
Region, Years, Quarters and Order dates are incorporated into Rows area.

Lets ungroup years.

Select any year cell.
Click Ungroup under Analyze tab

Ungrouped into basic order dates

Explanations:

Years has been ungrouped into basic ordering dates.

Now we are going to group dates as per Qtrs and Months and years.

Select any cell with dates.
Click the button Group Selection.
Check the relevant date range.
Highlight Months, Quarters, Years.
Ignored Days else one can highlight Days too.

Grouping window


Grouped

Explanations:

Negative buttons can be clicked to integrate the rows.

We can create our own group with existing groups.

One of the most useful features of Pivot table is the ability to create groups

Above is the default table. Lets Create Group 1 with Central and East Region.

Select Central and East Region (Including years)
Under Analyze Tab click 'Group Selection'.

Explanations:

New Group 1 emerges.
One can ungroup this group.

Timeline

Instead of adjusting filters to deal with dates, one can use Timeline.

Timeline is a filter option that one can use to zoom a particular date/time period in the pivot table. It is handy.

Click inside the pivot table > Under the Analyze tab click Time line

In the instance Timeline will deal with 'Order Date'.

Explanations:

Display name (Order Date) can be changed. Change the Time Line Caption (Ribbon Left).
The Timeline window comprises of two sliders one (colored) to highlight the zoom option that is particular month, year, qtr etc, and another slider helps to navigate the zoom bar.
In the Timeline window there is a zoom bar, here by default it is zoomed in months. If one clicks on the sliding bar at a particular point of the month the Pivot Table will change accordingly.
Again Month option can be changed to Qtrs and so on.
You can change the Timeline styles.
One can reset the Timeline by clicking at the top right corner icon.

Pivot table | Chapter 3 | Value field setting

2019-09-29T09:26:00.000+05:30

This post is the continuation of the immediate previous post.

A Simple data we are going to use in Field settings for Pivot table. You can copy this data in your Excel sheet and try.

Default Pivot table from above data

Field Setting 7

Lets deal with different standard deviation settings.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Summarize values by' tab if not activated.
Highlight the option 'Stddev'
Click Ok

Explanations:

In the Value Field settings four settings are found.
In the Value Field settings in Pivot table these formulas are used namely stddev, stddevp, var, varp
formula '=stddev(56,47)' results in 6.3639.... which is similar what Pivot Table adopts when we select Stddev from the list.
In case of single digit there cannot be any deviation hence #Div/0!
formula '=stddev(58,56,25,65,47)' results in 15.4822479.
Similarly with stddevp and others we can get the tabulated results in the pivot table.
How formula works is outside the purview of this post.

In the next instances we are going to use a different pivot table with little more data.

Download the datasheet


Pivot table from above data.

Field Setting 8

Lets represent the above table on the basis of '% of grand total'.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'
Highlight the option '% of Grand Total'
Click Ok

Explanations:

100% Grand Total is the sum of (6.63+10.97+82.40)%
Similarly 82.40% is the sum of 37.09%+45.30%

Field Setting 9

Lets represent the above table on the basis of '% of column total'.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'
Highlight the option '% of Column Total'
Click Ok

Explanations:

All the column total is represented as 100%
The performance of the representative 'Gill' in the Central region is 56.62% in 2018 and 43.38% in 2019. Say his performance declined in 2019.
Grand total is same as before instance.

Field Setting 10

Lets represent the above table on the basis of '% of row total'.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'
Highlight the option '% of Row Total'
Click Ok

Explanations:

All the row total is represented as 100 percent in the extreme right of the table.
In case of Representative 'Andrews' his total performance on the basis of Central region is 3.44% where as his performance is 6.26% of the total performance in 2019.
Sum total of all rows are 100%.

Field Setting 11

As it is apparent from the default pivot table that the Representative Smith's performance is the highest and best of the lot.

Lets present the above table on the basis of comparison of other representatives with Smith only.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'
Highlight the option '% Of '
Select Base field as Rep (Representative)
Select Base Item as 'Smith'
Click Ok

Explanations:

I find this representation very logical.
If we observe carefully we can see that representative Smith only performed in Central region in 2019. So comparison should be restricted to Central region for 2019.
So Andrews, Gill & Jardine's performances are compared with Smith and represented in percentage format. Others are Null.
Similarly Central region's Sub Total is also compared on the basis of same logic. Only Kivell performance is compared and represented on the basis of Sub Total. Since Kivell and Smith performed in the same region.
Only Smith performance is represented in 100 percent.

Field Setting 12

Lets represent the Pivot table on the basis of percentages of Parent row total.

Here parent row signifies sub total values at the top.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'
Highlight the option '% of Parent Row Total '
Click Ok

Explanations:

Where there are performances the sub total is considered as 100% (all marked with Red)
Say 56.62% and 43.38% together comprises 100%
Grand Total is compared on the basis of 4634.03 data.
Say 6.63% (As active Cell) arrives from dividing 307.37 by 4634.03 multiplied by 100, similarly 10.97% and 82.40% arrives in the same manner.

Field Setting 13

Lets represent the Default Pivot table data as 'Percentages of Parent Column Total'.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'
Highlight the option '% of Parent Column Total '
Click Ok

Explanations:

All the cells of the Grand Total column is marked as 100%
Performances are measured on the basis of Grand Total Value.
All the percentages total of any row is 100%
Even total of Grand Total row is 100%
All row totals are 100%

Field setting 14

Lets represent the pivot data as percentage of parent total.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'
Highlight the option '% of Parent Total'
Select the Region as Base Field
Click Ok

Explanations:

All the parents (Region totals for representatives) are presented as 100%.
Data are represented in percentage.

Field Setting 15

Lets represent all the representatives performance data as a difference from representative Andrews.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'.
Highlight the option 'Difference From'.
Select the 'Rep' as Base Field.
Select 'Andrews' as Base Item.
Click Ok

Explanations:

Andrews has performed in Central region only. And he was the worst performer.
There is no subtotal for Andrews in the Central Region.
Gill's difference is (953.27-131.34) 821.93 similarly others are projected in the subtotal zone.
Negative values has been rejected.

Field Settings 16

Lets represent all the representatives performance data as a difference from representative Andrews in percentage format.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'.
Highlight the option '% Difference From'.
Select the 'Rep' as Base Field.
Select 'Andrews' as Base Item.
Click Ok

Explanations:

Here Gill's difference in percentage format is 625.80% that is 821.93 (observe field setting 15) divided by 131.34 (Andrew's Performance) and multiplied by 100. That is 821.93/131.34*100 result is 625.80%

Field Settings 17

Lets represent all the representatives performance in running total mode.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'.
Highlight the option 'Running Total in'.
Select the 'Rep' as Base Field.
Click Ok

Explanations:

Grand total column is hidden.
Running total is total is easy presentation.
Say in Central 131.34, 131.34+953.27=1084.61,1084.61+429.14=1513.75 so on

Field Settings 18

Lets represent all the representative's performance in running total mode and in percentage format.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'.
Highlight the option '% Running Total in'.
Select the 'Rep' as Base Field.
Click Ok

Explanations:

Observe the previous table under the head Field Settings 17.
In the Central region Andrews performance is 3.44% - is the percentage of 131.34 of 3818.25. Similarly 28.41% - is the percentage of 1084.61 of 3818.25 and so on.

Field Settings 19

Lets rank the performance of representatives from Smallest to Largest rather worst to best.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'.
Highlight the option 'Rank Smallest to Largest'.
Select the 'Rep' as Base Field.
Click Ok

Explanations:

Andrews is the worst performer ranked as One (since smallest to largest) under grand total.
Smith is the best performer ranked as Seven (since smallest to largest) under grand total.

Field Setting 20

Lets rank the performance of representatives from largest to smallest rather best to worst.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'.
Highlight the option 'Rank Largest to Smallest'.
Select the 'Rep' as Base Field.
Click Ok

Explanations:

Representative Smith is the best performer and ranked as one in grand total.
Representative Andrews is the worst performer of all and ranked as seventh in grand total row.

Field Setting 21

Index setting

Calculation is as follows

(Value in Cells X Grandtotal of Grand Totals)/(Grand Row Total X Grand Column Total)

Click inside the pivot table and activate Analyze tab.
Click 'Field settings', activate 'Show values as' tab if not activated.
From the drop down menu 'Show values as'.
Highlight the option 'Index'.
Click Ok

Calculations for

1.213652851 is (131.34 X 4634.03)/(3818.25 X 131.34)
2.207332641 is (131.34 X 4634.03)/(2099.38 X 131.34)
If all value in the pivot table are equal each index will be 1
If an index less than 1 it signifies that it is less important.
If an index more than 1 it signifies that it is more important.
Decimal places can be reduced.

Pivot Table | Chapter 2| Modifying pivot table

2019-09-26T11:07:00.003+05:30

This post is the continuation of immediate previous post...
One can download the data sheet (we are going to follow) from the previous post.

Instance 4

Query: Representation of region wise sales of Rep (Representatives) on the basis of Date.

Pivot Table With Date field

As we can see the Grand total is same as before.
The check box associated with Order date is checked.
Fields like 'Quarters' and 'Years' are generated automatically.
Under Row area the fields like 'Years', 'Quarters' and 'Order Date' has been placed.

Another important thing can be noticed in the pivot table. Some +/- signs are visible in the table. Double clicking the + sign further disintegrates the data and table shows more precise information.

Modifying Pivot Table

The above table is without any subtotals.

Modify 1

Lets introduce sub totals.

Click inside the Pivot table, Design tab activates.
Click Subtotals (Extreme Left)
Click 'Show all subtotals at the top of the group.

Sub Totals introduced at the top of the group

Explanation: 19627.88 is the sum total of 11139.07, 6002.09 and 2486.72. Similarly instead of pasting sub totals on the top you can paste it at the bottom of the groups. And you can also disable sub total.

Modify 2

Lets remove Grand Totals for Row and Column

Click inside the pivot table.
Under Design tab, click Grand Totals
Click 'Off for Rows and Columns'

Modify 3

By default all pivot tables are in Compact form.

Reporting layout can be changed.

Tabular Form

Click inside the pivot table.
Under the Design Tab click Report Layout.
Click 'Show in Tabular Form'.

This format is useful.

Modify 4

Just to enhance the readability of the report we can introduce blank rows after each records.

Click inside the pivot table.
Under Design tab click Blank Rows.
Click 'Insert Blank line after each item'.

Other modifications

Row headers, Column Headers, Banded rows, Banded columns and Pivot styles are self explanatory. One can use those at his or her
discretion.

Field Settings

We are about to dive deep into the pivot table's advanced features and functions.

One can get the dialogue box of Field Settings by right click on the pivot table and click 'Value Field Settings'.

With Field Settings we can modify the displayed data in the pivot table, of course without altering the source data.

For clear understanding we are going to use simple data. Logic is important.

Field Setting dialogue Box

The Data is in Table Format
In the Day field 'Monday' is repeated twice.

Default pivot table with Grand total of Total

Field Setting 1

Count the number of Data

Click inside the pivot table and activate Analyze tab.
Click 'Field settings'.
Activate 'Summarize values by' tab if not activated.
Highlight the option 'Count'
Click Ok


Total Record Count

Explanation:

Monday is 2 (since repeated twice)
Grand total 5

Field Setting 2

Find the average of all records. (This is bit tricky)

Click inside the pivot table and activate Analyze tab.
Click 'Field settings'.
Activate 'Summarize values by' tab if not activated.
Highlight the option 'Average'
Click Ok


Field settings Average

Field Setting 3

Lets find the Maximum out of the field

Click inside the pivot table and activate Analyze tab.
Click 'Field settings'.
Activate 'Summarize values by' tab if not activated.
Highlight the option 'Max'
Click Ok


Field setting Max

Explanations:

As per 'monday' record 56 is max
As per total record 65 is chosen as the maximum of all.
Row label is changed to 'Max of Total'.

Field Setting 4

Finding the Minimum Value is similar to maximum value, rather the result is just the opposite.

Field Setting 5

Lets find the product of all.

Click inside the pivot table and activate Analyze tab.
Click 'Field settings'.
Activate 'Summarize values by' tab if not activated.
Highlight the option 'Product'
Click Ok


Field setting Product

Explanations:

For Monday record the product of 56x47 is displayed.
Grand total is the product of all records including 2632.

Field setting 6

Count the number of records.

For these data source 'Count' and 'Count numbers' are same.

Requires here no further explanation.

Pivot table | chapter 1 | Default Pivot Tables

2019-09-24T08:52:00.001+05:30

Pivot Table

Pivot table is one of the most important feature of Microsoft Excel.
Endless rows and columns can be presented in a meaningful format.
Pivot table is the answer of endless queries. It is highly interactive, even after creation and presentation one can change it again.

Its a dynamic summary report from a database.

A simple shortcoming is, it cannot update itself automatically if there is a change in the source data.

A simple Pivot table set up

Download the sample data that we are going to deal with in this topic. click here

I think it is always better to convert the data source in excel table format first before arranging pivot table from the data source.
Excel will automatically update the pivot table in case a new data is introduced in the table.

In Pivot Table your source data should be in a rectangular database format. Informations that are available in the source should be of two types.
Data (Say Total)
Data Category (Central Regions Total, even Smith performance total)

Default Pivot Tables

Instead of creating of pivot table from scratch one can select from some default styles provided in Excel. Under 'InsertTab' click 'Recommended Pivot Tables'.

Excels provides some ready made Pivot Tables for your use. Intelligently it recognizes the data, one is dealing with.

Select the entire Data range / click inside the range.
Click Recommended Pivot Tables.

Recommended Pivot Tables

From here even you can start your own Pivot Table by clicking Blank Pivot table button.
The table above contains 7 columns.

Order Date
Region
Representative (Rep)
Item
Units
Unit Cost
Total (Unit x Unit Cost)

Highlight any option and click Ok the pivot table will be placed in a new sheet with Pivot Table fields.
Even you can start your own pivot table from this option.
Say we select 'Sum of total of Region' from the list


Sum Total of Regions

By default the table is placed in a new sheet.You can see the accuracy of the table, Grand Total confirms the sum of Total column.

Lets modify the pivot table at your discretion.

This is to remember that one should start with a query for the presentation. There are innumerable queries, we will discuss few in this respect. One should practice with his own data set.

Laying out Pivot table

Fields in the 'Pivot Table Fields' window can be dragged in four spaces (Filter, Column, Row and Values). Say field 'Region' can be dragged in the Row area and field 'Total' can be dragged into the Value Space. Again if you want to remove any field from the above 4 spaces just drag out the field from the space. Or left click on the field on any of the 4 spaces, click 'Remove Field'.

Instance 1

Query: Presenting how Representatives performed in different regions.

Click the check box relevant to Rep in Pivot Table Fields Task Pane.

A new tab 'Analyze' has been generated in the above ribbon. It is activated if we click anywhere inside the pivot table. As you can see in the above display a new field 'Rep' has been incorporated under Row option.

Instance 2

Query: Presenting how Representatives perform item wise in different regions.

Click the check box associated with Item

Lets again modify the presentation. Drag and drop the 'Item' in the Column field. We will see that the presentation will be much more concise, dynamic and meaningful.

Lets analyze the presentation a bit. Morgan in the Central region sold Binder, Pen set and Pencil to the tune of Rupees 251.72, 686.95 and 449.10 respectively totaling 1387.77.
Revenue generated in the Central region from Binder is rupees 5762.63. Total revenue generated from Binder is Rupees 9577.65
There is no sale of Desk in the Eastern Region and no sales of Pen Set in the Western Region.
So we see how a simple range of data evolves into a much meaningful information.

We can modify the presentation again. Remove the Rep from the Columns area and put the field in Filter area.

Item field is shifted at the top (Marked with Red). We are going to filter the pivot table according to item.
Pivot table displayed the consolidated figure of total sales generated as (All) is displayed above.

Lets represent the region wise 'Pen Set' sales generated by representatives.

If we observe the previous display total Pen Set sales (4169.87) is still the same, but table represent it in a different manner.

You can select multiple items from the drop down box.

Instance 3

Query: Representation of Number of units sold item wise by different representatives in the 3 regions.


Pen set sales in different regions by representatives.

Explanations:

Item field has been placed under Filter area.
Pivot table fields display window has been removed deliberately to fit the table.
Total Pen set sales is same as before 4169.87
No Pen set sales has been generated in Western Region
Total sales generated by Kivell is 138 but in different dates that 42 & 96 that is why represented differently.
Pen set sales in Central is 2421.39 & in East is 1748.48

Again with Multiple items say Pen & Pen Set the table is big.

Explanations:

Units (B4 to L4) here represents consolidated sales figure of Pen & Pen set.

Emphasis is on the Representatives whose performance is measured in respect of two items. That is why consolidated figure of Pen & Pen Set is shown.