Excel

Browse » Home » All posts

Thursday, February 16, 2012

How to Add a Trend Line on an Excel Spreadsheet

Excel 2003
1. Create a chart from the data with which you want to make a trend line. Press the 'F11' key to create a chart using your default chart type, or click the 'Chart Wizard' button on the 'Standard' toolbar and follow the steps to create a basic chart.
2. Select the data series in the chart from which you want to create a trend line.
3. Go to the 'Chart' menu and select 'Add Trendline' The 'Add Trendline' dialog box will open.
4. Go to the 'Type' tab. Select the type of trend line you want to use and click 'OK.'
Excel 2007 or 2010
5. Open the Excel spreadsheet with the data from which you want to create a trend line. Select the data and make a basic chart. Press the 'F11' key to create a chart using your default chart type, or go to the 'Insert' tab and select a bar or column chart type in the 'Chart' group.
6. Click on the chart area to activate Excel's 'Chart Tools.'
7. Go to the 'Layout' tab under 'Chart Tools.'
8. Click the 'Trendline' button in the 'Analysis' group. Select the type of trend line you want to add.

How to Master Excel 2007 Pivot Tables

1. Open or create an Excel spreadsheet. The sheet needs to contain 20 or more rows of data plus column headings, which are required to create pivot tables.
2. Select the data you want to include in your pivot table. Either highlight the data before creating the pivot table or select the data during the pivot table creation process (See Step 3.)
3. Select the “Tables” group and choose “Insert.”
4. Select “PivotTable” and select “PivotTable” again.
5. Follow the prompts to create a pivot table. If you want to use data from an external source, choose “Use an external data source.”The default is to use data from the existing spreadsheet. Type in the cell range you want to include (not needed if you highlighted data beforehand) and finish the pivot table wizard to create a pivot table along with a provided field list based upon the range you chose.
6. Drag field names to the corresponding sections in the field list to organize the data you want summarized. The four main areas include Report Filter, Column Label, Row Label and Values.
7. Check each field name you want added to the pivot table. If you did not drag a field name to an area name, right-click the field and choose the area you want to add it to.
8. Practice adding fields to different areas of the pivot table to master the purpose of each area and how a pivot table is laid out.
9. Go to the “View” menu in the pivot table field list to change how the pivot table field list is viewed.
10. Change the way data are summarized by right-clicking a field heading or section in the pivot table and choosing “Field Options.” This includes changing various calculations such as average, count and sum.

How to Save a Workbook With a Password in Excel

Password Protect Workbook
1. Double-click on the Excel document to open it up.
2. Click on the 'File' tab at the top of the screen. Select 'Info' from the menu that covers the left side of the window.
3. Click on the 'Protect Workbook' button in the 'Permissions' area of the screen. Choose 'Encrypt with password' from the list of options. Enter in your desired password and click 'OK.' Enter the password again to confirm it, and press 'OK.'
Protect Document Elements
4. Double-click on the Excel file to open it up. Select the worksheet that you want to protect from the list of worksheets at the bottom of the Excel window.
5. Select the 'Review' tab at the top of the screen. Locate the 'Changes' area of the ribbon and select either 'Protect Sheet' or 'Protect Workbook.' Protecting the sheet will allow you to lock down the particular worksheet you are on from any changes. Protecting the workbook will prevent anyone from making changes to the overall design of the document, like adding new worksheets or altering the name of a current worksheet.
6. Place check marks next to the items that you want to allow people to alter. Anything without a check mark will be locked down. If you chose 'Protect Workbook,' do the opposite, as a check mark will signify to lock down that aspect of the workbook.
7. Type in a password into the field at the top of the window. Press 'OK' to continue. You will have to enter the same password again, and press 'OK' again to lock down the document.

How to Move a Column in Microsoft Excel

1. Open the Excel workbook to the worksheet containing the column you want to move. If you are moving it to a different workbook, open the second workbook to the target worksheet as well.
2. Click in the column header for the column you want to move. Use the built-in Excel column header with the pre-assigned letter, not a user-entered column header. Clicking the built-in header will select the entire column.
3. Press the 'Ctrl' and 'X' keys simultaneously to cut the column from its original location.
4. Click the column header to the right of where you want the column inserted, whether it is in the same worksheet or elsewhere. This will highlight the column you just clicked.
5. Press 'Ctrl' and 'V' simultaneously. This will paste the column to the left of your highlighted area, moving it to your target location.

Wednesday, February 15, 2012

How to Create a Curved Line Graph in Excel or Word

Microsoft Excel
1. Open Microsoft Excel. Highlight the data you want to insert in a graph.
2. Click the 'Insert' tab from the menu at the top of the page. You can insert pictures, clips and graphs here.
3. Look to the 'Charts' section on the drop-down menu. Select 'Line' and then choose either a 2-D or a 3-D line chart depending on your preferences. Your data will appear in a line graph.
4. Right-click on the line in the graph and select 'Format Data Series.' This is where you will choose options to make the line in the graph curved.
5. Select 'Marker Line Style' from the options at the right of the box that opens. Check the box that says 'Smoothed line' and click 'Close.' You will now have a curved line graph on your spreadsheet.
Microsoft Word
6. Open Microsoft Word. Click 'Insert' from the toolbar at the top.
7. Select 'Chart' and when the charts options box opens, choose 'Line' and click 'OK.'
8. Enter the data for the chart in the spreadsheet that appears. After you enter the data, close out the spreadsheet. The data will be applied to the graph on your document.
9. Right-click on a line in your graph and select 'Format Data Series.' Select 'Marker Line Style' and check the box that says 'Smoothed line.'
10. Click 'Close.' You will now have a curved line graph on your Word document.

How to Zip an Excel File

1. Open 'My Documents' in Microsoft Windows XP or earlier. If you are running Windows Vista, open 'Documents.' Both of these are usually found on the 'Start' menu.
2. Navigate through the folders until you locate the Excel file you want to zip.
3. Right-click on the file icon or name you want to zip and select 'Send To' from the pop up menu that appears.
4. Select 'Compressed (zipped) folder' from the sub-menu. A new zipped file will be created in the same folder. It will keep the same file name, but with a '.zip' extension, as your Excel file. This is the default, but you can change it just like any other file name.

How to Use Excel for Correlation of Data From Three Sensors

1. Arrange the data from the three sensors into columns in the Excel spreadsheet. To illustrate how to find the correlation, dummy numbers will be used as an example.
2. In column A, in cells A1 to A10, enter the following numbers:
0.33
0.05
0.77
0.90
0.25
0.66
0.30
0.12
0.05
0.85
3. In column B, in cells B1 to B10, enter the following numbers:
0.54
0.18
0.29
0.01
0.60
0.05
0.01
0.89
0.02
0.36
4. In column C, in cells C1 to C10, enter these numbers:
0.32
0.95
0.50
0.52
0.91
0.75
0.83
0.01
0.11
0.15
5. Make sure the Data Analysis add-in is switched on. Go to the 'Tools' setting on the menu bar, click 'Add Ins,' then check the box next to 'Analysis ToolPak.'
6. Click on the 'Tools' menu selection again, then scroll down to 'Data Analysis.'
7. Click on 'Data Analysis,' scroll down to 'Regression,' then click 'OK.'
8. Put the cursor in 'Input Y Range' then shade cells A1 to A10.
9. Put the cursor in 'Input X Range' then shade cells B1 to C10.
10. Click on 'OK' and the spreadsheet will produce a regression output data table automatically.
11. Look under regression statistics. The third number down is called 'Adjusted R Square.' This number represents the correlation of these three columns of data, which in this case are slightly negatively correlated with a value of -.16.

How to Copy and Paste Multiple Cell Contents in Microsoft Excel 2003

1. Highlight the cells that you wish to copy. Simply left-click on a cell and hold the mouse button as you drag the cursor over the multiple cells you wish to copy. Then let go of the mouse button and the cells will stay highlighted.
2. Copy the cells. To do this, scroll to the “Edit” tab on the command bar and select “Copy.”
3. Highlight the cells you wish to paste into. Find the group of cells that you wish to paste into; they must number the same as the cells you copied. Highlight the cells that you wish to paste into by following the directions in Step 1.
4. Paste into the cells. To paste your cells into the new cells, scroll to the “Edit” tab on the command bar and select “Paste.”

Tuesday, February 14, 2012

How to Create a Mirrored Image in Excel

1. Select or create the image you wish to manipulate in just about any software program you want. The image can be simple or intricate, homemade or an object that you found in an obscure photo album.
2. Click the image so that the entire object is highlighted. This will ensure that whole image will be copied to Excel.
3. Right click and select 'Copy.' Open or go to the Excel window and click on the cell where you want the image to be.
4. Pick 'View' from the top of the Excel window. If there is not a check mark next to the Drawing toolbar option, click 'View,' 'Toolbars' and 'Drawing.' A separate toolbar along the side, bottom or top of your window will appear.
5. Choose the 'Draw' option on the Drawing toolbar. In most versions of Excel it has an arrow pointing up or down next to it. A pop up window will appear next to it. Click 'Rotate or Flip' and then 'Flip Horizontal' or 'Flip Vertical' depending on how you want or image to look.
6. Drag the outer corner of your image inward or out to change the size.

How to Construct a Categorical Frequency Table in Excel 2007

1. Enter your data into one column. For example, if you have a list of 10 house prices that you want to categorize, enter them into cells A1 to A10.
2. Enter your bin values into the next column. In our example, you would enter your bin values (for example, 100,000, 200,000, 300,000) into column 2. Bin values are optional: If you do not enter values for bins, Excel will choose bins for you.
3. Click on 'Data Analysis' from the 'Data' tab.
4. Click on 'histogram' from the list box.
5. Enter the range for your chart data into the 'Input Range' box. For example, enter 'A1:A10.'
6. Enter the range for the bin values into the 'Bin Range' box. For example, if you have four bins in cells B1 to B4, enter 'B1:B4.' If you also want to display a histogram, check the 'Chart Output' box.
7. Press 'OK.' Excel will enter the frequency table into the Excel worksheet.

How to Change Decimal Formatting Placement Within Cells in Microsoft Excel 2003

1. Open and access your spreadsheet file by clicking on the “File” tab and scrolling to “Open.”
2. Activate the cell or cells in which you wish to change the decimal formatting placement. To activate the cells that you wish to change, either left-click on a single cell or left-click and hold as you drag the cursor over multiple cells to highlight them.
3. Open the cell properties box. To do this, right-click on the cell or cells that you wish to change the properties for. A sub-menu will open. Select “Format Cells.”
4. Change the cell decimal formatting placement. Under the cells properties box, left-click on the “Number” tab. Under the category drop-down menu left-click on the “Number: selection; a “Decimal Places” box will open inside of the cells properties box to the right. Use the up/down arrows to change the decimal places. Click “Okay” to implement changes.

Monday, February 13, 2012

How to Calculate a Weighted Average in Excel 2007

1. Enter your two sets of values that you wish to find the weighed average for into your spreadsheet, separating them into two columns. For the sake of this example, assume that the values in the first column stretch from A1 to A5 and the values in column B stretch from B1 to B5.
2. Type the following formula into a blank cell, cell C1, without quotes: '=SUM(B1:B5)' This formula will calculate the sum of the second column of numbers.
3. Type the following formula into another blank cell, cell C2, without quotes: '=SUMPRODUCT(A1:A5,B1:B5)' This formula calculates the total amount paid into the first and second columns.
4. Type the following formula into a final blank cell, cell C3, without quotes: '=C2/C1' This calculates the total weighted sum for the first column when compared to the second column.

How to Use Percentages in MS Excel

1.
Use MS Excel to display decimals as percentages.
Format a cell to display a percentage. In Excel, type a decimal value into cell A1, such as 0.3. Press 'Enter.' On the 'Home' tab of the ribbon, in the 'Number' section, click '%' to display 30%.'
2.
Calculate percentages by dividing an amount into the total.
Calculate a percentage. If you have 100 boxes and 12 of them are red, find the percentage of red boxes by dividing the amount,12, by the total, 100. In Excel, enter the following:Cell A1: 12
Cell A2: 100
Cell A3: =A1/A2In cell A3, press 'Enter' to view the result, 0.12. Format cell A3 as a percentage---as in Step 1---to display '12%.'
3. Figure the amount of a percentage. If you are buying a camera for $150 and you want to find how much a 5 percent sales tax will be, multiply the total, 150 by the decimal that represents the percentage (divided the percent, 5, by 100), 0.05. In Excel, enter the following:Cell B1: 150
Cell B2: .05
Cell B3: =B1*B2In cell B3, press 'Enter' to see the result, 7.5. To show the value as a dollar amount, format cell B3 as currency. On the 'Home' tab of the ribbon, in the 'Number' section, click '$' to display '$7.50.'
4.
Use Excel to calculate a discount or an original price.
Find the original amount from a total that has been reduced by a percentage. If you are shopping for shirts at 30 percent off and the reduced price is $21, calculate the original price by dividing the discounted price, 21, by the difference between 100 percent and the 30 percent discount (1 minus 0.3), in this case 0.7. In Excel, enter the following:Cell C1: 21
Cell C2: .7
Cell C3: =C1/C2In cell C3, press 'Enter' to see the original price, 30. Format cell C3 as currency to display '$30.00.'
5.
You can use percentages to figure dollar amounts.
Increase a total by a percentage. If you get a 3 percent raise and your salary is $48,000, figure your new salary by multiplying your current yearly wages, 48,000, by 1 plus the percentage of the increase (1 plus 0.03), 1.03. In Excel, enter the following:Cell D1: 48000
Cell D2: 1.03
Cell D3: =D1*D2In cell D3, press 'Enter' to see your new salary, 49,440. Format cell D3 as currency to display '$49,440.00.'
6.
MS Excel can help with budgets and accounting tasks.
Decrease a total by a percentage. If your monthly entertainment expenses are $100 and you want to reduce expenditure by 5 percent, multiply your current budget, 100, by 1 minus the percentage of the decrease (1 minus 0.05), or 0.95 in this example. In Excel, enter the following:Cell E1: 100
Cell E2: .95
Cell E3: =E1*E2In cell E3, press 'Enter' to view your revised entertainment budget, 95. Format cell E3 as currency to display '$95.00.'

How to Get to VBA in Excel 2007

1. Click the Microsoft Office icon at the top-left corner of the Excel spreadsheet. Select the 'Excel Options' button at the bottom of the drop-down menu.
2. Navigate to the 'Popular' tab at the left side of the window. Locate the 'Top Options For Working With Excel' heading.
3. Check the check box labeled 'Show Developer Tab In The Ribbon' underneath the 'Top Options' heading. Click 'OK.'
4. Click the 'Developer' tab at the top of the screen. Select the 'Visual Basic' option underneath the Microsoft Office button to bring up the VBA screen.
5. Double-click the 'Sheet1' entry at the left side of the VBA window to bring a blank screen. Type your VBA code into the blank area. Click on the other sheet options if you want to add VBA code to the other sheets in your Excel workbook.

How to Center a Worksheet Horizontally in Excel 2007

1. Select the Office Button and click 'Open.' Double-click the file you want to modify in the 'Open' dialog box. It loads on your page.
2. Select the worksheet you want to align in the workbook. To center each worksheet in your file, right-click on any worksheet tab (at the bottom of your page) and choose 'Select All Sheets' from the pop-up list.
3. Select the 'Page Layout' tab. Click the arrow on the right of the 'Page Setup' group.
4. Select the 'Margins' tab in the 'Page Setup' dialog box.
5. Go to the 'Center on page' section and check 'Horizontally.' Click 'OK.'

Sunday, February 12, 2012

How to Put a Picture in the Background on an Excel Spreadsheet

Microsoft Excel 2003
1. Open your spreadsheet. Click on the tab labeled 'Format.'
2. Scroll over 'Sheet,' and then select 'Background.'
3. Browse through your pictures to find the one you want to add. Click on the file name, then click 'Insert.'
Microsoft Excel 2007
4. Open your spreadsheet. Click on 'Layout' in the top menu bar.
5. Click 'Background,' and then browse through your pictures.
6. Select the picture you want to set as the background and click 'Insert.'

How to Create a Drop

1. Click in the cell that you want to have a drop-down 'pick' list. Select the 'Data' tab. In the Data Tools group, click 'Data Validation.' The Data Validation dialog box appears.
2. Select the 'Settings' tab. In the Allow box, select 'List.'
3. If you have the items typed in a range (one word per cell, either across or down) and in the same worksheet, then enter the range in the Source box (or click the icon to select the range from the worksheet). Alternatively, type the items in the Source box, separating them by a comma. When you are finished, click 'OK.'
4. Navigate to your worksheet. You will see a blank cell with a drop-down arrow. When you click the drop-down arrow of the box, a drop-down will display where you can select the desired response.

How to Create a Run Chart

1. Enter column headers. In Excel, put 'Date' in cell A1, the name of the variable you are tracking (such as weight/value) in cell B1 and 'median' in cell C1.
2. Add dates. Add the dates that you recorded data on in column A. For example, you might have 10/1, 10/2, 10/3, 10/7, 10/8. Usually, though, you will have a longer series.
3. Add the values. Add the values you observed in column B. For example, you might have 150, 151, 152, 151, 150.
4. Find the median. At the bottom of column B, click 'Formulas,' then 'More functions,' then 'Statistical' and 'Median.' Copy this number into column C next to each row that has data in column B. Then delete it from column B. In our example the median is 151, so add 151 to cells C2 through C6.
5. Add a chart. Click 'Insert,' then 'Line with markers.' This will be your run chart.

Saturday, February 11, 2012

How to Make a Loan Amortization Table in Excel

1. Install the Amortization Schedule for Excel product by Vertex42. This is a free download through CNET. You can quickly build a table that reflects all the different parameters of a loan. While most loans are based on a set payment schedule, this Vertex42 template shows how occasional changes to the payments affects the overall loan. If a payment is missed, or the occasional payment is increased, this table reflects the consequences of these actions on the final payback amount. Simply download the file and open it in Excel, and the interface is straightforward from there. No prior financial experience or programming background is required to quickly build amortization tables.
2. Open an official Microsoft Excel template designed for amortization tables. Microsoft offers hundreds of free templates for all Microsoft Office products in a wide range of application categories. As Excel is commonly used for financial analysis, amortization templates are readily available from the Microsoft website. Excel natively supports many financial formulas and calculations relating to interest rates and scheduled loan payments. The Microsoft templates simply organize these built-in functions into an easy interface so anyone can take advantage of Excel's programming functions without having to construct formulas manually. Users who desire added functionality or custom formatting can build on the template with their own content, if desired.
3. Purchase an Excel add-in for a more comprehensive analysis of loan scenarios. The Spreadsheet Store provides an Excel loan analysis package called 'Loan Calculator for Excel.' This suite of multiple Excel worksheets covers nearly any loan scenario you will encounter. As of March 2010 the price for this package is $25. Special payment schedules are supported by the program. As many loan types do not involve a set payment amount each month, the 'Loan Calculator for Excel' may be the best option. Variable interest rates and graduated payment plans are among the situations not easily analyzed by free Excel software. This package easily manipulates these extra variables. Unlike other products, it also creates charts to more effectively display the parameters of a loan.

How to Default to Excel 2003 and Not 2007

1. Close any open spreadsheet programs on your computer.
2. Click 'Start' in the lower left corner and then 'Documents' to open the directory containing your spreadsheet files.
3. Find a spreadsheet file that you want to associate with Excel 2003. The file will have a spreadsheet extension such as xls, csv or xlsx. Right-click the file name and select 'Properties' to view the file extension if it is not otherwise visible.
4. Right-click the file name and scroll to 'Open with' in the dialog box that appears. Then click 'Choose default program' to open another dialog box.
5. Click the Excel 2003 icon to set this as the default program for opening all spreadsheet files with the same extension as you selected earlier.Click 'Browse' if the Excel 2003 icon is not visible and double-click the program's name from you directory list of available programs.

How to Create a Break Even Graph in Excel

1. In cell A1, type 'Fixed Cost,' and in B1 enter the dollar amount of your fixed costs. For example, the supplier of mylar balloons requires that you pay $100 membership fee to be a buyer, and you are charged that amount no matter how many balloons you buy. In that case you would type '100' into B1.
2. In cell A2, type 'Cost per Unit,' and in B2 enter the dollar amount of the cost per unit. For example, each balloon cost $1. You would enter '1' into B2.
3. In cell A3, type 'Revenue per Unit,' and in B3 enter the dollars amount of the revenue per unit. If you plan to sell your balloons at the county fair, and you know you can charge $6 per balloon, then enter '6' into B3.
4. In cell A5, type 'Units.' In cell A6, enter the number 1. Under the number one (in cell A7) enter the number 2, and continuing entering numbers until you reach 25.
5. In cell B6, type 'Cost.' In B7 type '=A7*$B$2 $A$2' without any quotes. This formula means 'Multiply the number of units by the cost per unit, then add the fixed cost.'
6. Copy B7, and paste it into every cell in the Cost column. In our example, the first cell should read '101,' and each cell should grow in value by 1, until the final value is '125.'
7. In cell C6, type 'Revenue.'In C7 type '=A7*$C$2' without any quotes. This formula means 'Multiply the numbers of units by the revenue per unit.'
8. Copy C7, and paste it into every cell in the Revenue Column. In our example, the first cell should read '6,' and each cell should grow in value by 6, until the value is '150.'
9. In cell D6, type 'Profit'. Profit is Revenue-Cost, so enter the formula '=C7-B7' in cell D7.
10. Copy that cell, and paste it into every cell in the Profit column. In our example, the first cell should read '-95' or '(95)' (meaning negative 95). The final column should read '25.'
11. Highlight the area from A6 to D30 by holding down the left mouse key and mousing over the area.
12. Click the Insert tab on the ribbon at the top of the Excel interface. Inside the 'Charts' area on the Insert tab, you'll see a 'Line' button.
13. Click that button then choose 'Stacked Line' from the sub menu. This will bring up a line chart. The break even point is the point on the chart where the profit graph crosses the cost graph.

How to Create a Pivot Table to Consolidate Multiple Data Ranges

1. Open the Microsoft Excel application that contains that data you want to create a PivotTable for on your computer. Click on any blank cell within your worksheet.
2. Click on the 'Data' option and then click the 'PivotTable and PivotChart Report' option for Excel 2003. Press the 'ALT', 'D' and 'P' keys at the same time for Excel 2007. The PivotTable and PivotChart Wizard will then appear.
3. Click on the 'Multiple consolidation ranges' option and then click on the 'Next' button. Click on the 'I will create the page fields' option and then click on the 'Next' button.
4. Click on the 'Collapse Dialog' option and then select the first data range you want to add from your worksheet. Click on the 'Add' button to add the data range.
5. Click on the 'Collapse Dialog' option again to select another data range that you want to add. Click on the 'Add' button again to select your second data range. You can then continue to add as many data ranges as you want.
6. Click on the '0' option below the 'How many page fields do you want?' field and then click on the 'Next' button. Select a location for your PivotTable to be placed in your worksheet and click on the 'Finish' button.

How to Label the Columns of Microsoft Excel 2007 with Letters

Windows version of Excel 2007 or higher
1. Open Excel. You do not need to bring up a specific file. Just click to open the program, which will automatically open a blank spreadsheet.
2. Select the Office symbol button in the top left corner of Excel to open the menu. At the bottom right, click on the 'Excel Options' button. A new window will appear.
3. Click on 'Formulas' from the menu on the left and look mid-page under the heading 'Working with Formulas.' Uncheck the first item ('R1C1 Reference Style'), and you will return to columns labeled with letters. To change back to numbers, simply check the box once again. Click on 'OK' to close the window and save your selection.
Mac version of Excel
4. Open Excel and click on the 'Excel' menu option in the furthest top left corner. Scroll down and select 'Preferences' from the menu selections. A new window will pop up.
5. Click 'General' under the 'Authoring' heading to bring up the selections in the next window.
6. Remove the check from the box beside 'Use R1C1 reference style' to return column labels to letters, instead of numbers. Select 'OK' at the bottom right to save your options and close the window.

How to Use Vlookup in Excel 2007

1. Open MS Excel 2007. Click on the Windows icon on the top left of the screen. Scroll to and click 'Open.' Search your computer for the file you need to find values on. Click on it to select it and click on the 'Open' button to open the file.
2. Make sure there are definite options of things to search for. For example, if you want to list the city of people who contacted your organization, but you only have the cities listed by code, you will use a VLOOKUP to look up which city corresponds to that code.
3. Build a table array which lists your options somewhere else on the spreadsheet. In the option above, list all of your city codes in one column and the corresponding cities in the column next to it.
4. Add a column in your main spreadsheet to list the values you are looking up by right-clicking on the existing column to the right and clicking 'Insert.' In this city example, your column could be titled 'City Name.'
5. Type '=VLOOKUP(' in the first cell where the values will be located. Click on the cell with the value you are looking up (The city code in this example). Type ',' after you click in the cell.
6. Highlight the array you created in the other area of your spreadsheet with what you are looking up and their values. Type ','.
7. Type the number of the column in the array the information you want to display is in. In this example, you will have two columns of data in your array, the city code on the left and the city name on the right. For this array, you would type '2' after the comma in Step 6 to tell Excel that the information to display is in column 2 of the array. Type another comma.
8. Type 'false' after the comma in Step 7. This will tell Excel that you only want to return the data if it has an exact match. Placing 'true' in this spot will return the closest value to what you are looking up and you don't want that. Type a ')' and press 'Enter.' The cell will now contain the city name for that city code.
9. Click in the cell with the VLOOKUP equation. Click into the formula bar and place the cursor before the first cell in the array portion. Type '$' in front of all the column letters and row numbers in the array. (For example, if the array is G1:H125 you would type '$' before G, 1, H, and 125 to look like $G$1:$H$125). Press 'Enter.'
10. Right click in the VLOOKUP cell and click on 'Copy.' Highlight all the other cells in the column, right-click, and click on 'Paste' to paste the equation into all the other cells.

How to Find the Percentage of Two Cells in Microsoft Excel

1. Open Microsoft Excel.
2. Create your first data point. For the purpose of this example, assume you want to find out what percentage of your total business income was earned in the months of March and April. Enter 'March' (without quotation marks) in cell A1, then press the 'Tab' key and enter '8,500' (again without quotation marks) in cell B1.
3. Create your second data point. Press the 'Enter' key to move your cursor to cell A2. Enter 'April,' press the 'Tab' key, then press '8,750' in cell B2.
4. Add your data points. Move your cursor to cell B3, directly below your two number values. Enter the following formula into this cell to sum the two values:=SUM(B1:B2)
5. Calculate the percentage of the total. Double-click cell B3 to edit its contents, then add a '/' character and the total value by which the preceding cells should be divided. For this example, assume your annual business revenue is $115,000. Your formula should now look like this:=SUM(B1:B2)/115000The formula tells Excel to take the sum of the values in cells B1 and B2 and divide them by the total yearly revenue value of $115,000. Press 'Enter' after inputting the revised formula to return your final percentage (0.15, or 15 percent, in this example).

Saturday, January 28, 2012

How to Create a Custom Sort in MS Excel

1. Open the Excel worksheet.
2. Click and drag to select the range of cells you wish to custom sort.
3. Click the 'Home' tab on the command Ribbon.
4. Click the 'Sort Filter' button in the 'Editing' group. A list of options appears.
5. Click the 'Custom Sort' option. A dialog window opens. The categories include 'Column,' 'Sort On' and 'Order'
6. Select or deselect the check box for 'My data has headers.'
7. Click the arrow next to the 'Sort by' text box in the 'Column' category. Select the 'Column.'
8. Click the arrow in the text box under 'Sort On.' Select the preferred option, such as values or cell color.
9. Click the arrow in the text box under 'Order.' For example, 'Smallest to Largest' for values, or 'A to Z' for text.
10. Click the 'Options' button. A small dialog window opens. Select the orientation for sorting, such as 'Sort left to right' or 'Sort top to bottom.' Click 'OK.' This window closes.
11. Click 'OK.' The 'Sort' window closes. The range of cell contents display the new order.

How to Calculate Fractions in Excel 2007

1. Open the Excel spreadsheet you want to use to calculate fractions.
2. Click an empty cell and enter a number in decimal format, for example, '1.875.' Press 'Enter.' The number should show up exactly as you entered it.
3. Click the cell again and then click the 'Home' tab on the menu ribbon. Click the down arrow in the text formatting window in the 'Number' group. The text formatting window might display 'General' initially or something else. Click 'Fraction' from the drop-down list. The number displays as a fraction now.
4. Add, subtract, multiply, divide or otherwise create any equation using fractions in the formula bar. For example, to add two fractions, enter '=1/2 1/4.' Excel will display '3/4' in the cell if the cell is formatted as a fraction.

How to Lock Column Headers on Microsoft Excel

1. Open a worksheet in Microsoft Excel. Add row and column header text as desired.
2. Click on the column letter on the right side of the column you want locked. This highlights the entire column. Highlight a group of columns by clicking on the first column and dragging the mouse, with the left button depressed, across the desired columns to highlight.
3. Click on the 'View' tab if using Microsoft Excel 2007. Under the Window group item in the menu, click on the arrow for 'Freeze Panes.' Select the option to 'Freeze First Column' to lock only the one column highlighted. Select the option to 'Freeze Panes' if you want to freeze a group of panes at one time.
4. Click on the 'Window' menu if using Microsoft Excel 2003 and select 'Freeze Panes' to lock one or a group of columns.
5. Repeat the process listed in steps 1 through 4 to unlock the column by selecting 'Unfreeze Panes.'

How to Make a Histogram From a List of Data in Excel

1. Open a new Microsoft Excel 2010 spreadsheet. Select cell 'A1' and type in 'Input Range.' Select cell 'B1' and type in 'Bin Range.'
2. Click on cell 'A2' and type in the first value in your data set. Enter the rest of the data set into the cells down the first column.
3. Select cell 'B2' and enter your bins down the second column. Each actual bin number will stand for the top value in that bin's range. So, if you enter bins of '20,' '40' and '60,' the ranges will be anything below 20, 21 to 40, and 41 to 60, respectively. Excel will automatically add an additional bin called 'More,' which will collect every value higher than your highest bin number. The More bin will appear even if your highest bin is equal to or greater than your highest value. In addition, you have the option of not entering any bins and allowing Excel to automatically determine your bins.
4. Click the 'File' tab at the top of the screen and click 'Options' from the menu that appears on the left side of the Excel window. Click on 'Add-Ins' on the left edge of the Options window, then click the 'Go' button. Click the check box next to 'Analysis ToolPak' and click 'OK.' This will load the add-on that is required to create a histogram. You will only need to load this add-on once, as Excel will now load it automatically.
5. Select the 'Data' tab and click the 'Data Analysis' button. Select 'Histogram' and click 'OK.' The Histogram window will appear.
6. Click the radio button next to 'Output Range' and then click on cell 'C1.' Click the check box next to 'Chart Output.' This process will begin the histogram table at cell 'C1' and create the actual histogram to the right of the table on the spreadsheet.
7. Place your cursor into the 'Input Range' field and press 'Backspace' to erase whatever was in the box. Click and hold the mouse button over cell 'A1' on your worksheet, then drag the mouse down until you reach the last value in your data set and release the mouse button. If you entered your own bins on the worksheet, repeat this process with your bins in the 'Bin Range' field.
8. Click the check box next to 'Labels' as your data has labels in the first row, then click 'OK.' Excel will display your histogram.

How to Unlock an Excel Workbook

1. Open the Excel workbook you want to unlock.
2. Select 'Tools,' 'Protection' and 'Unlock Workbook' from the toolbar. If you added a password initially (when you locked the file), you will be prompted to type it in. Click on 'OK' in the 'Unprotect Workbook' box. The workbook will be unlocked.
3. Select 'Tools,' 'Protection' and 'Unprotect Shared Workbook' from the toolbar to unlock a shared workbook. Enter the password into the 'Unprotect Sharing' box if you inserted one initially. Then click on 'OK.' Click on 'Yes' to remove the workbook's shared settings if you are prompted to.
4. Select 'File' and 'Save As' from the toolbar to remove the password from a password-protected workbook file. Click on 'Tools' and 'General Options' in the 'Save As' dialog box. Remove the password from the 'Password to open' field (to allow users to open it) and/or 'Password to modify' field (to allow users to modify it) and click 'OK.' Click on 'Save' to save the workbook without a password.

Excel 2003 Menu Bar Tutorial

1. Click 'File' to view options that are related to file operations. You can create new files, open files, save files, print files and change file properties from this menu.
2. Click 'Edit' to view common options for performing Excel edits. This menu contains options that allow you to copy and paste data, find and replace text strings and delete cells or cell contents.
3. Click 'View' to change the Excel 2003 display. You can use this menu to change the Excel toolbars, document headers and footers and change the screen view.
4. Click 'Insert' to add something new to your document. You can use this menu to add blank cells, columns or rows. You can also add formula functions and graphics to a cell via this menu.
5. Click 'Format' to change the appearance of elements in your document. You can change the format of individual cells, rows, columns and entire worksheets using the functions under this menu heading.
6. Click 'Tools' to access a menu of tools that you can use to augment the contents of your workbook. The Tools menu includes entries for spell-checking, sharing your document and protecting cells with a password. The menu also contains entries for changing Excel options and editing the Excel AutoCorrect feature.
7. Click 'Data' to perform categorical edits on your worksheet's data. You can sort data, create forms and filters, insert pivot tables and import external data to your spreadsheet.
8. Click 'Window' to change the active window, freeze panes in your spreadsheet or split your view of the spreadsheet into multiple parts.
9. Click 'Help' to view Excel 2003 help documentation.
10. Click the 'Double Arrow' icon at the bottom of any menu to view all menu items. When you first click on the menu, the menu only displays items that you have used recently.

How to Compare Two Lists in Excel 2007

1. Open a new workbook (click the 'Office' button, 'New' and 'Blank Workbook') as well as the two lists you intend to compare. In the new spreadsheet, make headers across the top: 'Original List,' 'Comparison List' and 'Missing?'
2. Copy and paste the original list into the new spreadsheet. Move over a column and paste the comparison list into the spreadsheet.
3. Move your cursor to cell C2, the first empty cell in the 'Missing?' column. Type the function into this cell. Type in '=isna(match(' then either click the first cell in the 'Comparison' list or type B2 (if that's the cell number). Type a comma, then either click and drag to select the whole 'Original List' or type the range in--this might be something like 'A2:A18.' Type in another comma, then the word 'FALSE.' Finally, type two closed parentheses. The whole function should look like =isna(match(B2,A2:A18,false)).
4. Press 'Enter.' The function should return a 'TRUE' if the item in cell B2 does not appear in the list under column A, and 'FALSE' if it does.
5. Click to select the cell with the function in it. Change the cell range of the 'Original List' so there are two dollar signs in it. It should now read A$2:A:$18. These dollar signs will prevent Excel from changing that range in the next step.
6. Press 'Enter' again, then click and select the function again. Click the small black fill handle on the bottom-right corner of the cell and drag that cell down to the last row in the 'Comparison List.''
7. Look at the list you just created. If the lists are similar, most of the items under 'Missing?' should say FALSE.' Any items marked 'TRUE' don't appear in the 'Original' list.

How to Copy Paste Filtered Data

1. Drag your mouse over the filtered data so it is highlighted. This identifies to the program which data you wish to copy.
2. Press the 'F5' button on your computer keyboard, if you are using Excel 2007 or later. A pop-up window will appear. Alternately, you can click the 'Go To' button in the 'Find Select' section of the Excel toolbar. This step is not necessary for versions of Excel prior to 2007.
3. Click the 'Special' button on the pop-up window.
4. Click the 'Visible cells only' option, and press 'OK'. This instructs Excel to only select the data included in the filtered results. Hidden data from the original unfiltered data set is ignored. You are now ready to copy.
5. Press the Control-C keyboard combination to copy the data. Alternately, select the 'Copy' button on the Excel toolbar.
6. Click on the spreadsheet, file, or other document where you wish to paste the filtered data.
7. Press the Control-V keyboard combination to paste. The filtered data is pasted into the new file.

Friday, January 27, 2012

How to Copy a Microsoft Excel Sheet

Excel 2007
1. Click on the tab of the worksheet you want to copy.
2. Right-click with your mouse on the tab, then choose 'Move or Copy.'
3. Select where you want the worksheet to go. Use the drop-down menu to select a workbook.
4. Click on the check box next to 'Create a Copy.'
5. Click on 'OK.'
Excel 2003
6. Open the worksheet you want to copy.
7. Click on 'Edit,' then click on 'Move or Copy worksheet.'
8. Click on 'Create a Copy' in the dialog option box.

How to Format Number Currency in Excel 2003

1. Click 'Start' in Windows, select 'Microsoft Office' and then click 'Microsoft Office cell 2003.'
2. Click the cell that contains the number you want to display in the currency format.
3. Click 'Cells' on the 'Format menu.'
4. Click 'Currency' in the 'Category' box on the 'Number' tab.
5. Click the currency symbol you choose in the 'Symbol' list.
6. Enter the number of decimal places that you want to display in the 'Decimal Places.'
7. Select the display style for negative numbers in the 'Negative Numbers'.
8. Click 'File' and then click 'Save' to apply the change to your Excel spreadsheet.

How to Draw a Histogram in Excel 2007

1. Type the word 'Bins' into cell A1.
2. Type the word 'Frequencies' into cell B1.
3. Enter the minimum values for each histogram bucket in column A, under the word 'Bins.' For example, in cell A2 begin with '0.2,' and continue down the column with increasingly large numbers.
4. Enter the number of data items that belong in each given bin in column B, under the word 'Frequencies.' For example, if the first two bins are 0.2 and 0.3, and there are 12 data items that fall between the values 0.2 and 0.3, then type '12' to the right of 0.2 in column B.
5. Select all the data (including the header title) in column B. To select it, left-click on the title, and drag the mouse to the bottom of the list.
6. Click the 'Insert' ribbon.
7. Click the 'Column' button in the Charts section of the Insert ribbon.
8. Click the first icon on the top left of the Column drop-down. It looks like a flat bar graph.

How to Design a Crossword Puzzle With Excel 2003

Designing the Crossword Puzzle
1. Open a new document in Microsoft Excel 2003.
2. Select the area of your spreadsheet where the crossword puzzle will go. (Make sure to keep this same area highlighted throughout all of the steps in this first section.) Then right-click over the selected area and choose 'Format Cells' from the pop-up list.
3. Select the 'Alignment' tab. Then choose 'Right Indent' for the horizontal text alignment and 'Top' for the vertical text alignment.
4. Select the 'Font' tab. Then pick 'Arial' for the font, '10' for the font size and 'Superscript' for the effects and click 'OK.'
5. Select 'Format,' 'Row' and 'Height' from the toolbar. Type '20' into the 'Row Height' dialog box and click 'OK.'
6. Select 'Format,' 'Column' and 'Width' from the toolbar. Type '4' into the 'Column Width' dialog box and click 'OK.' The selected cells should now be square.
7. Select the 'Border' button from the 'Formatting' toolbar. Choose 'All Borders' to create a border around each selected cell.
Adding Content
8. Place your cursor in the cell where you want to add a number. Type in the number you want. Then press 'Enter' or use your arrow keys to move to the another cell in the crossword.
9. Insert additional numbers into the desired cells until they are all in.
10. Select any cells within the crossword puzzle that you want to shade (areas where letters/words will not be entered when printed). To select multiple cells at the same time, hold down the 'ctrl' key on your keyboard as you select each cell.
11. Click on the arrow beside the 'Fill (Color)' button in the 'Formatting' toolbar and choose black. The selected areas will be shaded.
Inserting the Clues
12. Place your cursor outside the crossword puzzle (below it) to insert the clues for the crossword puzzle. Type in 'ACROSS' and bold the text by using the 'Bold' button in the 'Formatting' toolbar. Then press 'Enter.'
13. Type in the number '1' and your first clue for the 'ACROSS' category and press 'Enter.' Continue typing in all of the other clues until you are finished.
14. Next to the Across clues, enter the Down clues in a different column. Type in the word 'DOWN' and bold it. Then press 'Enter.'
15. Type in each number and clue for the 'DOWN' category, pressing 'Enter' after each one. Continue typing in other numbers and clues until they are complete.
16. Save your work by selecting 'File' and 'Save' from the toolbar. Name your crossword puzzle file and click 'Save.'

How to Use a Reference in a Different Worksheet in Excel

1. Open the Excel workbook with multiple worksheets that you want to use.
2. Click the tab at the bottom of the Excel window to select the worksheet in which you want to enter a formula.
3. Click an empty cell to insert a formula. Type an equal sign, an Excel function and an open parenthesis to begin building your formula. For example, if you want to add numbers from different worksheets, enter:=SUM(
4. Enter a cell number or another function from the current worksheet you want to work with. For example, if you are adding a number from cell A2 in the first worksheet to a number in cell A2 in the second worksheet, click or type cell 'A2.' Follow that with an operator. For example:=SUM(A2
5. Click a different worksheet tab at the bottom of the Excel application window. Click the cell you want to add to the formula next or type the cell address. Excel will insert the name of the worksheet you are referencing along with the cell number. For example:=SUM(A2 Sheet2!A2
6. Type a closing parenthesis when you are finished building the formula and press 'Enter.' Excel will return you to the original worksheet. Your complete formula might look like this:=SUM(A2 Sheet2!A2)

How to Work With Pivot Tables in Excel 2003

1. Open the Excel 2003 file that contains the data you want to work with.
2. Ensure that each column of data has a header at the top. If you do not have a header row, you can right-click the number on the left side of your data's top row and choose 'Insert' from the pop-up menu. Type headers for each column into the new row. You also need to remove any subtotal cells from the middle of the data field, as the PivotTable can't handle these types of cells.
3. Click the 'Data' menu at the top of the screen and choose 'PivotTable and PivotChart Report.' This launches the PivotTable wizard. Select the 'Next' button on the first screen of wizard.
4. Click the small box next to the 'Range' field and the wizard will minimize. Select the data that you want to include in the PivotTable. Click the top-left cell of your desired data range, then hold down 'Shift' and click the bottom-right cell. Don't include any subtotal cells on the bottom or the right side of the data area. Click the small box again to bring the wizard back up, and choose 'Next.'
5. Choose between placing the PivotTable on a new worksheet, or an existing worksheet. If you choose to use an existing worksheet, you will have to select the cell where you want the PivotTable to start. The table will expand down and to the right of your selected cell. Click 'Finish' to complete the wizard and create your PivotTable. You will see an empty PivotTable along with a field list that contains the names of all your column headers.
6. Click an item in the field list, then drag and drop it to one of the four areas of the PivotTable. The first area sits just above the actual PivotTable and acts as a global filter for the data in the table. Then, there are the two areas that make up the rows and columns of the PivotTable. Finally, there is a large area in the middle of the PivotTable that comprises the main data for the table. Each area can have multiple fields, as they will just stack onto one another automatically.
7. Rearrange the PivotTable by clicking the name of the field in the PivotTable and dragging that name to a different area. You can also drag and drop the field name off the PivotTable to remove it.
8. Click the arrows next to any of the field names on the PivotTable to filter the report. Remove check marks from any item you want to remove from the table, or add check marks to bring any item back onto the table. By using this feature on the page field area above the PivotTable, you can filter the entire table based on the elements in this list.
9. Double-click any data or subtotal cell in the PivotTable to see a list of all the source data that Excel used to create that number. The list will appear on a new worksheet that Excel adds to the workbook.

Thursday, January 26, 2012

How to Export the Outlook Calendar to Excel

1. Open Microsoft Outlook and allow the main screen to fully load. Click on the 'File' menu at the top of the page and select 'Import and Export' from the subsequent menu. Select 'Export to a file' from the next page and click the 'Next' tab at the bottom of the window.
2. Select 'Microsoft Excel' from the list of programs and click the 'Next' button. Select the 'Calendar' folder from the list of options on the next page and click the 'Next' button at the bottom of the screen.
3. Select a name for the calendar file and type it into the text box. This will be the name of the file used for the calendar in Excel. Click the 'Finish' tab at the bottom of the screen to share the calendar with Excel.

How to Use the Pivot Table Wizard in Excel

1. Start Excel and open the workbook file where you want to create the pivot table.
2. Format your data as straight lists of values. For example, you might have month, type of product, salesperson, or income.
3. Select the data you want in the pivot table.
4. Open the Data menu and select Pivot Table and Pivot Chart Report.
5. Click the radio button in the first section for 'Microsoft Excel list.'
6. Decide if you want just a table for your data, or if you also want charts. Then Click Next.
7. Verify that the range of cells you selected is listed in the Range field.
8. Click Next.
9. Decide whether you want the pivot table in a new or existing worksheet, then click Finish.

How to Turn on Worksheet Gridlines Row Headings Using VBA in Excel 2003

1. Launch Office Excel 2003, click the “Tools” menu, click “Macro” and click “Visual Basic Editor” window. Click the “Tools” menu and click “Project Explorer.”
2. Click “ThisWorkbook module” and copy and paste the following code to hide the gridlines and headings:Private Sub hideGridLines()
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
End Sub
3. Press “F5” to run the procedure.
4. Copy and paste the following code to your module to show the gridlines and headings:Private Sub showGridLines()
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayHeadings = True
End Sub
5. Press “F5” to run the procedure.

How to Open an MS Excel File Without Excel

1. Locate the Excel file you would like to open on your computer. It may be on your desktop, in a folder or in a portable disk drive. The file extension (portion of the file name after the period) will be '.xls' for spreadsheets created in Excel 2003 and earlier versions. For Excel 2007 and later spreadsheets, the extension will be '.xlsx.'
2. Right-click on the file name if you use a PC or press 'Ctrl' and click on the file name if you use a Mac. This will cause a menu, known as a context menu, to pop up with options.
3. Select 'Open with...' from the context menu. This will open a list of programs you can use to view the spreadsheet. In some versions of Windows, you may need to click 'Choose default program' to get the list of programs.
4. Find the recommended programs at the top of the list. If you have any other spreadsheet programs on your computer, they should be listed here. Microsoft Works, Apple's Numbers, Lotus 1-2-3 and OpenOffice Calc can all open Excel spreadsheets, although you may lose some formatting. If you have any of these programs, just click the program name and the computer will open the spreadsheet in that application.
5. Click 'Cancel' if you do not have any spreadsheet programs on your computer, or if you try the recommended programs and they do not work.
6. Open an Internet browser, such as Internet Explorer, Safari or Chrome, and visit Microsoft.com to download the free Excel Viewer software. (A direct link is listed under this article's Resources section.) This software will allow you to open and view an Excel spreadsheet, but you will not be able to edit the data.

Wednesday, January 25, 2012

How to Rename a Worksheet in Excel 2003

1. On your computer, locate the folder that contains the worksheet you want to rename.
2. Right-click on the worksheet file name. This will summon a small pop-up menu with options.
3. Click the 'Rename' option. A small box will appear around the text.
4. Type in the new name for your Excel 2003 worksheet.
5. Click outside of the text box. This will save the new name.

Tuesday, January 24, 2012

How to Delete Comments in Excel 2007

Deleting Comments
1. Open the Excel workbook and worksheet containing the comment to be deleted.
2. Highlight the cells that contain comments you wish to delete. You may need to hold down the 'Ctrl' key while clicking if you are selecting several cells that are not adjacent to each other.
3. Left-click once on the 'edit' menu at the top of the screen. Then select 'Clear,' followed by 'Comments.'
Deleting All Comments
4. Open the workbook and worksheet containing the comments to be deleted.
5. Left-click on the 'Find Select' button at the top of the screen in the editing tool bar and choose 'Comments.'
6. Left-click 'Clear' and then 'Comments' in the 'Edit' tool bar.

How to Protect Cells From Modification in Excel 2003

1. Open Windows and pull up your Excel spreadsheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Highlight the cells to be protected. Choose 'Cells' from the 'Format' menu. Select the 'Protection' tab. Check the box to the left of 'Locked'.
3. Go to 'Tools'. Hold the mouse on 'Protection'. Then click 'Protect Sheet'.
4. Enter a password if you want to use one. If you do not want a password, just click 'OK'.

Monday, January 23, 2012

How to Add a Background Color or Pattern to Cells in Excel 2003

1. Open Microsoft Excel on your computer. You should be able to locate the program by using the 'Start' button. If you can not find it there, use the 'Search' or 'Find' function, or just look for the icon on your desktop.
2. Highlight the cells that you want to emphasize. You can click and drag or click on each cell individually while holding down the 'Ctrl' button. Using the 'Ctrl' button and clicking will allow you to select non-adjacent cells.
3. Look for the tiny paint bucket icon on your tool bar. Use this icon to fill in a cell with a single color. Choose the color by hitting the down arrow on the right of the paint bucket. To select the last color you used or the default color, just click on the paint bucket icon. If you only need one color, you'll be done after clicking 'OK.'
4. Left-click on your cell selection to reveal a drop-down Formatting menu if you want to add a pattern.
5. Choose a color and then click on 'Add Pattern' to customize the color and pattern for the chosen cell(s). Then click 'OK.'
6. Repeat the above steps for all of the different color and pattern combinations that you need. When you want to remove a color or fill pattern, just highlight the cells, click the arrow next to the paint bucket icon and select 'No Fill.'

How to Have Excel Solver Give Only Whole Numbers

1. Go to the 'Data' tab in Excel, and click the 'Solver' button to launch the Solver dialogue box.
2. Click the 'Add' button under Constraints to launch the 'Add Constraints' dialogue box.
3. Enter the name of the cell in the 'Cell Reference' box, or highlight a range of cells with your cursor to fill this box automatically.
4. Click the drop-down menu in the middle of the 'Add Constraints' dialogue box, and select 'int' from the menu. The word 'integer' will appear in the Constraint box.
5. Click 'OK'

Sunday, January 22, 2012

How to Make Columns Wider in Excel

Click Drag
1. Place your mouse pointer on the top row where the column letters are displayed.
2. Move the cursor over the lines separating the columns and notice that the cursor becomes a cross.
3. Place the cursor over the line to the right of the column that you want to make wider. Be sure that it turns into a cross before going to the next step.
4. Click and drag the column over until it is the width that you want.
Use the Toolbar
5. Go to 'Format.'
6. Select 'Column' and then 'Width.'
7. Enter the width that you want the column to be in the 'Column Width' box.
8. Click 'OK.' The column's width will change.

How to Get the Row Number of a Cell in VBA

1. Open the Visual Basic Editor (VBE) by clicking on the 'Developer' tab and then clicking on 'Visual Basic.'
2. Click on the 'Insert' tab and then click 'Module.' This opens a blank window.
3. Copy and paste the following code into the blank window:Sub myActiveRow()myRow=ActiveCell.RowMsgBox myRowEnd Sub
4. Press 'F5' to run the routine.

Subscribe to: Posts (Atom)