Excel

Browse » Home » All posts

Friday, March 11, 2011

How to Create Dynamic Charts in Excel Using Data Filters

1. Organize the source data in your spreadsheet.
2. Create the type of chart specific to your data needs using the Chart Wizard tool.
3. Insert data filters to the source data by selecting 'Menu' then 'Data.' Next choose 'Filter' and then 'Auto Filter.' In Microsoft Excel 2007, use the home ribbon and then select 'Filter and Sort Button' followed by 'Filter.'
4. Apply the data filter to select the type of data you wish to use in the chart. The chart will automatically change depending upon how the data is filtered.

How to Add Add

1. Open Excel and click the Office button. You can open, save or print a document and view the default settings for Excel using this menu.
2. Click the 'Excel Options' button located at the bottom of the menu that opens.
3. Select 'Add-Ins' located in the options list. You can view and manage your add-ins here.
4. Click the arrow next to 'Manage' and select 'COM Add-Ins' from the drop-down list that opens and click 'Go.' A box will open that shows the add-ins that are available for you to add or remove from Excel.
5. Select an add-in you want to use in Excel and click 'Add.' The add-in will be added to Excel.

How to Do Add

1. Open Microsoft Excel, and click on the 'Office' button.
2. Click on 'Excel Options' located at the bottom right corner of the Office menu window.
3. Select 'Add-Ins' from the vertical navigation bar on the left of the 'Excel Options' window.
4. Check the boxes next to the add-in programs you would like to download, and then click 'OK.'
5. Follow the installation prompts to install the add-ins, and then click on 'Add-Ins' located in the toolbar at the top of the Office Excel page to load and use your add-in programs; you can also access them through the data, formula and add-in tabs.

How to Create High Resolution TIFF Files From Excel Files

1. Create your file in Microsoft Excel. This can be anything from an invoice to a chart to a simple listing of data.
2. Select the area of your Excel spreadsheet that you want to convert to a TIFF file. Go to the 'Page Layout' menu then click 'Print Area' and 'Set Print Area' (this option may appear under 'File' menu in some older versions of Excel).
3. Print the file to a standard postscript printer (download a free file at Adobe.com). Name the postscript file and type in '.ps' without the quotation marks as the file extension.
4. Open the .ps file in Adobe Photoshop. A dialog box will open, asking you to confirm information about the file size and resolution. Type in the resolution you would like---300 pixels per inch and up is considered 'high-resolution' in most design applications. Set 'Mode' to CMYK. Click 'OK' to continue.
5. Look at the file in Photoshop to ensure that the output is the same as you saw in Excel. The text inside of an Excel chart may be different after the conversion---if you don't like it you can delete and re-add the text elements in Photoshop.
6. Adjust the size of the image in Photoshop if you'd like. You can crop it down, make it larger and even add photos and additional text as you see fit.
7. Go to 'File' then 'Save As' on the Photoshop menu. Select 'TIFF' from the 'Format' drop down list. Click 'Save' to create the TIFF file.

How to Make an X Axis in Excel 2007

1. Open the spreadsheet with the data for which you wish to create a graph. Place all the X values in one column and the Y values in a second column. Each row should contain the matching values for both axes.
2. Click 'Insert' on the Menu bar and go to 'Chart.' Pick your desired picture and input the description of the graph in the box provided.
3. Move to Step 2 of the Chart Wizard to arrange the Chart Source Data. Highlight the columns and their respective headings and click 'Enter.' Choose the 'Series: in columns' button.
4. Go to Series tab and fill out the X and Y value fields. Check this area carefully to make sure that Excel 2007 correctly identifies the columns for the X and Y axes. Since you are creating an X axis, be sure that it contains the correct data for that column before hitting the 'Next' button.
5. Fill out all the other tabs in the next step for the titles of the axes as well as the legend and labels of the data.
6. Click on the next step of the chart wizard to select the position of the graph. You can choose to create the graph on a separate workbook sheet or on the same page.

How to Use a Letter to Represent a Value in Excel

1. Launch Microsoft Excel.
2. In the Excel spreadsheet, type a letter in the column 'A' (for example, in the cell 'A1'). Type the value, which should be represented by the letter, in the same row in the column 'B' namely in the cell 'B1.'
3.
Repeat Step 2 to add to the spreadsheet other letters you wish to associate with values. Make sure to add one such 'letter-value' entry per row. In the example shown in the figure, the array 'letter-value' spans six rows from the cells 'A1' and 'B1' to 'A6' and 'B6.'
4. In the Excel spreadsheet, select a cell in which you want to insert the value using a letter. Type the following function: '=LOOKUP('T',A1:B6)', then press 'Enter.' Note that 'A1' and 'B6' are the first and last cells in the array defined in Step 2. 'T' is the letter. In our example, the value '9.06' appears in the cell.
5. Use the form 'LOOKUP('letter',A1:B6)' as an argument for any function. For instance, select a cell and type '=SUM(LOOKUP('u',A1:B6),LOOKUP('A',A1:B6))'. Press 'Enter.' The sum of two numbers represented by letters 'u' and 'A' appears in the cell. In our example, it is 18.17 (12.5 5.67).

Monday, February 28, 2011

How to Use Excel's Concatenate Function

1. Learn the syntax for Concatenate. It is Concatenate (text_1,text_2,...text_n) where text_1,text_2,...text_n are 1 to 30 text items that will be joined together. These values may be numbers, text strings or references to single cells.
2. Use the ampersand () as the calculation operator. This method also may be used to join text items instead of the Concatenate function. For example, =A1A2 will return the same result as =Concatenate (A1,A2).
3. Look at an example for Concatenate. Set A2=salmon, A3=species, A4=25 and enter =Concatenate('Stream population for ',A2,' ',A3,' is ',A4,'/mile') in a cell. This formula will return a result of 'Stream population for salmon species is 25/mile'.
4. Study the use of cell ranges for Concatenate. Enter =Concatenate(A2:A3) in cell B2. Notice that Concatenate only displays salmon in cell B2 because it only uses text strings that are in the results row. You would need to use an appropriate Concatenate formula on each row in order to use fields on those rows.
5. Enter a Concatenate formula automatically. Position the cursor in the cell you wish to use, select the Insert menu on the menu bar and select 'Function' from the 'Insert Sub-menu.' Select the 'Text' category and then the 'Concatenate' function. Supply the arguments as prompted and click the 'OK' button to display the results

How to Preview an Excel Worksheet

1. Start Microsoft Excel and open a spreadsheet that you are ready to print.
2. Choose the 'File' menu. Select 'Print Preview' to enter 'Print Preview' view.
3. Navigate through your Excel spreadsheet pages by using the 'Next' and 'Preview' buttons at the top of the 'Print Preview' view.
4. Zoom on your Excel spreadsheet by clicking the 'Zoom' button while in the 'Print Preview' view. Click on 'Zoom' once to magnify the spreadsheet page you are on. Click it again to return to full-page view.
5. Print the spreadsheet by clicking the 'Print' button to exit 'Print Preview' and bring up the 'Print' dialog box.
6. View the formatting specifics of your spreadsheet by clicking the 'Setup,' 'Margins' and 'Page Break Preview' buttons. The 'Setup' button will open the 'Page Setup' dialog box, the 'Margins' button will show the current margins of the spreadsheet and the 'Page Break Preview' will show the location of each page break.
7. Use the 'Help' button if you need more help and then click the 'Close' button to exit 'Print Preview' and return you to your spreadsheet.

How to Modify Pivot Table Data

Change Data
1. Locate the source data that your pivot table is based on. This is normally at the top left corner of the worksheet, or on a separate worksheet that you can access from the tabs at the bottom left corner of the Excel window.
2. Make the required modifications to your data.
3. Navigate back to the Excel pivot table and click on any cell within the table.
4. Click the 'Options' tab at the top of the screen, then press the 'Refresh' button in the middle of the toolbar. Your pivot table will now reflect the changes you made in the source data.
Add New Data
5. Locate and navigate to your source data.
6. Add the necessary information to your source data. Just like the original data, all information must have column headers in the top row and cannot contain subtotal cells.
7. Navigate back to your pivot table and select any cell in the table. Click the 'Options' tab at the top of the screen and then click the 'Change Data Source' button in the middle of the toolbar. A small window will appear.
8. Navigate back to your source data, the 'Move PivotTable' window will remain in place, even if you have to change Excel worksheets.
9. Click and hold on the top-left cell in your source data, then drag your mouse to the bottom-right cell and release the mouse button. Click 'OK' on the 'Move PivotTable' window to complete the process. Your pivot table will change any data that you updated, and you will see any new fields appear in the field list.

Sunday, February 27, 2011

How to Identify Duplicate Items in Excel

1. Click on the column name that you want to check for duplicates. You can also select a range across several columns by selecting the top-left cell in the range and then holding 'Shift' while selecting the bottom-right cell.
2. Select the 'Home' tab at the top of the Excel window. Click on the 'Conditional Formatting' button in the 'Styles' area of the ribbon.
3. Move your mouse over 'Highlight Cell Rules' in the drop-down menu and then choose 'Duplicate Values.'
4. Choose the type of color to apply to duplicate cells by using the drop-down box on the right. It defaults to a light red color with red text but you can quickly change it to yellow, green or you can create a custom look by choosing 'Custom Format.' Click 'OK' when you are done and all the duplicate cells will be highlighted.

How to Customize Columns and Rows in Excel

Customize the Columns
1. Click and drag your mouse over the columns you want to customize. Alternately, click the letter directly above the column to automatically select all the cells in this area.
2. Select the 'Column' option under the 'Format' drop-down menu, then click the 'Width' option to enter a distinct value for this measurement.
3. Use the 'Formatting Palette' (Mac), the 'Formatting Toolbar' (Windows) or the 'Font' group on the 'Home' tab (Excel 2007) to personalize the column. Click the 'Fill Color' option to apply a background color, or click the 'Pattern' or 'Pattern Color' box to insert a design.
4. Click the 'Type' or 'Border Type' and choose the line, color and style that define the outline of the column area.
5. Save your workbook.
Customize the Rows
6. Select rows by repeating the process in Step 1, or click the numbers to the left of the cell.
7. Choose the 'Row' option under the 'Format' menu, then click the 'Height' title to enter a number for this dimension.
8. Repeat Steps 3 and 4 from Section 1 to format the background color and border of the row(s).
9. Save your Excel file.

How to Convert Quattro Pro for Windows to Excel 2007

1. Download the Quattro Pro file converter from Microsoft. The link is in the Resources section below.
2. Double-click on the file you download (qp7conv.exe). Follow the download instructions on the screen.
3. Open Excel 2007. Click on the Office Button (the round icon at the upper left with the Microsoft Office logo on it) and select 'Open.' On the drop-down menu for 'file type,' select Quattro Pro. This will allow you to open any Quattro Pro file (with extensions *.wb3 or *.wb2) into Excel 2007.

How to Open to a Specific Directory in Excel 2007

1. Click the 'Office' button in the upper left of Excel.
2. Select 'Excel Options.'
3. Click 'Save' on the left hand menu.
4. Change 'Default file location' to the specific directory you want to use. Click 'OK.' Now if you open or save a file, your new directory is the one that will open.

Saturday, February 26, 2011

How to Insert Page Numbers in Excel 2007

Adding Page Numbers
1. Open the worksheet to which you would like to add sequential numbering, and select the 'Insert' tab from the text group.
2. Choose 'Header and Footer' and then select 'Click to add header,' or 'Click to add footer,' depending on where you would like the numbers to be located. This will display the 'Header and Footer Tools' option.
3. In order to select the location of the page number, you will choose the left, center or right section of the header or footer you have decided on.
4. Click 'Page Number' from the 'Header and Footer Elements group.'
5. Once you see 'Page' appear, press the space bar once and then type the word 'of' followed by a space. This will allow you to add the total number of pages to the document. Next, click 'Number of Pages' from the 'Header and Footer Elements' group.
Completing the Process
6. Once the placeholder '[Page] of [Pages]' appears, click anywhere outside of the header or footer to display the actual page numbers in your 'Page Layout View.'
7. You can choose to start with a different number by clicking 'Page Setup,' and then 'Page Layout.'
8. From there, you can click the dialog launcher beside 'Page Setup.'
9. On the 'Page' tab, you would simply change the 'First Page Number' option to whatever you prefer.

How to Link Cell Sheets in Excel 2007

1. Type '=' in the cell that you want the link to occur in.
2. Click the worksheet, located at the bottom of the page, that contains the cell you want to link.
3. Select the cell you want to link and press 'Enter.' Excel then takes you back to the page where you created the link. By selecting the cell, the Excel automatically formats the 'sheetname!celladdress' for you.

Subscribe to: Posts (Atom)