General QA to Excel News Group

provided by XLDataSoft - Excel/VBA Consulting, Training and Software Development

NEW Q1: There is a long string of text in my cell, but it shows #### rather than the text. What is the problem?
A1: In Excel, the column width limit is 255 characters. If your text has more than 255 characters, the program shows #### rather than the text itself. This can be solved by format the cell using General rather than Text: Format Cell > Tab Number > Category General.

Q1: I have some data imported from other application. But in excel they are text rather than numbers. What is the easiest way to convert all of them into numbers?
A1: Do as follows: 1) In any cell type number 1;  2) Press Ctrl+C to copy the cell; 3) Select the whole range which contains the text which you want to convert to numbers; 4) Go to menu: Edit/Paste Special, check Paste -> Values, Operation ->   Multiple. This will multiply all cells by 1 and automatically convert those texts into numbers.

Q2: In Excel the column headings become to 1, 2, 3... not A, B, C, D... What should I do to change it back?
A2: Do as follows: Go to menu Tools>Options>General Tab> Uncheck "R1C1 Reference Style"

Q3: How can I achieve countif, maxif, sumif under multiple conditions?
A3: Use array function (end with Ctrl+Shift+Enter) like:
=SUM(IF((($B$2:$B$4)=2005)*(($C$2:$C$4)="Feb")*(($D$2:$D$4)="West"),$E$2:$E$4))
This above functions sum all the data in range E2:E4 under the condition that column B has 2005, C has "Feb" and D has "west".
You can use functions average, max, min, and count to replace sum function.

Q4: I have a range of formulas, some of them return values and other returns error. I want to calculate sum of all the values and ignore the error cells. What should I do?
A4: There are two ways to work out.
1) Suppress all the error values in your range - replace your original formula with =IF(ISERROR(Your Formula),"",Your Formula).
This formula first tells whether your formula returns a value or an error. Then, this formula returns blank if your formula returns error, otherwise, this formula returns the same value as your formula.
2) Use array function (end with Ctrl+Shift+Enter) like: =SUM(IF(NOT(ISERROR(!$A$1:$A$4)),!$A$1:$A$4)). You can use average, max, min, count functions to replace sum function.

Q5: I use vlookup function for value look up, but it does not return what I want - is there something wrong with my formula?
A5: Most likely you need to use "false" as the value of the 4th(last) parameter in vlookup function. This parameter (range_lookup) is optional and its default value "true",  which assumes the values in the first column of table_array is placed in ascending sort order but in your case it may not be ordered.

Q6: I want to find out the days between today and the end of this year. However, my result shows a date "2/11/1990" not a number. Why?
A6: The reason is Excel is too "smart" - since you want the difference between two dates, then it returns a date for you.
There are two ways to solve the problem: the first one is just re-format the result cell as numbers and the second one is to use format function TEXT.

Q7: I want to calculate sum of every 3 rows of data, which formula should I use?
A7: Try some formula like this:(array formula, ending with Ctrl+Shift+Enter)
=SUM(IF((MOD(ROW($A$1:$A$999),3)=0),$A$1:$A$999)).

Q8: I want to find the 2nd largest/smallest value in a large array, which function should I use?
A8: Use functions large(range, rank) and small(range, rank)

Q9: I have a long column of data and I want to reverse the order - last row become first row, ..., and first row become last row. Is there any function to achieve this?
A9: Suppose you have data in range A1:A999, a total of 999 rows. In cell B1, type formula
=INDEX($A$1:$A$999,ROW($A$999)-ROW($A1)+1)
and copy this formula to range B2:B999. Attention should be paid to the absolute/relative addresses in the formula.

Q10: I want to highlight alternative rows using different colors. How to set up conditional format?
A10: In the conditional format, set condition using formula =(MOD(ROW(),2 )=0)

Q11: I have a long column of data and want to calculate the average of every 12 rows, which function should I use?
A11: Suppose all you data are stored in column A from starting from cell A1, then in Column B, starting from cell B1, type formula
=AVERAGE(OFFSET($A$1,(ROW()-1)*12,0,12,1))
This will average every 12 rows, starting from A1. You can change 12 to any number you want. Also you can change average function to sum, min, or max.
If you want to extract value from every 12 rows, then use the following formula:
=OFFSET($A$1,(ROW()-1)*12,0,1,1)

Q12: When I type some text which include symbol @, Excel automatically formats is as an email address. What should I do to avoid it?
A12: This is an AutoCorrect problem. Go to menu: Tools/AutoCorrect/AutoFormat As You Type/Clear [Replace As You Type-Interent...]

Q13: I am preparing a timesheet and am inserting times as hh:min. I have 5 entries in a column and want to sum up the times. The results is totally not what I want.
8:30
8:03
8:35
8:36
7:34
A13: Assume your time 8:30 is for 8 hours and 30 minutes, then use the following array functions (end w/ Ctrl+Shift+Enter)
=SUM(HOUR($A$1:$A$5))+SUM(MINUTE($A$1:$A$5))/60
which will give you a total of 41.30 hours.

Q14: I have a table of sales data - Column A has the sales person's name, B has the month number and C has the sales amount.
I want to extract the sales based on given salesperson and month. Which function should I use?
A14: There are more than on solutions to your questions. Here is one of them:
Use function MATCH(1,(A1:A10="Salesperson")*(B1:B10=MonthNumber),0) to find the row number which meet your given criteria.
Use function INDEX(A1:A10,RowNumber) to get the sales amount data in Column C.
Combine the above two functions together can get what you want - =INDEX(A1:A10,MATCH(1,(A1:A10="Salesperson")*(B1:B10=MonthNumber),0))

Q15: My page setup center footer is too long and it overlaps with left and right footers. What should I do?
A15:If you use Excel menu to setup the page, then use [enter] key at the end of left/right footer, i.e. add more blank lines as left/right footer which puts the center footer (at the bottom) align with the added blank lines and does not overlap the existing left/right footer. If you use VBA code, then use "&vbCrLf" at the end of right/left footer.

Q16: I know I can use function TODAY or NOW to get current date and time. However, it changes when the time changes. How can I input the fixed date (today and current time) into my cells?
A16: Use shortcut keys: Ctrl+; to input today's date and Ctrl+Shift+; to input current time.

Q17: I want to have different Tab color for different sheet, what should I do?
A17: Go to menu: Format/Sheet/Tab Color and select the color your like for the active sheet only. (Only work for Excel 2002+)

Q18: I have a workbook with >20 sheets. It is difficult for me to select the sheet I want. Is there any way to sort the sheets' name?
A18: For Excel itself, there is no direct way to do it. But this utility program can do it for you - http://www.xldatasoft.com/downloads/SortSheet.xls