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:

How to insert current date and time in a cell in Excel 2013

Download Excel File

When we work in Excel, we sometimes require to enter current date or time. The purpose may be to maintain log of activities or tracking some changes.

There are mainly two methods to insert current date & time in a cell in Excel 2013:     

1. Dynamic – A date or time that updates when the worksheet is recalculated
2. Static – A value in worksheet that doesn't change

Inserting "DYNAMIC" date and time

TODAY and NOW functions are generally used to insert dynamic current date and time respectively.

Syntax for the TODAY function is:

=TODAY()

NOW function is used to insert "current time" which is dynamic

Syntax for NOW function is very similar to TODAY:

=NOW()

Important Note: As you might have noticed, these functions have no arguments i.e. () does not contain anything. As a result, the data updates each time the worksheet is recalculated.

Inserting "STATIC" date and time

Select the cell into which you want to insert current date or time

To insert current date >>> Press CTRL + semi-colon (;)

To insert current time >>> Press CTRL + SHIFT + semi-colon (;)

To insert both date and time >>> Press CTRL + semi-colon (;), then press SPACE and then CTRL + SHIFT + semi-colon (;)

Important Note: In both the above cases, Excel will give date format as per the default settings. You can change the format from cell format menu [Shortcut: "Ctrl+1"] in "Number" tab >> Date

How to change name in cell comments in Excel 2013

Inserting cell comment
Quick recap on the process of adding a cell comment
Select cell >>> Right click >>> Click "Insert Comment"

After adding the cell comment, if we hover over the cell:

1. The cell comment is visible with the name of the Author on the top of comment box
2. Name also appears in the status bar at the bottom of the Excel

Sometimes its good to change the display name on the cell comment. Imagine you are doing some work on behalf of your boss and copy is going to the client!!

Continue reading, to see how you can quickly change the display name in the comments:

1. Click on the "File" tab and click "Options"
2. Select "General" category in the same
3. Under the heading "Personalize your copy of Microsoft Office" you can see "User name"
4. Edit the box besides "User name" to add desired Author name
5. Press OK and it's done.

You can try inserting a new comment and see that the author name has changed.

Important note: The existing cells with cell comment before changing username will have no effect and will have the same author name as before.

How to center headings without merging cells in Excel 2013

Merged cells are annoying at times and create many problems while using various functions. The best practice suggests that merged cells should be avoided as far as possible.

Common issues with merged cells:

• Tools such as pivot tables, data tables etc. are difficult to use with merged cells
• Sorting and filtering the data is difficult
• Using various commonly used excel functions like SUMIF becomes difficult

General motivation behind using merged cells is to create multi-column headings.

Let's see an alternative approach to create multi-column headings without merging cells in Excel 2013.

Enter the heading on the top of one of the columns

To center the given heading over the rest of the adjacent columns:

1. Select the cells till you want to center heading

2. Go to "Alignment settings" button on the Ribbon

3. Under "Horizontal" >>> Click drop down arrow >>> click "Center Across Selection"

4. Click OK.

Visually, this does exactly the same as Merge & Center but importantly, it does not merge the cells.

Alternatively, using shortcut buttons:

1. Type "Ctrl+1" to open "Format Cells" dialog box
2. Press "A" to go to "Alignment" tab
3. Press "Alt+H" for "Horizontal" drop down
4. Select "Center Across Selection"
5. Press OK.

Shortcut summary: Ctrl+1 >>> A >>> Alt+H >>> Center Across Selection >>> OK

Complete the Formatting part

You can now format the headings with common outside border and apply different fill colors to it.

How to password protect workbook in Excel 2013

There are primarily two levels of password security you can apply to workbook in Excel 2013:

1. Protection for structure
2. Protection for Windows

Protection for Structure

This feature disables workbook structure features like insert, delete, rename, move, copy, hide or unhide

To protect workbook for structure

Open workbook >>> Go to "Review" tab >>> Click "Protect Workbook"
[Keyboard shortcut: Alt +RPW]

Click on "structure" and enter desired password. Press OK.

Then re-enter the password to confirm the same. Press OK.

Protection for Windows

This feature protects the workbook windows i.e. one cannot move or change size or close windows

The process to enable this is very similar to protecting the structure.

Open workbook >>> Go to "Review" tab >>> Click "Protect Workbook"

Click on "Windows" and enter desired password. Press OK.

Then re-enter the password to confirm the same. Press OK.

Unprotecting workbook

To unprotect the workbook, click on Protect Workbook again (on "Review" tab) and enter correct password.

How to sort by color in excel 2013

Follow the steps below to sort data by colour:

1. Select any single cell inside the data
2. Go to "Data" tab >>> Click "Sort" (Shortcut: Alt+DS)
3. Select the below in the dialog box:

    Sort by: Name (the column used to sort)

    Sort on: Cell Color (the order of color)

4. Click "Copy Level" for two times to sort with the remaining colors
5. You will get the sorted output based on sequence of colors selected on clicking OK

How to compare two lists of data in Excel 2013

Suppose we have to compare two lists of data as given below. Basically we want to find which elements in first list is not present in second list and vice versa.

To highlight the data which is not covered in other list follow below steps:

1. Select first data range A2:A17
2. Go to "Home" tab >>> Conditional Formatting >>> Click "New Rule…"
3. Select the option "Use a formula to determine which cells to format"
4. Enter the formula =COUNTIF ($B$2:$B$25, A2) = 0
5. Go to "Format...” to select required highlighting format and click OK

Explanation: The COUNTIF function applied here will count all the instances of A2 in entire second list, if the same is not found, the count will be zero and hence the entry will be highlighted. Similarly, it will check for all the instances in first list.

Similarly, we can highlight cells in second list which are not in the first list by following steps:

1. Select second data range B2:B25
2. Go to "Home" tab >>> Conditional Formatting >>> Click "New Rule…"
3. Select the option "Use a formula to determine which cells to format"
4. Enter the formula =COUNTIF ($A$2:$A$17, B2) = 0
5. Go to "Format...” to select required highlighting format and click OK

How to use RANK function in Excel 2013

The RANK function returns the rank of a number, i.e. its position within a list of numbers in Excel 2013. We will also look into RANK.EQ and RANK.AVG functions which are introduced in Excel 2013.

Syntax: RANK (number, ref, [order])

• Number: the number whose rank needs to be found
• Ref: list of numbers from which the rank is intended to be found
• Order: (Optional) specifies whether to rank in ascending or descending order

If order = 0 (or omitted) >>> Excel ranks number as if the list was sorted in descending order

If order is any nonzero value >>> Excel ranks number as if the list was sorted in ascending order

Note: For "Ref", any non-numerical value or reference would be automatically ignored by Excel 

Caution: Make sure to freeze the "Ref" if you want to drag the cells to Rank multiple values

Important note: In case of ties (i.e. in case more than one value has same rank), this function returns same rank and skips subsequent rank.

E.g. If two values have rank 2, then the next entry will have rank as 4 (rank 3 will be skipped and two entries will be given rank 2)

Excel 2013 has two separate functions for RANK

1. RANK.EQ – This returns same rank and skips subsequent rank as mentioned above.
2. RANK.AVG – This takes average of ranks and subsequently gives average rank to all ties (e.g. ties at rank 2 and 3 will give rank as 2.5)