Some Useful Tips For Excel: Conditionally Formatting Rows, Page Breaks & More
Written By Samrat| 25 June 2009| 3 Comments
Table Of Contents:
Excel is a very handy tool. It is used for multi task. Know tips to make Excel more useful. Know few today and keep checking this section for further tips and tricks in Excel.
Changing The Font Size By Using Shortcut Keys
There are interesting shortcut keys that allows in changing the point size of selected cells. It is Ctrl + Shift + P, but in fact it depends on what you have displayed on the screen.
If, as people usually do it, you have the Formatting toolbar displayed then pressing the above mentioned shortcut keys selects the Font Size control on the toolbar. After this just type the desired font size and press Enter. This is best practiced if you don’t have the Formatting toolbar displayed for some reason or else you don’t want to keep your fingers away from the keyboard.
Conditionally Formatting Entire Row
Lets take a case to explain it. Let the data be in cells A3:H50. Follow the below steps:
- First select the cell A3 and scroll the worksheet so you can see cell H50. While scrolling keep the cell A3 selected.
- Next, hold the Shift key as you click on H50. Now A3:H50 should be selected with A3 still being the active cell.
- From the Format menu choose Conditional Formatting.
- Now go to the left-most drop-down list for Condition 1 and select Formula Is.
- There in the formula space on the right to the drop-down list enter this following formula: =$E3>40000
- After doing this just click the Format button.
- Specify how you want the cells that are greater than 40,000 to be displayed by using the controls in the dialog box.
- To dismiss the Format Cells dialog box just Click OK.
- Click OK to accept your conditional format.
Conditional Page Breaks
This tip will help you in Excel how you can automatically insert page breaks when the contents of a certain column change. Suppose there is a column that contains department names and in that you may need each department to start on a new page. Then follow the below mentioned steps:
- First be sure that your table contains column labels. For example, if the column A contains names of the department, then cell A1 should have a label like “Department.”
- Sort out the data in your table by using the department column as the key.
- Choose subtotals from the Data menu having any cell inside the table still selected from the data
- Next, select Department by using the At Each Change In drop-down list.
- After this select Count by using the Use Function drop-down list.
- Now select the name of the column where you want the subtotal to appear by using the Add Subtotal To list.
- Check that the Page Break Between Groups check box is selected.
- Then click OK. Excel will add the subtotals and also the page counts as it is directed.
Keep reading this section and keep commenting. We will be back with some other useful tips for Excel soon.