How to create a histogram in Excel 2013

A histogram is an excellent tool used in project management and many other disciplines to analyze a collection of data.

A histogram is a bar graph that shows how frequently data occur within specified ranges or intervals. The height of each bar gives the frequency in the respective interval.

Excel 2013 has Data Analysis tool which helps to create a frequency distribution from the given data and a histogram chart. Data Analysis tool is available in "Data" tab, but sometimes we need to manually add the tool.

Read more: How to load the Data Analysis tool in Excel 2013

Steps to create histogram using Data Analysis tool in Excel 2013:

1. The first step is to specify intervals (bins) within which we want to classify the data. Enter the bin numbers (only upper levels) in separate column.
2. Go to "Data" tab >>> Data Analysis >>> Select "Histogram" >>> Click OK
3. Once the Histogram dialog box opens, enter the below detail:
   • Input range: select the data for which histogram is required
   • Bin Range: select the specified intervals (bins)
4. Select appropriate output options and click OK

Remember: Check "Chart Output" to get the histogram chart along with frequency distribution.

Customize the formatting of histogram

a) Label your bins properly to show intervals

b) Remove the spacing between the bars

Right click a bar >>> Format Data Series >> Series Options >>> Gap Width >>> change to 0%

c) Add border to the bars with appropriate color

Right click a bar >>> Format Data Series >> Border color >>> Solid line >>> Color

How to transpose rows and columns in Excel 2013

Download Excel File

TRANSPOSE literally means changing the orientation of a cell range or an array.  Thus, TRANSPOSE enables to swap data from rows into columns and vice-versa.

We can transpose the data in Excel 2013 in two ways:
1. Static Transpose – using "Paste Special"
2. Dynamic Transpose – using TRANSPOSE function

Static Transpose using "Paste Special" function

This method is very useful when we have to quickly transpose data without changing anything in the future. The output data is STATIC i.e. it does not have any linking or formula attached to it.

Following steps will be helpful for static transpose using paste special option:

1. Select the cell range that you want to transpose and copy the same (Ctrl+C)

2. Select the cell where you want the output to start

3. Go to "Home" tab >>> "Paste" button >>> Paste Special >>>Transpose

OR  Right click >>> Paste Special >>> Transpose
Shortcut: Ctrl+Alt+V >>E  OR Alt+ESE

4. Press OK and its done. The transposed data will get pasted starting from the selected cell.

Dynamic Transpose using TRANSPOSE function

TRANSPOSE function gives the output with linking. Hence, if the original data altered, the transposed data will reflect the change automatically.

TRANSPOSE function is an "Array" function i.e. it takes cell range as input (quite logical..!!) and needs combination of "Ctrl + Shift + Enter" keys to insert. Array functions are enclosed by curly braces {}.

One critical difference between Paste Special and TRANSPOSE function:

For using TRANSPOSE function the output range should be selected first and it should be an appropriate array/cell range i.e. if the input range is 2x3 cell range, the output range selected should be 3x2 cell range.

1. Select the output cell range where the transposed data is required. You can watch the selection matrix in the "Name Box".

2. Keep the cell range selected and type below formula:

       =TRANSPOSE (input cell range)

       "input cell range" is the input data range which we want to transpose

3. Press "Ctrl + Shift + Enter" after entering the formula. This will automatically enclose formula with curly braces {}.

Important Note: Once the TRANSPOSE function is applied, you cannot edit or delete any of the individual cells in the output range.

EXCEL TUTORIALS – ALL EXAMPLES

Tutorial #01   How to split first and last names in Excel 2013

Download Excel

How to split first and last names in Excel 2013

Download Excel File

Conventionally, the "convert text to columns" wizard was generally used to split first name and last name in earlier versions of Excel.

A new feature called "Flash Fill" has been introduced in Excel 2013 to perform such task. Flash Fill detects a pattern in your initial data entry which enables it to figure out remaining data you want to enter

The main advantage of using Flash Fill is that there is no need of any formula, wizard or any kind of construct to get the desired output.

Suppose we have full name of the person in column A. We are planning to split this name as below:
First name – Column B
Last name – Column C

Following steps will help to split first and last names into separate columns:

1. Enter the first name in cell B1 and press ENTER
2. Start typing the next one and instantly the "Flash Fill" feature will show the remaining list of first names
3. Hit ENTER to accept the suggested list OR press "Esc" to continue typing remaining names
4. In the similar way, initiate to type Last name in cell C1 and press ENTER
5. Again, Flash Fill will give suggested entry once you initiate typing in second entry
6. Press ENTER to accept suggestion from Flash Fill and it's done

Important note: Flash Fill feature is case sensitive. If you type lower case for initial entry, all the suggested entries will be in the same case. This is as such one more advantage over using "convert text to columns" wizard.

Bonus tip: If Flash Fill is turned on but doesn't start automatically when you type data that matches a pattern, you can try starting it manually by clicking Data >>> Flash Fill, or by pressing Ctrl+E.

Two-way lookup in Excel 2013 using INDEX and MATCH

Two-way lookup gives value of the intersection of desired row and column values in a table containing data.

For example, suppose we have data as per table below, and we are looking for number of bank transactions of David in the month of August. This requirement is appropriate to go for 2 way lookup.

i.e. We are looking up value at intersection of desired customer name row (David) and desired month column (August)

To lookup the desired name of customer (e.g. David), we will use MATCH function. This function returns the relative position of particular value in the given cell range or an array.

Syntax for MATCH function:

MATCH (lookup_value, lookup_array, [match_type]

lookup_value: the value whose relative position is required

lookup_range: the range in which the relative position is to be found

[match_type]: put "0" for exact match

Thus, we will get relative row number of the customer by using MATCH function.

Similarly, to get relative column position of desired month we will use MATCH function.

Now, we have relative position of both row as well as column in the data table. To lookup the output value we will now use INDEX function.

Remember, the INDEX function gives ouput corresponding to given relative row number and column number in the data table.

Syntax for INDEX function:

INDEX (array, [row_num], [col_num])

array: the defined range of cells or an array to use in the lookup (basically the table with data)

[row_num]: relative row number in the defined range of cells

[col_num]: relative column number in the defined range of cells

Please note that "array" would not contain the row and column headings.

Thus, now the story is pretty clear. We need to feed the MATCH function used to fetch relative row number in place of [row_num] in INDEX function. Similarly, feed the MATCH function for relative column number in [col_num].

The final formula will look something like this: