Here is a Sample File for you to download. Therefore I would do this trick without GROUPing columns but bear in mind of the F9 key. Moreover, it may not be easy to group columns with real life data in a seamless way without adding extra columns. Nevertheless, doing the GROUPing is kind of tedious. With this workaround, we save the key stroke of F9 after hiding / unhiding. Interestingly, hiding/unhiding columns by using the +/- button does trigger re-calculation. Note: as we have inserted three columns, the formula in J4 (which was previously in G4) is now extended to cover column A:H. We should be able to get the above layout. Now we should see the +/- button on top of column labelĤ) Repeat Grouping to columns D:E, and G:H (tips: Select the columns and then press F4, which is to repeat previous action) Please follow the steps below:ġ) Insert 3 empty columns (C, F, I) (tips: set column width to 2 which is more or less the width of the +/- button displayed on top)ģ) Data tab –> Outline Group –> Group ( shortcut tip: Alt+Shift+Right arrow) Select Only Visible Cells In Excel Menu Select the cells you want to copy. Go ahead and use the Ctrl+C and Ctrl+V shortcuts to copy and paste the content of the visible cells.

This outline means that only the visible cells are selected. A white outline will appear around the selected cells. (note: we may hide ROW 1 for better visual effect) To further workaround it, we may GROUP columns and use the +/- button on the top to hide or unhide columns. Once you’ve selected the cells, type the Alt+ shortcut. We need to give it a little push, say pressing F9, after hiding/unhiding columns. Although CELL is a volatile function (meaning it will get recalculated whenever there is a change in the worksheet), a simple HIDE/UNHIDE COLUMN action does not trigger recalculation. To see or print the gridlines for these cells, remove the fill color by selecting the cells, and then click the arrow next to Fill Color (Home tab, Font group), and To remove the fill color, click No Fill. Now we may hide column C:D to see the result… If you apply a fill color to cells on your worksheet, you won't be able to see or print the cell gridlines for those cells. Put it in other words, SUM visible columns only. It simply instruct Excel to sum the range of A4:F4 where corresponding cells in A1:F1 is greater than zero (“>0”). In A1, input the formula: =CELL('width',A1) 'copy across to F1Īs mentioned in previous post, the function CELL(“width”,A1) returns 0 (zero) when the column is hidden. What we need is a helper row, say Row 1 in our example, and SUMIF function. If we need to SUM visible columns only, we will need a twist. However, there is no similar function for visible columns only. We know that SUBTOTAL allows us to perform some basics functions like SUM, COUNT, AVERAGE, etc.

Excel 2016 select visible cells