Cell Line Breaks Via a Formula
When creating a cell that joins text together you may want to include a line break between parts of the text. A function and a format combined can provide that. (more…)
Special Rounding
When you need to round to a certain number, e.g. 5 cents, you can use the MROUND function. If cell A1 contains a number then the following formula will round the value to the closest 5 cent value. =MROUND(A1,0.05) The MROUND function does have some issues - see this blog post.
The New Slicer in Excel 2013
Slicers are a graphic filtering tool added in Excel 2010. They allow you to filter Pivot Tables. Excel 2013 added a new slicer that makes filtering by dates a lot easier. (more…)
Formatted table shortcuts
Two of Excel keyboard shortcuts work differently in Formatted Tables (tables that are created using Format As Table icon on the Home ribbon) Ctrl + Space normally selects the entire column(s). In a formatted table it selects the data in the column. Pressed again it selects the heading and the data pressed once more it selected the whole column. Shift + Space normally selects the whole row(s), in a formatted table it will select the row within the table.
Two Excel Functions to Format Numbers as Text
Using numbers in automated text sentences can be frustrating. Typically you don’t want to display decimals, but you do want to use the comma format. (more…)
Excel 2013 – Start screen
When Excel 2013 opens it display its start window. Many users don't use templates so it is frustrating. Pressing the Esc key takes you to the familiar grid. To stop the Start screen displaying use Excel Options. Press in sequence Alt t o (not held down), at the bottom of the dialog untick the Start screen option and click OK. See image below.
A Useful Excel Range Naming Tip [VIDEO]
Here is the problem. You have a single column range. Each cell in the range needs to be given a unique range name. Doing this manually takes time, but there is a quick and easy method to do it. (more…)
Formatting part of a cell
When you are editing a cell you may notice that most of the ribbon icons are greyed out, because you can't use them. The formatting tools however are not greyed out. You can select part of the text in a cell and format it differently to the rest of the cell. See example below.
Extracting the Column Letter in Excel
There can be times when identifying the current or a specific column letter reference is useful. Check out the comments for a shorter formula – thanks Batuque. (more…)
Cell Link Shortcuts
If you want to follow a cell link or find cells that are linked to a cell you can use these two keyboard shortcuts. To follow a link use Ctrl + [ To find linked cells use Ctrl + ] To return after following a link, you can press F5 and then press Enter.
Unhide all sheets in Excel
In Excel if you need to hide data or workings sheets you can hide multiple sheets in one action. Unfortunately Excel won’t let you unhide multiple sheets in one step (even in the latest version). To get around this limitation you can use a macro that unhides all the sheets.
(more…)Flexible MIN and MAX
The MIN and MAX functions allow you to identify the lowest and highest values within a range. But what if you want to find the second, or third highest values? Well there are two companion functions that you can use to do just that. (more…)
Stop a cell from displaying and printing
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.
Cumulative Total in a Filtered Excel List
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…)
Counting text in a range
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.
Status Bar Calculations in Excel
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…)
Convert date to its Financial Year End Date
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…
Where is Print Selection in Excel?
In a recent training session I had a question about where the Print Selection option is in the new Excel versions. (more…)
Displaying Blank or a Zero if an error is encountered
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.
Adding Positive or Negative Values
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.
Finding how many entries in a column
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.
Restricting the Scroll Area in Excel
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…)Determine if a Value is Duplicated in a Column
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.
Determine if a value is in a range
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.
ABS Function
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.
Sum Function Range
The SUM function can handle more than one range eg Instead of =SUM(A1:A100)+SUM(C1:C100) Use =SUM(A1:A100,C1:C100) will total both ranges.
Zoom Percentage Shortcut
To zoom into the currently selected range use Alt W G Pressed in sequence, not held down. To return to 100% zoom use Alt W J
Page Breaks
To insert a page break at the current cell use Alt P B I Pressed in sequence, not held down. To remove a page break use Alt P B R
Sort current table in order
To sort the current table by the selected column in ascending order, press in sequence Alt H S S Do not hold the keys down, press one after the other. To sort in descending order use Alt H S O