How to sort horizontally (by row) in Excel 2013

Generally, we sort the data in Excel by values in one or more columns i.e. using top to bottom values in particular column. However, sometimes we have different arrangement of data in Excel 2013 and might need to sort horizontally i.e. using left to right values in particular row.

Below is the quick way to sort horizontally in Excel 2013. This trick works fine in Excel 2007 and Excel 2010 as well.

Consider the sample data below and say we want to sort the data horizontally by values of "Number of employees" given in Row 8 (say smallest to largest)

1. Select the range of data which you want to sort horizontally. Generally we select the entire data (except headers) since other rows are interlinked with given row that we need to sort

2. Go to "DATA" tab >>> Click "Sort"  [Shortcut: Alt + ASS]

Note: If the data contains headers check "My data has headers" check-box. In our case, we have not selected the headers and hence we will keep it unchecked.

3. Now the most important step: Click on "Options…" >>> Choose "Sort left to right" >>> Click OK    

4. Select the row on the basis of which we want to sort the data (here Row 8 i.e. Number of Employees) and choose order "Smallest to Largest". Click OK.

This will give you the data sorted horizontally by row and the sorted data will look as below in Excel:

Remember: We had selected the entire data range although the sorting was done based on one row. This will ensure that corresponding data after sorting remain intact without any distortion.

More interesting articles on this website:

• How to find and highlight cells with formula in Excel 2013
• How to highlight alternate rows in Excel 2013
• How to create Histogram in Excel 2013
• How to create Waterfall Chart in Excel 2013

How to highlight alternate rows in Excel 2013

Highlighting alternate rows in Excel 2013 worksheet makes the data visually much more appealing. Instead of doing this manually, we can use Conditional Formatting feature to automatically shade every other row in Excel.

Conditional Formatting gives us flexibility to change the shade colour in case it is required. The biggest advantage of using Conditional Formatting is that the shading is dynamic i.e. it automatically updates if we insert or delete the rows.

Also read: How to Trace Precedents and Dependents in Excel 2013

Below steps will help you highlight alternate rows:

1. Select the data range where we want to highlight alternate rows.
   

2. Go to "HOME" tab >>> Conditional Formatting >>> Click "New Rule..."
   

3. This will open conditional formatting dialog box. Click "Use a formula to determine which cells to format"

4. Enter the formula =MOD(ROW(), 2)
   

5. Click on "Format" button and choose the shade or format for alternate rows
   

6. Click OK >> OK and you will see that alternate rows in selected data range are shaded as per desired format (here with GREEN fill)
   

More interesting articles on this website:

• How to use AGGREGATE function in Excel 2013
• Two Way lookup in Excel 2013 using INDEX and MATCH
• How to create Histogram in Excel 2013
• How to create Waterfall Chart in Excel 2013

How to find and highlight cells with formula in Excel 2013

Excel 2013 has introduced some awesome new functions that can help professionals save time.

Some of the amazing new functions include: DAYS, IFNA, ISFORMULA, XOR, ISOWEEKNUM and many more.

Also read: Top 7 new features in Excel 2013 that every professionals must know

The new ISFORMULA function detects if the cells contains formula. We can use CONDITIONAL FORMATTING along with ISFORMULA function to identify and highlight cells containing formula.

Syntax: ISFORMULA (reference)

• Reference can be a cell reference, a formula, or even a name that refers to a cell.

Suppose we have data-set as shown below, with expense occurred in each month and column H have formula for total expenses.

Follow the below steps to identify and highlight cells with formula:

1. Select cell range A4:H4 and keep A4 as active cell
   
2. Go to "HOME" tab >>> Conditional Formatting >>> Click "New Rule..."
   
3. This will open conditional formatting dialog box. Click "Use a formula to determine which cells to format"
4. Enter ISFORMULA function giving reference to active cell (here A4 cell) =ISFORMULA(A4)

   

   
   
5. Click on "Format" button and choose the format for the cells with formula
   
6. Click OK >> OK and you will see that all cells with formula are coloured with desired format (here with GREEN fill)
   

More interesting articles on this website:

• How to create Two-Variable Data Table in Excel 2013
• Two Way lookup in Excel 2013 using INDEX and MATCH
• How to freeze panes in Excel 2013
• How to create Waterfall Chart in Excel 2013

Top 7 new features in Excel 2013 that every Professional must know

Flash Fill feature
"Flash Fill" is the magical feature introduced in Excel 2013

Flash Fill detects a pattern in your initial data entry which enables it to figure out remaining data you want to enter.

For example, combining first names and last names from different columns is detected by Flash Fill and applied across multiple rows based on pattern detection.

Also read: How to split first and last names in Excel 2013 using Flash Fill 

Chart Recommendations

Now this is pro-active feature launched by Excel 2013!! Based on the selected data, Excel recommends appropriate chart which makes it more intuitive.

Follow below steps to use this feature:

1. Select the data >>> Go to "INSERT" tab >>> select "Recommended Charts"
   
2. This will open a dialog with range of charts. The interesting part is we can click on each chart and see the preview of that chart.
   
3. Choose the desired chart and hit OK. Your chart is ready!!

Quick analysis Tool

This is again an excellent tool introduced in Excel 2013 to preview the data in insightful ways.

In order to use this tool, select the data you want to analyze and instantly the Quick Analysis icon appears at the bottom-right corner. This tool has lot of design and formatting options which is worth exploring. Additionally, it provides preview before actually applying any option.

Extensive Cloud support with SkyDrive

Excel 2013 allows users to share their Excel workbooks on SkyDrive. This enables quick and easy access to your Excel files on any device running Excel 2013 (which can include your windows tablet, smartphone, desktop or laptop).

Also, with Office 365 subscription one can review and edit workbooks online using almost any web browser.

Sharing on Social Networks

This is an innovative feature introduced keeping in mind the growing use of Social Networks. 

To share Excel files on Social Networks follow below steps:

1. Go to "FILE" >>> Share >>> Post to Social Networks
2. Select any social network that is linked with your Office 2013 account
3. Select privacy options of read-only or edit for the shared worksheet
4. Include any message in the message box and press "POST" button

Pivot Tables Recommendation

Pivot Tables helps immensely for data analysis and managerial decisions, but most of us usually face difficultly in effectively using it. Excel 2013 has almost solved this problem by introducing "Recommended Pivot Tables" feature.

Again, it’s extremely easy to use it:

Click inside data range >>> Go to "INSERT" tab >>> Recommended PivotTables

This will open a dialog box showing the list of recommended pivot tables along with explanations.

Select the table which suits your output and press OK. It's done!!

Introduction of great new functions

What's more!! Excel 2013 has introduced some awesome functions that can help professionals save time.

Some of the amazing new functions include:

• DAYS – returns the number of days between two dates
• IFNA – returns specific value if the formula gives #N/A error
• ISFORMULA – detects the cells containing formula
• XOR – detects expressions and returns FALSE only when all expressions are FALSE
• ISOWEEKNUM – returns ISO week for a date

Please comment, SHARE or LIKE if you enjoyed this article!! Visit Facebook page: FundooExcel

More interesting articles on this website:

• Text Alignment keyboard shortcuts for Excel 2013
• How to freeze panes in Excel 2013
• How to generate RANDOM number in Excel 2013
• How to create two-variable Data Table in Excel 2013

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 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)

How to count blank or empty cells in Excel 2013 using COUNTBLANK

The COUNTBLANK function is used to count empty cells in Excel 2013

Syntax:
=COUNTBLANK(range)

Arguments:
The range can be cell references or can be directly entered.

Follow the below steps to count cells that are not empty:

1. Enter the data in the worksheet

2. Select any cell where you want to get the count of blank or empty cells

3. Enter a COUNTBLANK formula and select the required cell range as argument

4. Press the Enter key.

This will give the count of the empty cells in the selected range

Note: The COUNTBLANK function will NOT count cells with formulas (even if they were value pasted later) and spaces

For example, formula like =IF(A1="","", "na") may have inherent "" value to signify empty cell and it may appear to be blank, but COUNTBLANK function will exclude this in its count.

Add-on:
To check if the cell contains any character (which looks empty), you can use =LEN(cell) function. This should ideally give 0 if the cell is empty.

However, the "" (often after value pasting the formula) does not get detected, to see this we need to check mark "Transition navigation keys"

File >>> Options >>> Advanced >>> Lotus Compatibility

How to highlight Duplicates in Excel 2013

Is there an easy way to locate and highlight duplicates in a list in Excel?

Yes, Excel has many powerful tools to locate and highlight duplicates in a list or data range. Conditional formatting is one of the tool with which we can highlight duplicates.

Let's learn the process of highlighting duplicates with the help of an example:

1. Select the cell range where you want to find and highlight duplicates

2. Use the Conditional Formatting tool located in "Home" tab of the ribbon as mentioned below:

Conditional Formatting >>> Highlight Cells Rules >>> Duplicate Values [Shortcut: Alt+HLHD]

This will activate the "Duplicate Values" dialog box.

3. Select "Duplicate" from the dropdown in the dialog box and also select relevant highlight option

 To apply any customized format you can select "Custom Format..." from the dropdown list. The preview of format will appear while you are selecting from the dropdown.

4. Click "OK"

This will apply the desired format to duplicate values in the selected range.

IMPORTANT NOTES:

• The feature is NOT case sensitive.
• Excel will recognize the cells as duplicate only if both cells contains exactly same data.
• In case any extra spacings are present in any one of the cells, Excel will not recognize them as duplicates (If both cells have same extra spacings, then it will recognize as duplicates!!)

Creating drop down list in Excel 2013 using data validation

Validating your data while entering data into the cell helps you to prevent unnecessary/invalid data in the input cell. Excel 2013 provides great tools to ensure the validation of input data in a given cell.

Drop-down lists or combo boxes helps in picking up a value from a valid list to enter in a cell.

Let's learn to create drop down list in Excel with the help of an example:

• Creating a drop down menu list using Data Validation
• Inserting an "Input message" tab using Data Validation
• Inserting an "Error alert" tab using Data Validation

1. Create a separate list of data items which we need for the drop-down

2. Select a cell where the drop down list is required

Go to:  “DATA” tab in ribbon >> Data Validation button >>> Data Validation [Shortcut: Alt+AVV]

3. In the Data Validation dialog box, go to settings and select "List" from the drop down menu

Ensure that "In-cell dropdown"  button is checked. Also, check "Ignore blank" if you want to allow blank entries.

4. You can manually enter the range which you require in drop-down list OR can give cell reference in the "Source" field

5. Press "OK"

Inserting an "Input message" tab using Data Validation

The input message provides the information regarding the valid input data to the user when the cell containing the drop-down is selected.

• In the Data Validation dialog box, go to "Input Message" tab
• Enter the required title and input message that you want to populate (ensure that "Show input message when cell is selected" is checked)

Inserting an "Error alert" tab using Data Validation

Error alert populates when any invalid data is entered into the cell by the user.

• In the Data Validation dialog box, go to "Error Alert" tab
• Select the error symbol from "Style" drop-down
• Enter the required title and error message that you want to populate when error occurs (when the data entered in the input cell is different from the validation list)