Keyboard Shortcuts:For full list, see here
Highlighting rows per one cells value You can have excel format an entire row based on the value of a single cell. For this example, lets say that you have a row of text that you want to change color based on the phrase on the left most column. We will match the color listed with what the text color will become. When the first column says "red" we want everything to the right of it to turn red. If is says "blue" then we want all the text to the right of it to turn blue, and if it's something else, to simply ignore it.
Using SUMIFSSay you want to only sum totals in a column, if the numbers are part of a specific group. In this example, we want to sum al the different servers based on what tier they are in. So for column "D", sum rows 3-4 for tier1, rows 5-6 for tier2, etc...This gets really easy with the "SUMIF" formula. The sum_range is row "D", the row you want to sum, the criteria_range1 is the row you want to compute some logic on (needs to be the exact same size as sum range), and the critiera1 is what you want the criteria_range to equal to sum that range. Hide Page Breaks:Your spreadsheet might get ugly as excel adds dashes in the sheet to show where the pagebreaks are located. If you want to hide this, under "File", "Options", select the "Advanced" tab, and navigate down to the "show page breaks" check box. Un-checking it will clear up your page.Fixing Cells that show Formulas rather then values:Sometimes cells in your spreadsheet get goofed up, and rather then showing the value of the cell, they show the formula.To fix this, simply do the following:
Repeating "Format Painter"You can simply copy styles from once cell(1), and make a new cell(2) use the same formatting. cell To do this, select the cell you like(1), then select the format printer icon(2), and then select the new cell that you would like to format(3) If you want to format multiple cells, when you select the format printer icon(2), click it twice. Adding leading zeros using the TEXT formulaIf you need to add leading zero's to a normal cell, you can easily do this with the "custom" cell format option. In the "Home" ribbon, select "More Number Formats..." Then select "Custom" and in the Type field enter zero's for each significant digit you want to denote. In the example above, we use the concatenate function to combine the three cells, but since the last cell (M5) has a leading zero added by the custom number format, it will not add it to the output of the formula. To solve this, we use the TEXT() function within the Concatenate function. |