You can use a Custom number format to stop a cell or range from displaying and printing. Select the cell or range Press Ctrl + 1 Use the 1 on the keyboard not the numeric keypad. In the Number tab click the Custom option (bottom left-hand side) In the Type box enter ;;; Click OK This doesn't stop the contents of the cell from displaying in the Formula bar.
You might think adding a cumulative total to a filtered list is impossible, but with one of Excel’s new functions, it is easy. (more…)
To find out how many cells contain a text string in a range you can use the COUNTIF function and an Excel wildcard character. To see how many cells contain abc in column A you could use =COUNTIF(A:A,"*abc*") This will count abc no matter where it appears in the text eg 123abc, abc123 and 123abc123 will all be counted. It is only counted once per cell. The * is the wildcard character.
Excel has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula. (more…)
To convert a date to its financial year end date you can use the following formula, assuming the date is in cell A1. =DATE(YEAR(A1)+(MONTH(A1)>6),6,30) The (MONTH(A1)>6) part returns TRUE when the month number of the date is above 6. In Excel TRUE = 1, hence 1 is added to the year of the date. If the month number is 6 or less it returns FALSE and in Excel FALSE = 0, so the year is left unchanged. A shorter formula…
In a recent training session I had a question about where the Print Selection option is in the new Excel versions. (more…)
To display a blank cell if an error is encountered use =IFERROR(A1/B1,"") To display a zero in a cell if an error is encountered use =IFERROR(A1/B1,0) The IFERROR function is available in Excel 2007 onwards.
To add up the positive values in a column you can use =SUMIF(A:A,">0") This will display the total of all the positive values in column A. To add up the negatives use =SUMIF(A:A,"<0") This will display the total of all the negative values in column A.
To find out how many entries there are in a column you can use =COUNTA(A:A) This will display how many entries there are in column A. This is different from the COUNT function which only counts numerical values.
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
(more…)To determine if a value is duplicated in a column you can use =COUNTIF(A:A,B1)>1 Will display TRUE if the value in cell B1 appears more than once in column A and FALSE if it appears once or not at all. You can use the above as a logical test in an IF function as well.
To determine if a value is in a column you can use the COUNTIF function =COUNTIF(A:A,B1)>0 Will display TRUE if the value in cell B1 is in column A and FALSE if it isn't. You can use the above as a logical test in an IF function as well.
To confirm that two values are within 1 of each other you can use the ABS function. =ABS(A1-B1)<=1 Will display TRUE if the values in the cells are within one of each other. It will display FALSE if their difference is more than one. 1 is used as it is a common balancing check for rounded numbers.