MS Excel: Close all Open Workbooks

At times, when you have many workbook open on your desktop and wish to close all of them together,there is an easy way in MS Excel to do this. I could never understand the reason why this option is not on main menu and is kept hidden.
Follow this:

Press Shift key on the keyboard while clicking File on the menu;

And now you will see the option: Close All

Click on it and all the workbooks are closed unless, in some workbook(s), you have made changes and not saved them.Excel will prompt you to save changes and close the workbooks.

MS Excel: Not only sort data in a column but also in the rows

Click a cell of the row you want to sort horizontally.

Select Sort from the Data menu.

In the Sort dialog box, click on the Options.

In the Sort Options dialog box, click on Sort Left to Right, then click OK.

Back in the Sort dialog box, go to the Sort By combo box and select a row to sort, and also select the direction (ascending or descending).

You can select additional rows by going to the boxes.

Click OK.
Yu Hooo!!! Excel sorts the row horizontally.

MS Excel: Get Summary Calculations on the Fly

As a busy executive, you are presented with lots of data in MS Excel format. Though you know the formulas to calculate and find results, but it is just a waste of time to write formula to see what the sum of particular range is or which is the highest value or lowest value or may be the average.
While looking at the spreadsheet, you can do the following to improve your analysis capabilities or effectively respond to one off questions which keeps coming up during meetings:


1. From the menu option, select View, and check the status bar.





2. This action will start showing you status bar at the bottom of spreadsheet.

3. Select the range of data you wish to analyze, and you will see on the status bar, Sum= 100 or whatever is the sum of the range selected (By default, excel is set to show Sum).

4. Right click on status bar, and you will see options like Average, Count, Count Nums, Max, Min and Sum. Select any one and see the result on status bar.

MS Excel: Sum of a column with #DIV/0! Or any other error message in some cells

My friend recently requested me to suggest some quick fix for sum of a column which has few cells containing #DIV/0! Or any other error message. Doing the sum function produces error because sum neither ignores those cells nor understands it.

My process to deal with this problem is:

1.Insert a column next to the column you are trying to sum up.
2.In my example, the first cell is A3, I inserted column B.
3.In Cell B3, I wrote the formula “=IF (ISERROR (A3), 0,A3)”
4.This formula looks for the cell value and if it is an error, converts it to 0.
5.Then I copied this formula in B4 to B6 since my values were in up to cell B6, you could copy it till last cell value.
6.Select Cells B3 to B6, copy and paste special-values only.
7.Now you can sum this column and you are done.

Example:
Formulas:



Values:

MS Excel and VBA: Transposing Data

This tip is submitted by Anupam Srivastava:

Transposing Data from Column to Row with a feature to start a New Row for each New Record which is identified by an empty Row:

The original dataset was stored in the same column followed by an empty row before each new group entry. The task was to bring each dataset into row format to make data analysis job easier. This is particularly useful for comparing information.
This Macro scans through the column, copies each item and paste it into different columns in the same row and begins a new row for each empty row it finds. The datasets are differentiated by empty row before new dataset begins.
To maintain consistency with the generated table, it is wise to make sure that each new dataset has the same number of data entities (items).

For users with knowledge of DB, if we consider each group to be an attribute with x number of entities, then each new attribute that you add to the original data should consist x entities to make sure that the final table gets populated in a uniform fashion.

For this Macro it is assumed that each dataset consists of 7 entities


Sub DataTranspose()

‘ Declare variable to denote column and row assignment

' Row Variable
Dim i As Integer
' Column Variable
Dim j As Integer
i = 2
j = 1


' Sheet has 218 rows of data

For i = 1 To 218
Range("A" & i).Select
Selection.Copy
Range("a" & j).Select
ActiveSheet.Paste

' Increment Row Counter to read next row
i = i + 1
Range("A" & i).Select
Selection.Copy

' Transposing Row Entry to Column
Range("B" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("C" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("D" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("E" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("F" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("G" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("H" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("I" & j).Select
ActiveSheet.Paste
i = i + 1
j = j + 1
Next

End Sub


----------------------------------------------------------------------------------
Anupam Srivastava is B.Sc. (Computer Engineering) from Queen’s University, Kingston, Canada.