Tips and Tricks of Excel
Custom Toolbars
You can customize your toolbars and include functions you perform most often. Here's how:
-----1. Click View ' Toolbars ' Customize
-----2. Click New and Provide Toolbar Name (e.g. Test)
-----3. Click on Commands Tab
-----4. Drag and drop commands you use most often to your toolbar. (e.g. Insert Rows/Delete Rows/formatting, etc.)
Custom Lists
You can create custom lists that can be used over and over. For example, if you always use a list of states in alphabetical order you can set the list once and be able to reuse time and again. Here's how:
-----1. Visit www.usps.gov and copy the list of states
-----2. Paste the list into an excel sheet
-----3. Click Tools --> Options --> Custom Lists
-----4. Click on the box next to the "Import list from cells", highlight the list of states, and press.
-----5. Press Import button to complete the import.
-----6. Click on Sheet2, starting from position A1, type in Alabama and drag it down to copy - what happened?
Repeating Titles & Simple Formulas
As a print option, you may print column and row titles. Titles may be useful when you are printing a range that extends over several pages and you need titles visible to clarify the data. The titles columns or rows you select will repeat only on the pages that follow the page containing the title data. Here's how:
-----1. Open any excel sheet and type in some information.
-----2. Set Column A as a repeating print tile. Here's How:
-----3. Click File --> Page Setup --> Sheet tab
-----4 Click Columns to Repeat at Left and select columns in the worksheet.
Note:
------a. Columns must be adjacent.
------b. To remove print titles, delete the reference.
------c. To setup up rows, click rows to repeat at top.
------5. Print out a copy and see your results
Freeze Titles
Freezing the titles comes very useful when you have a very big worksheet and need to scroll a lot. Here's how to implement.
-----a. Click on the B Column
-----b. Click Window Free Panes
-----c. Scroll to the right and notice what happens.
Conditional Formats
For example, suppose a cell contains a formula that calculates the variance between forecast sales and actual sales. Microsoft Excel can apply green shading to the cell if the sales exceed forecast and red shading if sales fall short. If the value of the cell changes and no longer meets the specified condition, Microsoft Excel temporarily suppresses the formats highlighting that condition. Conditional formats remain applied to the cells until you remove them, even though none of the conditions are met and the specified cell formats are not displayed. Here's how:
-----1 Select the cells you want to format.
-----2 On the Format menu, click Conditional Formatting.
-----3 To use values in the selected cells as the formatting criteria, click Cell Value Is, select the comparison phrase and then type a value in the appropriate box. You can enter a constant value or a formula; you must include an equal sign (=) before the formula.
You can customize your toolbars and include functions you perform most often. Here's how:
-----1. Click View ' Toolbars ' Customize
-----2. Click New and Provide Toolbar Name (e.g. Test)
-----3. Click on Commands Tab
-----4. Drag and drop commands you use most often to your toolbar. (e.g. Insert Rows/Delete Rows/formatting, etc.)
Custom Lists
You can create custom lists that can be used over and over. For example, if you always use a list of states in alphabetical order you can set the list once and be able to reuse time and again. Here's how:
-----1. Visit www.usps.gov and copy the list of states
-----2. Paste the list into an excel sheet
-----3. Click Tools --> Options --> Custom Lists
-----4. Click on the box next to the "Import list from cells", highlight the list of states, and press
-----5. Press Import button to complete the import.
-----6. Click on Sheet2, starting from position A1, type in Alabama and drag it down to copy - what happened?
Repeating Titles & Simple Formulas
As a print option, you may print column and row titles. Titles may be useful when you are printing a range that extends over several pages and you need titles visible to clarify the data. The titles columns or rows you select will repeat only on the pages that follow the page containing the title data. Here's how:
-----1. Open any excel sheet and type in some information.
-----2. Set Column A as a repeating print tile. Here's How:
-----3. Click File --> Page Setup --> Sheet tab
-----4 Click Columns to Repeat at Left and select columns in the worksheet.
Note:
------a. Columns must be adjacent.
------b. To remove print titles, delete the reference.
------c. To setup up rows, click rows to repeat at top.
------5. Print out a copy and see your results
Freeze Titles
Freezing the titles comes very useful when you have a very big worksheet and need to scroll a lot. Here's how to implement.
-----a. Click on the B Column
-----b. Click Window Free Panes
-----c. Scroll to the right and notice what happens.
Conditional Formats
For example, suppose a cell contains a formula that calculates the variance between forecast sales and actual sales. Microsoft Excel can apply green shading to the cell if the sales exceed forecast and red shading if sales fall short. If the value of the cell changes and no longer meets the specified condition, Microsoft Excel temporarily suppresses the formats highlighting that condition. Conditional formats remain applied to the cells until you remove them, even though none of the conditions are met and the specified cell formats are not displayed. Here's how:
-----1 Select the cells you want to format.
-----2 On the Format menu, click Conditional Formatting.
-----3 To use values in the selected cells as the formatting criteria, click Cell Value Is, select the comparison phrase and then type a value in the appropriate box. You can enter a constant value or a formula; you must include an equal sign (=) before the formula.
Read more
posted by Sneakytje @ 7:26 PM
0 comments
