|
|
||
|
|
|
Excel Index and Search Tool (Click
on the buttons below) |
|
Excel Lesson 12 of 30: Reports Presentation The most important reporting tool in Excel is the SUMPRODUCT function that AUTOMATICALLY brings the data into your table. Then you want these reports nice looking and user friendly. Here are a few tips and tools that you can use to achieve that purpose (conditional formatting, text boxes, hyperlinks and charts).
Conditonal Formatting You would like that when the value of a cell is greater then $50 the font turns blue and the background color changes to green. You need "Conditional Formatting". Select the cell or the cells where you want this to happen go to the menu bar Format/Conditional Formatting and the following window appears (may vary depending on the Excel version that you useyou use).
Select "greater than" in the second text box, enter 50 in the third text box and then click on the "Format" button. Choose blue as color for the font and select the "Patterns" tab and select the color green. Now if you write any number greater than 50 in one of the formatted cells the color of the font and of the background will change. If the value is smaller than 50 the default format is selected. When you don't want zero values to appear in a worksheet, you go to "Tools/Options/General" and remove the check for "Zero Values" but if you want not to see only the zero values within the table and not the total cells, select the interior of the table and use a conditional format that says "Cell value"/"Equal to"/0 and then select the color white for the font. Text Boxes Let's start this lesson on text boxes by talking about the MERGED CELLS. You want to write the word "Invoice" and you want this word to be centered within cells A2 to D2 like below. You select cells A2 to D2, you go to "Format/Cells/Alignment" and you check the box for "Merge Cells" and you select "Center" in "Horizontal"...and you end up with a major problem trying to modify the width of all 4 columns A to D. As much as possible avoid merging cells. Enter "Invoice" in cell "A2" then select cells A2 to D2. Go to "Format/Cells/Alignment" an in the "Horizontal" text box select "Center Across Selection". The effect is the same as with merging cells but you are now able to modify the width of all four columns without problems.
Now if you look at the image above you see that the height of row 2 is set by the largest font on the row. You end up with this same height in cell F2 even if what you need to enter is in a much smaller font. Also you could not enter two pieces of information and keep an interesting layout like in the image below.
Notice that all rows have the same height in this example. It is because the word "Invoice" has not been entered in a cell but in an invisible text box. Using these invisible text boxes make developing forms much easier. You can use the font that you want and you can move the text boxes around and adjust their position for much better layouts. The invisible text boxes When you want to work with text boxes and other drawings you need to make the "Drawing" toolbar visible at all time. Go to "View/Toolbars" and select "Drawing". This toolbar becomes visible.
If it is not automatically installed at the bottom
of your screen, drag it there. Click on You are now ready to work with text boxes and other drawings. Click once on the text box icon. Go to the sheet click, hold and stretch a text box. And you get this:
When the editing border of the text box is a series of forward slashes you are in the text editing mode. You can enter text, copy/paste text, format the font... Right click INSIDE the text box and see the options. If you click on the editing border it becomes a set of dots meaning that you are now editing the text box itself.
To make the text box invisible right click on the editing border, select "Format Text Box" then go to "Colors and Lines", in "Fill/Color" select "No Fill" and in "Line/Color" select "No Line".
Hyperlinks When you are surfing the WWW you click on a word, an image, a text box or anything else and you are taken somwhere esle. This technology is called hyperlinks and it exists in Excel. You can create hyperlinks in Excel and attach them to cells, images, text boxes and any other object. With these hyperlinks you can surf from one sheet of the workbook to the other, from one workbook to another (open or not) and you can open any other file created with any othe rprogram (Word, Power Point, your browser). You can even create a map of the USA and click on different States to go to different sheets (it is call dynamic mapping). You can even send emails from Excel using the hyperlink technology. To
create an hyperlink you select a cell or an image or any other object,
you go to the hyperlink icon
|
|
Excel Index and Search Tool (Click
on the buttons below) |
|
|
||
|
|
hold and drag.
and follow the instructions.