How to Fix Column Width in a Pivot Table

1. Launch the 'Microsoft Excel' program and open a spreadsheet in which a pivot table is already created.
2. Right-click the pivot table and choose 'Table Options' from the resulting menu.
3. Uncheck the 'Auto Format Table' box and click 'OK.'
4. Adjust the column widths and refresh the pivot table. The columns will stay fixed at the width you specified instead of reformatting to fit the data.

Read more ►

How to Create Control Charts in Excel

1. Create a two-columned table; enter the dates or other time measuring criteria in the right column, and the data to be graphed in the left column. For example, to begin creating a control chart to measure the number of products produced on an assembly line per day, list the respective dates on the left and the corresponding production numbers on the right.
2. Calculate the average of your entire data series by entering '=AVERAGE(first value,last value)' into any cell, and establish your desired upper and lower control limits. In our production example, you may find that the average number of products made per day is 100, and you may wish to produce no more than 150 per day and no less than 50. In this case 150 would be your upper control limit and 50 would be your lower control limit.
3. Create a second two-columned chart to list your average data value and control limits. Since these values will be the same for all dates, list only the first and last date in your data series in the left column, once for each value, and the corresponding values in the right column. In our example, assuming your first date is 1/1/09 and your end date is 2/2/10, you would enter '1/1/09' in any cell, '2/2/10' in the cell directly beneath it, and '100' directly to the right of both dates. You would then repeat this procedure twice, directly under the first entries; once for the upper control limit, and once for the lower.
4. Create an XY chart by highlighting the data in your first table and clicking 'Insert -> Charts -> XY Chart'. You will now have a control chart showing the data values at each time interval. In the example, you would have a horizontal XY chart showing the number of products produced each day.
5. Add your data average and control limits by highlighting each of the four-cell blocks in your second table, one at a time, and dragging them over to the chart. Click 'OK' in the Paste Special dialog box that pops up each time, and you will have a complete statistical process control chart. Following the example, you would highlight the start date, end date, and both instances of '100' for the average, then drag the group over the chart. You would then repeat the process for the upper and lower control limits.

Read more ►

How to Link Data to Different Worksheets in Microsoft Excel

1. Launch Microsoft Excel and open each of the source spreadsheets that contain information to link into the target spreadsheet. Open the target spreadsheet last.
2. Click on the cell in the target spreadsheet that is to contain linked information from a source spreadsheet and press the 'Equal' key ('='). Switch to the source spreadsheet and click on the cell that contains the information to be linked and then press 'Enter.' The target spreadsheet now contains a link to the data in the source spreadsheet as indicated in the formula bar. Changes made to the source spreadsheet will be reflected in the target spreadsheet automatically.
3. Click on a cell in the target spreadsheet that is to contain calculated information from cells in multiple source spreadsheets and press the 'Equal' key ('='). Switch to the first source spreadsheet and click on the first cell that contains the information to be linked. Type an operator such as the 'Plus' key (' ') and click on the next cell in the formula, from the same source spreadsheet or a different one, or even from the target spreadsheet if desired. Continue to add operators and cell references until the formula is complete, then press 'Enter' to store the formula and display the results of the calculation.

Read more ►

How to Create a Shortcut for Symbol Use in Excel

1. Make sure your Microsoft Excel security level is set to 'Low' or 'Medium.' On the 'Tools' menu, click 'Options.' In the 'Options' window, choose 'Macro Security' and click the 'Security Level' tab and set it to 'Medium' or 'Low.' If Excel is on 'High' security, then it won't allow your macro to run.
2. On the 'Tools' menu, hover over the 'Macro' option, and select 'Create New Macro.' (If you're using Excel 2007, go to the 'View' tab, click 'Macro,' and select 'Record Macro.'
3. Enter a name for your macro in the space provided. Make it something memorable and ensure that it begins with a letter. Do not make the macro name a cell reference--otherwise, it will create errors when run.
4. Enter a letter for your macro under 'Shortcut Key.' When you run your macro, you'll hold 'Ctrl' and press the key you provide in this box. You cannot use a number for the macro.
5. Choose where you want to store the macro. If you want to be able to use it with any workbook, choose 'Personal Macro Workbook.'
6. Enter a description for the macro. Be as concise as possible.
7. Click 'OK.' Now Excel is ready to record the macro. Carry out the steps needed to create your symbol or functions. Remember, the macro tool can record a number of functions in one round, so you can create complex routines to run with every step of the shortcut key.
8. Now that you're finished recording your Macro, click 'Stop Recording' in the Stop Recording toolbar. If you're using Excel 2007, click on the 'Macro' button in the 'View' ribbon, and select 'Stop Recording' from the drop-down menu.
9. Practice using the shortcut and make sure it works correctly. If it doesn't, go to 'Macros,' click on 'View Macros,' and click 'Edit' to make any necessary changes.

Read more ►

How to Reset Excel 2003 to Print DPI

1. Open the Microsoft Excel 2003 spreadsheet file that you want to reset dpi settings for.
2. Select the 'File' option from the top toolbar menu and then click the 'Page Setup' option. The Page Setup dialog box will then appear.
3. Select the '600 dpi' option in the 'Print quality' section to return to the standard dpi setting.
4. Click the 'OK' button to close out of the Page Setup dialog box and return to your document.
5. Click the 'File' button and then select the 'Save' option to save you changes made to the Excel 2003 spreadsheet.

Read more ►

How to Specify the Templates Folder in Excel 2007

1. Open Microsoft Excel 2007.
2. Click the 'Microsoft Office' button located in the upper left corner of the screen.
3. Select 'Excel Options' from the menu.
4. Click 'Advanced' from the 'Options' menu.
5. Select 'File Locations' from the general section.
6. Select 'User Templates.'
7. Press the 'Modify' button.
8. Browse to the new templates folder location.
9. Press the 'OK' button.
10. Click the 'OK' button again.

Read more ►

How to Use Bullets in Excel 2007

1. Open an Excel 2007 spreadsheet. Insert the cursor in a cell where you want to use bullets.
2. Press 'ALT 0149' to insert a bullet point into the cell. Press the space bar to put a space and then type in the bullet item.
3. Press 'ALT Enter' to put a line break in the same cell and then press 'ALT 0149' to insert another bullet point, or press 'Enter' to submit the text in the current cell and move to the one below it.

Read more ►

How to Graph a Histogram Bin in Excel

1. Type the data you want on your histogram in column A. Use a separate cell for each piece of data.
2. Type your corresponding bin numbers into column B.
3. Click 'Data,' then 'Data Analysis.'
4. Click 'Histogram,' then 'OK.'
5. Click the 'Collapse Dialog' button next to 'Input Range,' and then highlight your data range, which is column A.
6. Click the 'Collapse Dialog' button next to 'Bin Range,' and then highlight your data range, which is column B.
7. Click 'OK.'

Read more ►

How to Remove Blanks From Pivot Table

1.
Select any box within the pivot table report to activate the PivotTable toolbar. Click the 'Refresh Data' icon.
2.
Click on the data field drop-down list in the first column of the pivot table. Uncheck the box next to the '(blank)' option. Click 'OK.'
3. If there is another data field drop-down list in the same column, select it and uncheck the box next to the '(blank)' option. Click 'OK'. Repeat this step until all data fields in the column have all of their '(blank)' filtering options unchecked.
4.
Select the first data field drop-down list in the first row of the pivot table and uncheck the '(blank)' filtering sort options.
5.
Repeat Step 4 for each and every data field that has drop-down filtering list.

Read more ►

How to Protect a Worksheet in Excel 2007

1. Open the Excel workbook that has the worksheet you want to protect.
2. Click the worksheet you want to protect and then click the 'Review' tab located in the Excel ribbon at the top of the page. You can check spelling, add comments, and protect a worksheet here.
3. Look to the 'Changes' section of the ribbon and click the 'Protect Sheet' button.
4. Enter a password to protect the worksheet. If someone else wants to make changes to the worksheet, they will have to enter the password you selected.
5. Select any elements you want others who have access to the worksheet to be able to change without the password. For example, you can choose to allow other users to edit objects or use the Auto Filter. After you make your selections, click 'OK.' The worksheet is now protected.

Read more ►

How to Convert a Webpage Table to an Excel Spreadsheet

How to Convert a Webpage Table to an Excel 2003 Spreadsheet
1. Open the Excel worksheet into which you want to import the webpage table. Go to the 'Data' menu, point to 'Import External Data' and select 'New Web Query.' The 'New Web Query' window will open.
2. Type or paste the webpage address into the 'Address' bar and click 'Go.' The webpage will open in the dialog box with arrows to the left of each available table.
3. Click the arrow beside to table that you want to convert from the webpage to your Excel spreadsheet.
4. Click the 'Import' button. The 'Import Data' dialog will open. Under 'Where Do You Want to Put the Data,' select 'Existing Worksheet' and click 'OK.'
5. Wait while Excel converts the table to your worksheet. Modify the table and save your changes as desired.
How to Convert a Webpage Table to an Excel 2007 or 2010 Spreadsheet
6. Open the Excel worksheet into which you want to import the webpage table. Go to the 'Data' tab.
7. Click 'From Web' in the 'Get External Data' group. The 'New Web Query' window will open.
8. Enter or paste the webpage address into the 'Address' bar. The address may be up to 255 characters in length. Click 'Go' to navigate to the webpage.
9. Click the arrow icon next to the table you want to convert to your spreadsheet or click the arrow icon at the top left of the window to import the entire webpage. If you do not see arrows on the webpage, click the 'Show Icons' button on the 'New Web Query' toolbar.
10. Click the 'Import' button and Excel will convert the webpage table to your worksheet.

Read more ►

How to Embed Objects in MS Word

Microsoft Word 2007 or 2010
1. Open a Microsoft Word document.
2. Place your cursor where you want to insert an embedded object.
3. Click the 'Insert' tab. Click 'Object' in the 'Text' group.
4. Click the 'Create from file' tab to embed an existing object. Click 'Browse' to select the file from your computer. Uncheck the 'Link to file' box. Click 'OK.'
5. Click the 'Microsoft Office Button' or 'File' tab. Click 'Save' to save the Word document.
Microsoft Word 2003
6. Open a Microsoft Word document.
7. Place your cursor where you want to insert an embedded object.
8. Click the 'Insert' menu. Click 'Object.'
9. Click 'Create from file.' Click 'Browse' to select the file from your computer that you want to embed. Uncheck the 'Link to file' box. Click 'OK.'
10. Click 'File,' then 'Save' to save the Word document.

Read more ►

How to Cite MS Excel in Literature

Creating an APA in-text citation for Excel
1. Open your word processing software to write your paper. Write out the thought that needs to have a citation referring to Excel.
2. Type “Microsoft Excel” and, in parentheses, “Excel version” as your in-text citation. It should look something like “In using Microsoft Excel (2007), I found it easy to…”
3. Do not add an entry for this in-text citation later in your paper in the list of references or bibliography section.
Creating an APA software citation
4. Open your word processing software to write your paper. Write out the paraphrased thought that needs to have a citation referring to an uncommon piece of software.
5. Type in your citation in your text. It should be the author’s last name and the month of creation for the software. Example: Jack Smith’s software CaffeineDog (2010) allows you to feed the dog and start your coffee pot at the same time. Next you need to provide the reference in your list of references in the back of your paper.
6. Type out the name of the author, last name, first name. In parentheses, type in the year the software was published and a period.
7. Type in the name of the software and, in parentheses, type the version, if applicable. Do not italicize the title. In brackets, write out Computer Software. Add a period. Follow this with the name of the institution where the software was created followed by a period. Example: Smith, Jack (2010). CaffeineDog (Version 3.0) [Computer Software]. CaffeineDog Software, Omaha, NE.

Read more ►

How to Use an Excel List for a Random Drawing

1. Construct a list of contest entrants. If you already have a list with consecutively numbered entrants, skip to step 6. If not, open a new spreadsheet in Excel.
2. Click on cell B1. Enter, or copy and paste, the first person's name. Click on cell B2, and enter the next name. Continue down the column until you have entered all the names.
3. Assign a consecutive number to each entrant starting with 1 and ending with N, where N is the total number of entrants that you have. Click on cell A1, and enter '1.' Click on cell A2, and enter '2.'
4. Click on A1, and drag the cursor to cell A2. These two cells should now be highlighted.
5. Move your cursor to the bottom right corner of cell A2 until a solid black cross (one without arrows) appears. Click and drag this black cross down column A until you reach the last name. Each number should now correspond to a name to the right of it.
6. Click on any empty cell in the spreadsheet. Enter the following formula to generate a random number: =RANDBETWEEN(1,N)N is the total number of entries that you have. For example, if you have 100 contestants, you would enter: =RANDBETWEEN(1,100)Don't put spaces between any of the terms in the function.
7. Hit 'Enter' to receive a random number between 1 and N. That number corresponds to the winning entry in your list. To generate another random number, move your mouse to the random formula cell again, and hit 'Enter' again.

Read more ►

How to Remove a Page Break in Microsoft Excel 2003

1. Open the Microsoft Excel 2003 worksheet. Click the 'View' tab on the top menu.
2. Select 'Page Break Preview' to change the worksheet view. This exposes the page breaks in the worksheet.
3. Click on the page break you want to remove. Hold your left mouse button and drag the page break out of the preview screen. Release your mouse button. This removes the page break from the worksheet.

Read more ►