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 create waterfall chart in Excel 2013

Waterfall chart (or Bridge chart) is great tool to analyze how an initial value is affected by series of intermediate positive or negative values.

Generally, the initial and final values are represented by whole columns, while the intermediate values shown by floating columns. Again, let’s show positive values with GREEN bars and negative values with RED bars.

For example, we take data below to create waterfall chart in Excel 2013:

To create a simple waterfall chart in Excel 2013 follow below steps:

DATA ARRANGEMENT

Insert additional columns between the given data. Here we will add 4 columns >>> "Ends", "Rise", "Fall", "Hide"

Ends – used to get the columns pertaining to first and last values

Rise – positive values contributing to increase in initial value

Fall – negative values (absolute) contributing to decrease in initial value

Hide – calculates values required to get starting point for Rise or Fall

INSERTING FORMULAS

Cell B2: =F2 (initial)

Cell D3: =MAX(F3, 0)     >>>>    Drag this formula to D8 (don't include FINAL row)

Cell E3: =MAX(-F3, 0)    >>>>    Drag this formula to E8 (don't include FINAL row)

Cell C3: =B2+C2+D2-E3   >>>>    Drag this formula to C8 (don't include FINAL row)

Cell B9: =F9 (final)

CREATE CHART

Now we have the data properly organized and formulas in place. Let's start creating the stacked column chart

1. Select the data including the row and column headers; exclude "ORIGINAL" column

2. Go to "INSERT" tab >>> Column Charts >>> Stacked Column

HIDDEN SECRET

Coming to the hidden secret of the Waterfall chart, we need to apply trick on "Hide" values (basically HIDE them).

1. Select the "Hide" series by clicking on any column, then right-click and select "Format Data Series…"

2. This will open "Format Data Series" dialog box on the right side

3. Press on "Fill" icon and select "No fill" and "No line" option

FORMAT WATERFALL CHART

We have the basic structure of the chart ready. It's time to do little formatting.

Let's first decrease the GAP between the columns so that its visually appealing.

Double-click any column >>> Series Options >>> Gap Width >>> 10%

Quickly change colour of different columns: Ends >>> BLUE; Rise >>> GREEN; Fall >>> RED

Select Series >>>  Right-click >>> Format Data Series >>> Fill >>> Choose Colour

Add Data Label to make values of the columns visible.

Select Series >>> Right-click >>> Add Data Labels

Finally, remove unnecessary data labels and legends from the chart

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

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.