Excel

Browse » Home » All posts

Thursday, December 20, 2012

How to Use a Saved Template for Pivot Charts

1. Click 'Start' and 'All Programs.'
2. Navigate to the 'Microsoft Office' folder, click it once to display the folder contents and then click 'Microsoft Excel' to launch the program.
3. Click the 'Office' button in the upper left corner, and then select 'New' to create a new document. Or click 'Open' to locate and open an existing Excel spreadsheet.
4. Click and hold your mouse on the uppermost cell containing your target data, and then drag the mouse until all of your target data has been selected.
5. Click the 'Insert' tab at the top of the screen. Then click on the 'PivotTable' icon, and select the 'PivotChart' option.
6. Select whether you would like the PivotChart to be inserted into the existing worksheet or into a new worksheet. Click 'OK.'
7. Locate the 'PivotChart Tools' section at the top of the screen, and click the 'Design' tab in this section.
8. Click the 'Change Chart Type' icon at the top of the screen.
9. Select the 'Templates' option in the left column, select your saved template and click the 'OK' button.

How to Create a Calendar in a Pull

Create a Calendar Using Excel 2007
1. Enable the 'Developer' tab. The Excel 'Developer' tab is inactive by default, so you may need to activate it to create a pull-down menu. To activate the 'Developer' tab, click the 'Office' button in the top-left corner of Excel. Locate and click the 'Excel Options' button along the bottom right of the menu. Select and click the 'Show Developer in Ribbon' check-box from the pop-up menu, then click 'OK' to exit.
2. Insert a pull-down calendar. Click the 'Developer' tab from the Excel main menu ribbon. Click 'Insert' to display a menu of options and then click the 'More Controls' icon from the ActiveX controls section. The 'More Controls' icon is the last icon in the second row. It appears as a hammer and wrench crisscrossing in an 'X' pattern. A long list of additional controls will appear, so scroll down until the 'Microsoft Date and Time Picker 6.0' option appears. Click the option and then click 'OK' to return to your spreadsheet.
3. Draw, position and size the pull-down calendar. Draw a rectangle the size you desire for the calendar. Use the resize handles on the drop-down box to adjust the size if necessary. To move the calendar to another location on the Excel spreadsheet, click inside the calendar, hold the mouse button down and drag it to a new location. Resize the cell to fit the calendar by double-clicking the top margin lines.
4. Test the calendar. Deselect the 'Design Mode' tab in the main menu ribbon by clicking it once. Click the black triangle to the right of the calendar date, and your calendar will appear. If you need to make additional adjustments, go back to 'Design Mode.'
Link Calendar to Another Cell
5. Select the link cell. Select the cell you want to update when the date on the calendar changes, such as H9. In the formula bar, type in '=B5' (or whatever cell the calendar is in). Then click anywhere outside the cell to deselect it.
6. Create a link between the calendar and the update cell. Click 'Design Mode' and then click on the drop-down calendar to select it. Select 'Properties' option from the options box next to the 'Design Mode' button and locate the 'Linked Cell' option, and in the 'Linked Cell' information box, type in 'H9' (or whichever cell you select for the date update). Close the 'Properties' box and click on 'Design Mode' to deselect.
7. Activate the update. Change the date on the pull-down calendar. The update will appear in the linked cell.

Wednesday, December 19, 2012

How to Make a Dashboard in Excel

1. Open an Excel workbook containing data you want to manipulate. Create a new worksheet, inserted at the front of the workbook, and call it 'Dashboard.'
2. Click 'File' and then 'Options.' Click 'Customize Ribbon.' Tick the checkbox next to 'Developer' and click 'OK.' This enables your form control. Forms can be a vital component in an Excel dashboard.
3. Insert form items by clicking 'Developer,' 'Insert' and selecting a form option. With forms, such as a combo box, you can 'Format Control' and choose an output cell. That cell, in the case of a combo box, will show a number representing your selected option. For a combo box displaying the days of the week, for example, the output cell (hidden) will contain a value between '1' and '7.' This number can then be used by a graph or macro to display data only for that day.
4. Create a linked cell to link to other locations. This is useful if, for example, you have a totaled set of figures on the second worksheet and want to create a graph on page one but also show those totals. Type out 'Total' and 'Subtotal,' for example, on the first page, and then click the cell to the right of 'Total.' Type an equal sign ('='), then click the second worksheet, select the 'Total' cell figure and press 'Enter' to create a link to that cell. You can change the data on that page and it will still update on the first page.
5. Use color formatting to improve the look of your dashboard. This is useful not just for block color but also for conditional formatting. For instance, select a table of data and click 'Home,' 'Conditional Formatting,' 'Color Scales' and then 'More Rules.' You can select a different color scale and edit the figures so that high figures, for example, are displayed as red and low figures as green. Use these basic techniques to start creating your dashboard.

How to Make a Graph in MS Excel That You Change Every Day

1. Open the Microsoft Excel 2010 spreadsheet that contains the data you want to make into a dynamic graph.
2. Click anywhere in your data, then click the 'Insert' tab at the top of the screen. Click the chart button that corresponds to the type of graph you wan to use, then select your desired graph from the popup menu to make it appear on the spreadsheet.
3. Click the 'Formulas' tab at the top of the screen, then click the 'Define Name' button in the middle of the ribbon. Type a name for the first data series in your chart. This is normally the same text as appears in the header above the data series on your spreadsheet, but can be anything you desire.
4. Place your cursor into the 'Refers to:' box. Delete whatever is in the box, then type in '=OFFSET(' and click on the first cell in the column or row where the data for this series is located. Type in a comma, the number one, another comma, then number zero, then another comma. Then type in 'COUNTA($A:$A)-1)' but change each 'A' after the dollar signs to the column letter or row number where your data is located.Your final formula should look similar to this:=OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)Click 'OK' to close the Define Name window. Click the 'Define Name' button again and repeat the entire process for each data series in your graph.
5. Right-click on your graph and choose 'Select Data' from the popup menu. Click on your first data series, listed on the left side of the window that appears, then click 'Edit.'
6. Place your cursor into the 'Series values' text box and press backspace several times to delete everything after the exclamation point. Type in the name of the range you created for this set of data.
7. Click 'OK' to go back to the Select Data Source window. Click on a different data series and repeat the process. Continue until you have entered your names for each data series, then click 'OK' to close the Select Data Source window. Your chart now changes as you add new data each day.

How to Make an Address Book on a Computer

1. Open up Microsoft Excel on your computer. You will see that a spreadsheet opens up with predetermined rows and columns. You will use these rows and columns to set up your address book.
2. Add a title to the top of the spreadsheet. This will help you remember what information is in a particular address book. You may need to set up different address books for various purposes--for example, one for members of a book club and one for family members. Having a clear title will help you keep everything organized. Make the title bold and increase the size of the font, using the tools on the top toolbar.
3. Skip a couple of lines after the title and enter headings for the columns in the address book. The following column headings will be very helpful: Name, Address, City, State, ZIP Code, Phone Number, Fax Number and E-mail Address. Type in these headings into columns A to H, respectively. It is very important that each item is entered into a separate column.
4. Center and bold the column headings. To do this, click on the row number to the left of the row of headings. This will highlight, or select, the entire row. And, then, use the bold and center tools on the top toolbar.
5. Enter the contact information in each column. Information for each contact should be entered on a separate row. Depending on how many contacts you have, this could take some time. But, remember, you will only need to do this one time. In the future, you will just have to add new contacts and update information. If you do not have certain items of information for certain contacts, leave the cell blank. Remember to use the two-letter state abbreviations that the post office requires.
6. Format the text in the ZIP Code column to make all of the ZIP codes look the same. Highlight all of the text in the column, except for the column heading. Right-click on the highlighted cells and select 'Format Cells.' Click on the 'Number' tab, and then click on 'Special' in the 'Category' box. Click on 'Zip Code' or 'Zip Code 4' in the 'Type' box. Click 'OK.' This will make all of the ZIP codes look the same.
7. Format the text in the Phone Number column so that all of the phone numbers in the column look the same. Highlight the text in the Phone Number column, except for the column heading. Right-click on it and select 'Format Cells.' In the 'Number' tab, select 'Special' from the 'Category' box. Click on 'Phone Number' in the 'Type' box. Click 'OK.'

How to Draw a Histogram With MS Excel

1. Type your data into column A of your worksheet. For example, click on cell 'A1' and type your first data item, click on cell 'A2' and type your second data item. Continue down the column, clicking on cells and typing your data in until all of your values are on the worksheet.
2. Place your bin widths into column 'B.' Type the top value of each bin, starting in cell B1. For example, if your bin widths were 0 to 4 feet, 5 to 6 feet and 6 feet to 8 feet, type '4' into cell B1, '6' into cell B2 and '8' into cell B3.
3. Click on the 'Data' tab, then click 'Data Analysis.'
4. Click on 'Histogram,' then click on 'OK.'
5. Type the range of your inputs ito the 'Input Range' box. Your inputs are the values you entered into column A. For example, if your inputs are in cells A1 to A10, type A1:A10.
6. Click on the 'Bin Range' box and type the location of the bin ranges. For example, type B1:B3 to indicate the bin ranges are in cells B1 to B3.
7. Click 'New Workbook' under 'Output Options' then click on the 'Chart Output' check box.
8. Click on 'OK' to create the histogram.

Tuesday, December 18, 2012

How to Insert an Excel Spreadsheet to a VB Form

1. Open Microsoft Excel and type 'A' in 'A1,' 'B' in 'B1,' 'Column A' in 'A2,' and 'Column B' in 'B2.' Save your spreadsheet to 'C:\' as 'ExcelFile.xlsx.'
2. Open Microsoft Visual Basic 2010 Express, click the 'File' menu and select 'New Project.' Click 'Installed Templates,' select 'Windows Forms Application' and click 'OK.'
3. Press 'Ctrl' 'Alt' 'X' to open the 'Toolbox' window. Double-click 'DataGridView' to add a new Data Grid View control to 'Form1.' Double-click 'Button' in 'Toolbox' to add a new button to 'Form1.'
4. Double-click 'Button1' to open the 'Form1.vb' module. Type the following above 'Public Class Form1':Imports System.Data.OleDb
5. Type the following inside 'Private Sub Button1_Click' to declare a 'DataSet' and define the Excel connection:Dim ds As New DataSet()Dim connectionString As String = 'Provider=Microsoft.ACE.OLEDB.12.0;' _'Data Source=C:\ExcelFile.xlsx ;' _'Extended Properties=Excel 12.0;'
6. Type the following to connect to the 'ExceFile.xlsx' file and fill the 'DataSet':Dim excelData As New OleDbDataAdapter('SELECT * FROM [Sheet1$]', connectionString)excelData.TableMappings.Add('Table', 'ExcelSheet')excelData.Fill(ds)
7. Type the following to display the spreadsheet in your Data Grid View:Me.DataGridView1.DataSource = ds.Tables(0)Me.Refresh()Press 'F5' to run your program and press 'Button1' to import the Excel spreadsheet.

How to Tell If a Worksheet Is Protected in Microsoft Excel 2003

Single Worksheet
1. Click on the 'Tools' menu.
2. Select 'Protection->Protect Sheet.'
3. Read the dialog box. If 'Protect worksheet and contents of locked cells' is checked, the sheet is protected.
Workbook (All Worksheets)
4. Open the workbook. If you cannot open the workbook, it is protected at the file level.
5. Add another worksheet to the workbook. If you can't add another sheet, the workbook is protected.
6. Resize the Excel window. If you can't, the workbook is protected.

How to Fill a Series in Excel 2007

1. Type out a series of numbers in the cells you want the start your series. For example, type '1' in cell A1, '2' in cell A2, then '3' in cell A3.
2. Click on the first number in your series, then press 'Shift' and using the arrow buttons highlight the remainder of the series you typed. In the example, highlight cells A1 through A3
3. Click and hold the click in the lower, right-hand corner of the highlighted cells, then move the mouse down to highlight the empty cells where you want to continue your series. In the example, click and hold on the bottom right of cell A3, then drag the mouse down to cell A6. Microsoft Excel will fill cell A4 in with '4,' A5 with '5' and A6 with '6.'

How to Make Custom Receipts

Excel 2010
1. Select the 'File' tab and select 'New.' Click 'Receipts' in the left task pane. Review the receipts that appear. Click a receipt to see a preview in the right task pane. Download a receipt by clicking the receipt image and the 'Download' button in the right task pane. The template opens in Excel 2010.
2. Update the logo section by right-clicking on the default logo and selecting 'Change Picture.' Browse your PC for the logo you want to use. Click the logo and the 'Open' button. Your logo replaces the default logo.
3. Highlight the default text and type your customized information it. This includes the address, product details, and customer messages. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents template gallery. Type 'Receipt' in the search box. Click 'Search Templates.' Review the templates that appear. Download a receipt by clicking the 'Use This Template' button. The template opens in Google Documents.
5. Delete the default logo by clicking the logo image and pressing the 'Delete' key on the keyboard. Add a new logo by clicking 'Insert' and 'Image.' Browse your PC for the logo you want to use. Click the logo and the 'Open' button. Your logo replaces the default logo.
6. Highlight the default text and type your customized information it. This can include the address, product details, and customer messages. Save your changes by clicking the 'Save' icon on the menu.
OpenOffice
7. Access the OpenOffice template gallery. Type 'Receipt' in the search box. Review the templates that appear. Download a receipt by clicking the 'Use This' button. The template opens in OpenOffice Calc.
8. Delete the default logo by clicking the logo image and pressing the 'Delete' key on the keyboard. Add a new logo by clicking 'Insert' and 'Image.' Select 'From File.' Browse your PC for the logo you want to use. Click the logo and the 'Open' button. Your logo replaces the default logo.
9. Highlight the default text and type your customized information it. This can include the address, product details, and customer messages. Save your changes by clicking 'Save' on the menu.

Monday, December 17, 2012

How to Delete Every Other Row of an Excel Spreadsheet

1. Open the Excel Visual Basic Editor. With your spreadsheet open in Excel, click 'Tools' from the menu near the top of your screen, then select 'Macro' and 'Visual Basic Editor.' If you use Excel 2003 or older, go on to the next step.Excel 2007 users will notice that the previous command does not exist in the current version of Excel, and that no macro editing tools appear by default. Turn on access to these tools by clicking the Office button at the top-left corner of your screen, then clicking the 'Excel Options' button at the bottom of the menu. Locate the heading labeled 'Top options for working with Excel' in the resulting dialog box, then check the box next to the 'Show Developer tab in the Ribbon' label. Click 'OK.' Select the Developer tab, then click the 'Visual Basic' button.
2. Add macro code. Click 'Insert' from the menu near the top of the Microsoft Visual Basic editor window and choose 'Module.' Copy all of the code listed below, then paste it into this module:Sub Delete_Every_Other_Row()
' Dimension variables.
Y = False ' Change this to True if you want to
' delete rows 1, 3, 5, and so on.
I = 1
Set xRng = Selection
' Loop once for every row in the selection.
For xCounter = 1 To xRng.Rows.Count
' If Y is True, then...
If Y = True Then
' ...delete an entire row of cells.
xRng.Cells(I).EntireRow.Delete
' Otherwise...
Else
' ...increment I by one so we can cycle through range.
I = I 1
End If
' If Y is True, make it False; if Y is False, make it True.
Y = Not Y
Next xCounter
End Sub
3. Run the macro. Return to your spreadsheet without closing the Visual Basic editor. Highlight the rows for which alternating row should be removed. In Excel 2003 and earlier, click the 'Tools' menu option, then select 'Macro', followed by the 'Macros' option. In Excel 2007, select the Developer tab, then click 'Macros.' To run the macro in either version, select the macro labeled 'Delete_Every_Other_Row', then click 'Run.' Every even-numbered row of your spreadsheet deletes.

Sunday, December 16, 2012

How to Remove Borders From Cells in Microsoft Excel 2003

1. Open the file that you wish to work on. Scroll to the “File” tab on the command bar and select “Open.” Then browse to the desired Excel file and click “Okay” to open it.
2. Access the cell with the borders that you wish to remove. You can activate the cell by scrolling to the cell of choice and left-clicking on it.
3. Remove the border from the cell. Scroll to the “Format” tab on the command bar and select “Cells.” Under the cells properties box, left-click on the “Border” tab.
4. Set the border presets. There will be box under this tab labeled “Border Presets.” You will need to left-click on the “None” box to remove a border from the cell. Then simply click “Okay” to implement the changes.

How to Create a Spreadsheet Template in Excel 2003

1. Open a new Excel document.
2. Make your spreadsheet. Include all the formatting you would like for your template. Add the fonts, cell colors, macros and anything else you would like.
3. Include only the information that needs to be on every spreadsheet. For example, if you are making a template for a budget, add the headings but not the entries for each transaction.
4. Go to File > Save As. Below the box where you name the file, there is a drop-down menu with file types. Save your file as a '.xlt' Template file. Name the file and click Save.
5. Use the template. Go to File > New. This will bring up a few options, including one for the template you made.

How to Create a Form Using Mircosoft 2007 Excel

1. Launch the Microsoft Excel 2007 program and open a blank spreadsheet. Click the 'Start' button and then 'Excel.' If you do not have Excel 2007, a free trial of Office may be downloaded from the Microsoft site (see Resources section). If a blank spreadsheet does not open automatically, click the 'Microsoft Office' button and then 'New.' Double-click the 'Blank Spreadsheet' icon to open a blank document.
2. Make certain the 'Developer' tab is available in the 'Ribbon.' Click the 'Microsoft Office' button and select 'Excel Options' to launch a separate dialogue window. Click the 'Show Developer tab' check box in the 'Top Options' section of the 'Popular' category. Click the 'OK' button.
3. Add form fields to the Excel 2007 spreadsheet. Go to the 'Developer' tab and locate the 'Controls' section. Click 'Insert' and select the desired form tool from under the 'Form Controls' section, such as 'List Box.' Click in an area of the spreadsheet that you want to add the form tool. The form tool will appear.
4. Adjust the form tool properties. Right-click the form tool and select the 'Format Control' option to launch the 'Properties' dialogue box. Depending on the type of form tool you selected, use the options in the 'Properties' window to change the details. Change the size of a form tool by clicking on it and using the small circles on the border to drag it to a desired size. To change the location of a form tool, click once on the border and drag it to a new area while holding down the mouse button.
5. Repeat steps 3 and 4 for each form tool you want to add to the spreadsheet. Save the form using the 'Save As' option under the 'Microsoft Office' button. Select a folder in which to save the Excel 2007 form using the arrows in the address bar of the 'Save As' dialogue box. Type a name for the form in the 'File name' field and click the 'Save' button to complete the process of creating a form using Microsoft 2007 Excel.

How to Add Data Labels to a Pie Chart

Adding Data Labels to a Pie Chart in Excel 2007 and Excel 2010
1. Start the Microsoft Excel program and open the worksheet containing the pie chart to which you wish to add data labels.
2. Select the chart by clicking on it. A translucent ribbon will appear at the edges of the chart, indicating its selection.
3. Click on the 'Layout' tab in the toolbar above the chart to display the 'Layout' ribbon.
4. Locate the 'Data Labels' button and click on the downward arrow to reveal a drop-down list of options.
5. Select the desired location for the labels. This will add data labels on your pie chart.
Adding Data Labels to a Pie Chart in Versions Prior to Excel 2007
6. Select the pie chart by clicking on it.
7. Click on 'Chart' from the toolbar above and select 'Chart Options.' A dialog box will pop up.
8. Select the 'Data Labels' tab from this box.
9. Select what you wish to depict on the chart from the five options: 'Series name,' 'Category name,' 'Value,' 'Percentage' and 'Bubble size.'
10. Click 'OK.' This will add the desired data labels to your pie chart.

How to Construct a Histogram in Microsoft Excel

1. Click on the 'Data' tab in Excel 2007 or the 'Tools' tab in Excel 2003.
2. Click on 'Data Analysis.'
3. Choose 'Histogram' from the list box, then press 'OK.'
4. Tell Excel where your data is by entering a range in the 'Input range' box. For example, if your data is in column A2 to A11, type 'A2:A11' into the box. If you have entered your own bin values (a range of data for the columns), enter the location of the data in the 'Bin values' box in the same format. For example, if your bins are located in B2 to B5, enter 'B2:B5' in the bin values box.
5. Check the 'Chart Output' box, then press 'OK.' Excel will insert a histogram into the spreadsheet.

How to Get Stock Quotes in Excel

1. Open Microsoft Excel. First, select 'Start' from the main operating system menu. Next, choose 'Programs.' Then, click on 'Microsoft Office' in the programs menu. Finally, select 'Microsoft Excel' from the Microsoft Office menu.
2. Click on the 'Data' menu from the Microsoft Excel main menu screen. Then, choose 'Get External Data' from the data menu. A dialog box will appear with a list of established data sources. Finally, choose the data source labeled 'Investor Stock Quotes.'
3. Select the cell in the spreadsheet for the stock quote information input or choose the 'Create New Worksheet' option to place the stock quote in a new worksheet. After selecting either option, select 'OK' from the dialog box.
4. Type the stock ticker symbol into the next Microsoft Excel dialog box. If the user wants to update the stock quote in the future, choose 'Use this value/reference for future refreshes.' Also check the second check box if you would like the information to refresh on its own.
5. Save the Microsoft Excel file for future use. Select 'Save' from the main file menu, name the file and choose the appropriate place on the computer hard drive to save it.

Saturday, December 15, 2012

How to Do a Pamphlet in Excel

1. Click on the 'File' tab and select 'Print.' Set the layout of the spreadsheet to 'Landscape' and set the margins to '0.25' on all four sides of the page. This will give you the maximum printable area on your pamphlet.
2. Click on the “View” tab and select 'Page Layout' on the left side of the Ribbon. This will change the view of your spreadsheet to one with page borders set. In this view you will also be able to set the width of columns in inches or fractions of an inch which is essential to using Excel as a page layout program.
3. Click on the box at the upper left hand corner of the worksheet area between the two rulers. It has a small triangle pointing down and to the right; this will select all cells in the work sheet.
4. Define your layout grid by clicking on the gray column headers at the top of the worksheet area and set your column widths to a quarter of an inch. Continue the process by clicking on the gray row borders at the left edge of the worksheet area and set your row heights to one fifth of an inch.
5. Switch back to the 'Home' tab. Select cells and use the 'Merge Center' button in the 'Alignment' section of the tab to merge cells to suit your design. For example, if you wanted to center a title on the left side of your brochure you'd select cells A1 through U2 and click 'Merge Center' to make a large box to enter your title.
6. Override the centering of text in a merged group of cells by using the text alignment tools next to the 'Merge Center' button and set the fonts to reflect what you want; larger fonts for titles and smaller fonts for text.

How to Convert the First Letter to an Uppercase in Excel

1. Decide if you want the first letter of every word to be capitalized ('Pete Is Great'), or just the first letter in the cell ('Pete is great'). In this example, assume that the original text ('pete is great' - note no capitals) is in cell A1, and you want the text capitalized in cell B1.
2. Enter the following Excel code into cell B1 if you want every word to be capitalized:=PROPER(A1)This will give the output 'Pete Is Great'.
3. Type the following Excel code into cell B1 if you only want the first letter of the cell capitalized:=UPPER(LEFT(A1,1))LOWER(RIGHT(A1,LEN(A1)-1))This will give the output 'Pete is great'.The 'UPPER(LEFT(A1,1))' part of the function tells Excel to return the left-most character in cell A1 in upper case. The 'LOWER(RIGHT(A1,LEN(A1)-1))' tells Excel to return all but the left-most character as lower case.

How to Build Drop

1. Scroll to the area of the spreadsheet where you want to create the drop-down list's contents. You build a drop-down list menu in Excel from data typed into any set of cells. If you want this to be inconspicuous to the Excel user, do not use the beginning of the spreadsheet. Instead, you can scroll to the right several columns or scroll down many rows to a less obvious part of the spreadsheet.
2. Type the list you want in your drop-down list menu. You can either type the list as a series of vertical cells, all in the same column, or as a horizontal list, all in the same row. For example, if you selected cell P100 as the starting point for your list, you can type each list item into P100, P101 and P102 (or into P100, Q100 and R100) and continue the list for as long as necessary.
3. Click in the cell or cells where you want your drop-down list menu to appear, based on the typed list elsewhere in the spreadsheet. If you want multiple cells to have the menu, drag your mouse over all of them so they are all selected.
4. Click on the 'Data' menu and the 'Data Tools' section. Then, click 'Data Validation' and select 'Data Validation.' In versions of Excel prior to 2007, click the 'Data' menu and select the 'Validation' option. A pop-up box will appear.
5. Click the drop-down menu on the 'Settings' tab of the pop-up window. Select the 'List' option.
6. Click in the 'Source' field.
7. Drag your mouse over the cells in your spreadsheet that contain the list of items that you typed earlier.
8. Type the 'OK' button to complete the drop-down menu setup.

How to Use Excel Charts

1. Launch Microsoft Excel 2010.
2. Open a file with some data already present in the cells or populate a blank sheet with some data that will be used for the chart. Include data labels for the rows and columns by typing descriptive names for the data in the next cell above the first entry in each column and in the next cell to the left of each row of entries.
3. Click in the cell above the first row label and to the left of the first column label and hold the mouse button down. Drag the mouse across the data diagonally until it is pointed at the lower right entry in the last row and last column and release the mouse button. This should highlight all the data that will be included in the chart.
4. Click the 'Insert' tab and click the small arrow at the bottom of any of the chart types listed in the 'Charts' group. Click the 'All Chart Types' button at the bottom of the list that appears.
5. Click on the desired chart type and click 'OK' to create a chart.
6. Click the 'Design,' 'Layout' or 'Format' tabs under the new 'Chart Tools' menu tab that appeared once the chart was created to access additional features and options.

How to Copy Vertically Paste Horizontally in Excel

1. Open the document in Microsoft Excel and highlight the cells you want to copy.
2. Click the 'Home' tab on the Microsoft Office ribbon and click 'Copy.'
3. Click on the cell where you want to paste the data.
4. Click the pull-down menu next to 'Paste' on the 'Home' tab to view a list of paste options, then click 'Transpose.' The copied data is pasted into the highlighted cell and its adjacent cells with the rows and columns reversed.

Friday, December 14, 2012

How to Calculate Descriptive Statistics Using Analysis ToolPak

1. Open Excel 2007 and add the numbers for which you want to calculate descriptive statistics in the first column. For example, add the numbers 210, 110, 50, 50, 70 and 80 in A2, A3, A4, A5, A6 and A7 cells of Excel.
2. Click on the 'A9' cell. This is the cell where you will calculate the descriptive statistics using Analysis ToolPak. Please note, you don't have to select 'A9' cell for the descriptive statistics calculation; any cell under your chosen values can be selected.
3. Click on the 'Data' tab and then 'Data Analysis' found on the top right-hand side of the Excel spreadsheet. A window titled 'Data Analysis' will pop open.
4. Click on the 'Descriptive Statistics.' A window titled 'Descriptive Statistics' will pop open. In the Input Range of window, select and drag from A2 to A7 cells. In the Output Range of Window, select A9 cell.
5. Click on the 'Summary Statistics' of the window. A check mark will appear next to Summary Statistics. Click 'OK.'
6. Thirteen descriptive statistics have been successfully calculated starting from 'A9' cells. In this example, the following will appear as your calculated values.Mean 96.66666667
Standard Error24.17528582
Median 75
Mode 50
Standard Deviation59.21711464
Sample Variance3506.666667
Kurtosis 3.549458572
Skewness 1.854360629
Range 160
Minimum 50
Maximum 210
Sum 580
Count 6

How to Make a Log with Microsoft Excel 2003

How to Make a Log with Microsoft Excel 2003
1. Choose the column headings you wish to use and determine the number of columns you will need. If your log requires the headings 'Date,' 'Time,' 'Comment' and 'Initials,' you will need 4 columns.
2. Open Microsoft Excel 2003 and type your column headers in the first row. For this example, type 'Date' in Cell A1, 'Time' in Cell B1, 'Comment' in Cell C1 and 'Initials' in Cell D1. Highlight those 4 cells and choose the format and font you wish to use for the text.
3. Highlight columns A through D and right click somewhere on the highlighted cells. Select 'Format Cells' from the menu that pops up and activate the 'Borders' tab. Click the 'Outline' and 'Inside' buttons under the 'Presets' subhead. Click 'OK.'
4. Adjust the row height to meet your needs by clicking 'Format' on the menu bar, than 'Row' and then 'Height...' The default height is '12.75' which yields approximately 50 lines per page. This may be too small for some people's handwriting, so adjust this number to meet your needs and click 'OK.' It may take some experimenting to get it just how you want it. You may use this same method to re-adjust later.
5. Change to the page break view by clicking 'View' on the menu bar and then 'Page Break Preview.' Once there, only the first 1 or 2 rows will be active and there will be a blue border around them. Click and drag the bottom blue border downward until a page divider appears and 'Page 2' appears in the highlighted area below the divider. You may have to release the border and drag again to get this to appear. Click and drag the bottom blue border upward until it aligns with the divider.
6. Adjust the column widths to meet your needs by clicking and dragging the dividers between the column headers. For example, you can change Column A's width by clicking the divider between 'A' and 'B' and dragging it in one direction or the other. Adjust the columns as wide as necessary, but do not exceed one page in width for the whole table. You will know if you have exceeded 1 page if a dotted page divider appears with 'Page 2' on the right side of it.
7. Save the file. Print as many copies as you need. When you run out of copies, you can simply open the file and print out more.

How to Calculate Probability Using Excel

1.
Go to Start>Programs>Microsoft Office>Microsoft Excel. If Excel has been used recently, simply go to Start>Microsoft Excel.
2.
Create two columns, one entitled 'Numeric grades' and the other 'Probability of getting each grade.'
3.
List the grades from 50, 60, 70, 80, 90 and 100 in cells A2 to A7.
4.
List the probabilities associated with each grade from cells B2 to B7. List the numbers as follows: 0.05, 0.1, 0.4, 0.3, 0.1 and 0.05.
5.
Enter '=Prob(A2:A7,B2:B7,70,100)'. This formula for probability isolates the numeric range of numbers (A2:A7), the probability of getting each grade (B2:B7), the lower range for which the probability is needed (70) and the upper range for which the probability is needed (100). In short, the formula answers the question: What are the chances of someone getting a grade between 70 and 100?
6.
Click on the '%' icon to convert the answer (0.85) to a percentage. The resulting answer is 85%. There is an 85% chance that the grade will be between 70 and 100.

How to Link an Excel Cell to a Word Document

1. Open the Excel document within Excel and right-click on the cell to be linked to the Word document.
2.
Select 'Hyperlink' from the cell menu and locate the Word document to link it to and then click 'OK.'
3.
Save the Excel spreadsheet and then click on the cell to open up the linked Word document.

Thursday, December 13, 2012

How to Make a Parabola on Excel

1. Enter a series of x values into the cells in a column, entering multiple values on either side of the vertex. If you are unsure of the vertex, enter a wide range of x values.
2. Enter an equal sign followed by the formula being graphed into a cell next to the top x value, then click on the lower right corner of the cell and drag down to the cell next to the bottom x value to copy the formula automatically. Excel will not display the formula in the cells, it will show the results.
3. Highlight the values entered in both columns. If you were not sure of the vertex, find the point where the change in y reversed direction then choose cells on either side of that point. For example, if y was getting larger for five cells, then smaller for the remaining cells, the vertex is between the fifth and sixth values. The more cells chosen, the more accurate your graph will be.
4. Click 'Insert' from the menu, then select 'Chart' to launch the chart creation wizard.
5. Select 'XY Scatterplot' from the wizard.
6. Adjust the look of your graph to meet your desires by following through the wizard. You will be given the option to customize the labels, the colors and the grid lines of the graph, among other options.
7. Click finish to create the graph.
8. Click on a corner of the graph and move your mouse to adjust the size of the graph.

How to Convert a Mac Date System to Excel

1. Open the Excel file that contains the cells with incorrect dates.
2. Click on any empty cell. Type '1462' into this cell, as this signifies the number of days between the two date systems. Right-click the cell and choose 'Copy.'
3. Select the cells that contain the incorrect dates. To select multiple cells, click and hold on the top left cell in a range and then drag your mouse to the bottom right cell. Hold the 'Ctrl' button to select ranges that aren't adjacent to each other.
4. Right-click on any of the selected cells. Move your mouse over 'Paste Special' in the first pop-up menu that appears, and then click on 'Paste Special' that appears at the bottom of the second pop-up menu. This brings up the 'Paste Special' window.
5. Click the radio button next to 'Add' in the 'Operation' area of the window and click 'OK.' The selected dates will shift up by 4 years and a day.

Wednesday, December 12, 2012

How to Insert a PDF Into Excel

1. Open Excel 2007 and select the 'Insert' tab. Select 'Object' from the 'Text' group. The Object dialog box appears. Click the 'Create from File' tab. Select the 'Browse' button. Search your files to locate the PDF that you plan to insert. Click the file and select 'Insert.' Click 'OK.' Excel inserts the PDF into your document as an image.
2. Review the image of the PDF in your workbook. Open the PDF by right-clicking the image and select 'Adobe Document Object.' Select 'Open.' The PDF opens with Adobe in a separate Adobe window.
3. Save your changes by clicking the 'Save' icon the Quick Access Toolbar. The newly attached PDF is inserted as an attached image in your Excel workbook.

How to Subtract Cells in Excel

Create a Formula
1. Enter your data. For the purpose of this example, type the number 34 in cell A1 and the number 15 in B1.
2. Choose the cell where you want your results to appear. Use C1, for instance.
3. Place an equal sign (=) in C1. The equal sign always precedes formulas in Excel and goes into the cell where your results will be displayed.
4. Click on cell A1. Clicking on this cell automatically places 'A1' in cell C1.
5. Type a minus sign (-) in cell C1.
6. Click on cell B1. Clicking on this cell automatically places 'B1' in cell C1.
7. Press the 'Enter' key on your keyboard, or click on the check mark on the tool bar, to see the result of your calculation. Excel performs the calculation instantly and cell C1 displays the answer, 19. Notice that the formula appears in the formula bar when you click on cell C1.
Subtract Numbers using the SUM Function and the Autosum Button
8. Type a number into A1. For instance, type the number 10.
9. Type a number into B1, preceded by the minus sign. For instance, -8.
10. Click on the cell where you want the answer displayed, like C1.
11. Use the SUM function. Type =SUM(A1, B1) into cell C1. Click the check mark on the tool bar or press the 'Enter button' to display the answer in C1.
12. Use the 'Autosum' button, which automates the SUM function. Enter your data and then click cell C1. Click the 'Autosum' button on the toolbar to display the answer, 2, in cell C1.

How to Convert Excel 2007 to Excel 2002

Instructions
1. Complete your spreadsheet. Save as usual by clicking on the 'Office' button and selecting 'Save' from the drop-down menu. This will open a pop-up window. Type your file name in the 'File Name' box and click 'Save.' This will save your spreadsheet as an Excel 2007 file with the extension '.xlxs' and ensure that you have access to the original document.
2. Use the 'Save As' option to convert your Excel 2007 file to one that is compatible with earlier versions of Excel. Click on the 'Office' button again to reveal the drop-down menu.
3. Select 'Save As' from the drop-down menu. This will reveal a sidebar menu with several format options. From this menu, select 'Excel 97-2003 Workbook.' In the pop-up window that opens, type in your file's name and click 'Save.' This will convert your spreadsheet into an Excel file with the extension '.xls.'

How to Copy an Excel Worksheet

1. Open Microsoft Excel and the file you want to change.
2. Open the Edit menu and select Move or Copy Worksheet.
3. Click the Create a Copy option in the dialog box.
4. Select OK to create a copy.
5. Rename your newly copied worksheet by double-clicking its tab at the bottom of the Excel window.

How to Remove Characters in Excel 2007

1. Open your spreadsheet and select all cells from which you want to remove the character string.
2. Click the 'Home' tab and click 'Find Select' in the 'Editing' group. Click 'Replace' to open a Find and Replace dialog box.
3. Type the character or string of characters that you want to eliminate in the 'Find what' field.
4. Type the new characters that you want to insert in place of the removed characters in the 'Replace with' field. If you simply want to delete the characters, then don't type anything in this field.
5. Click the 'Find Next' button if you want to perform the search-and-replace function manually.
6. Click 'Replace All' to remove all instances of the specified characters in the selected cells.

How to Get Started With Excel VBA

Record a Macro
1. If you have ever recorded a macro, you are already using VBA. The Visual Basic editor translates your keystrokes into VBA commands. The resulting code is inefficient, but it can help you get familiar with VBA syntax and commands.
2. In Excel, record a simple macro. In Office XP, select Macro, Record New Macro from the Tools menu. (In Excel 2007, commands are on the Developer tab.) Change the macro name or leave the default, and press OK.
3. With the macro recorder running, type 'Hello World' in cell A1. Apply Bold, Italic, and Underline, and change the font color to red. Double-click on the column separator in the header row, between columns A and B, to resize the cell's width to its contents.
4. Turn off the macro recorder by selecting Tools, Macro, Stop Recording.
5. Test the macro to make sure the message appears.
Examine Recorded Code
6. Right-click on the Sheet1 tab and select View Code, or press Alt-F11, to open the VB editor, and double-click on Module 1. Your macro code will look something like this:Sub Macro1()ActiveCell.FormulaR1C1 = 'Hello World'
Range('A1').Select
Selection.Font.Bold = True
Selection.Font.Italic = True
Selection.Font.Underline = xlUnderlineStyleSingle
Columns('A:A').EntireColumn.AutoFit
Selection.Font.ColorIndex = 3End Sub
7. Note that the macro begins with 'Sub' and ends with 'End Sub.' Every VBA subroutine begins and ends this way.
8. Find familiar keywords. Since you know what this macro does, you can figure out that 'Selection.Font.Bold=True' changes the selection to bold font. You can also recognize the commands to resize the column and change the color.
9. Go back to Excel and try recording a few more simple macros, each time examining the code in the VB editor.
Set Up the VB Editor
10. Launch the VB editor by pressing Alt-F11.
11. Click on each menu item across the top to familiarize yourself with available options.
12. Set up your environment with options from the View menu. At a minimum, add the Properties Window and the Project Explorer. You can dock them to the left side of the screen by right-clicking and selecting 'Dockable.'
13. Try the context-sensitive Help feature at any time by pressing F1.
Design a Form
14. From the Insert menu, select Insert UserForm. The UserForm is your design canvas.
15. If the UserForm doesn't pop up, go to the Project Editor and double-click 'Forms,' then double-click UserForm1.
16. When the UserForm appears, you will also see the Toolbox, which contains controls you will use in creating your forms. For instance, you can include buttons, text boxes, drop-downs and labels. Hover your cursor over each to see the names.
17. Add some controls to your form. To add a text box, find it in the Tool Box, click on it, then move your cursor to the User Form and draw a rectangle on the form. You will see a shape that looks something like a window. This is where the user will enter his input.
18. Place more controls on the form. Experiment with resizing and rearranging. For some controls to work, they need to be programmed. For instance, an 'OK' button needs an associated sequence of actions. Other controls, like labels, usually need little, if any, programming.
Program a Message Box
19. The 'MsgBox' is preset; you will not need to design a form. But you will need some code. You can program 'Yes,' 'No,' 'OK' and 'Cancel' buttons, configure the prompt, and more.
20. This message box will warn the user that the file will close without saving when she clicks 'Yes.' If she clicks 'No,' the file will save before closing. If she selects 'Cancel,' the file will not close.
21. Launch the VB editor. From the Insert menu, insert a module; then, in the Project Explorer, double-click the new module. A code window will open up.
22. Type the following routine. (Do not type the text in the brackets that follow.)
Sub MsgBoxTest() [Subroutines always begin with Sub and end with End Sub]
myTitle = 'Warning' [Assigns a value to 'myTitle.' When you use 'myTitle' later, it's the same as typing 'Warning.']
myMsg = 'Close without saving? All changes will be lost.' [Assigns a value to 'MyMsg.']
Response = MsgBox(myMsg, vbExclamation vbYesNoCancel, myTitle) [Defines the message box. It will contain 'myMsg,' a warning exclamation point, and Yes, No and Cancel buttons, and the title bar will read 'Warning,' because that's the value of 'myTitle.']
Select Case Response [Select Case assigns actions to various options.]
Case Is = vbYes [If the user clicks Yes]
ActiveWorkbook.Close SaveChanges:=False [Close without saving.]
Case Is = vbNo [If the user clicks No]
ActiveWorkbook.Close SaveChanges:=True [Save and close.]
Case Is = vbCancel [If the user clicks Cancel]
Exit Sub [Exit the subroutine. Don't do anything.]
End Select [This ends the Select Case statement.]
End Sub [Ends the subroutine]
23. Select Run, Run Sub/User Form from the menu. Your message box will be displayed; you have now successfully written a functioning VBA routine.

Tuesday, December 11, 2012

How to Create Multiple Graphs in One Chart

1. Open your spreadsheet program and enter your two data sets into adjacent columns. Type a label for your data sets in the first row of each column. (Note: These instructions are based on Open Office Calc, a free spreadsheet program, but the process will be similar when using Microsoft Excel.)
2. Click and drag to select all of the cells whose data you wish to include in your chart, including the 'label' cells at the top of each column.
3. Click 'Insert' and then 'Chart.' Select 'Line' from the 'Choose a chart type' column. Select 'Lines and Points' from the icons on the right-hand side of the dialog box. Click 'Next.' Make sure the 'First row as label' box is checked and then 'Next' twice.
4. Type in a title for your chart, a subtitle (if desired) and names for the X axis and Y axis. Click 'Finish.'

How to Create a 4 Axis Chart in Excel

1. Create a new spreadsheet in Excel.
2. Type the label names of your axes in each column, for example, Axis 01, Axis 02, Axis 03, and Axis 04 as headers in columns A, B, C, and D respectively.
3. Type the corresponding data for each column and row. The row data will be the “Series” plotted against the actual axis of each column.
4. Highlight the entire set of rows and columns by dragging the mouse cursor across the axis and data fields while holding the left mouse button down.
5. Click “Insert,' 'Charts,' then 'Other Charts' and choose a 'Radar' option from the main menu.

How to Print Odd Even Pages in Excel 2007

1. Click on the 'Developer' tab in Excel 2007.
2. Click on 'Visual Basic' to open the Visual Basic Editor (VBE).
3. Click 'Insert,' then 'Module' to open a blank module window.
4. Copy and paste the following code into the module window:Sub PrintOddEven()Dim TotalPages As LongDim StartPage As LongDim Page As IntegerStartPage = InputBox('Enter starting page number')TotalPages = Application.ExecuteExcel4Macro('GET.DOCUMENT(50)')If StartPage > 0 And StartPage
5. Press 'F5' to run the macro. A pop-up window will appear and you will be returned to the Excel spreadsheet.
6. Type the starting page number in the textbox. If you want to print odd pages, enter an odd number. If you want to print even pages, enter an even number. Excel will print odd or even pages from that starting point. For example, if you type '1,' Excel will print odd pages 1 through the end of your document.

How to Make a 2 Column List in an Excel Spreadsheet

1. Open Microsoft Excel 2007 on your computer. As you can see, several columns and rows already come up in Excel.
2. Place a title at the top of the spreadsheet. This is very important so that you know what is actually listed in the spreadsheet. Place the title at the very top of the page, starting in cell A1.
3. Add titles to the two columns that will compose the list. Place the column titles a few lines down from the title of the spreadsheet. This will help you remember what information is in each column. Center the columns by highlighting both cells and click on the centering icon (showing centered text) in the 'Alignment' section of the 'Home' tab.
4. Bold the title of the spreadsheet and the titles of the columns. Highlight the cells to be bolded and click on the bold icon in the 'Font' section of the 'Home' tab.
5. Skip a line after the column titles and enter your data into columns A and B. You can enter text or numbers, depending on the purpose of the two-column list.
6. Format any numbers that you have entered. If you entered dates, monetary amounts or regular numbers in the columns, you can format them so that all of the numbers look the same. To do this, highlight all of the cells with numbers that you want to format in the same way. Right click and select 'Format Cells.' Use the tools in the 'Number' tab to format the cells according to your purpose.
7. Create totals for columns with numbers, if needed. To do this, click on the cell where you want the total to be, and then click on the sigma symbol (it kind of looks like an E) in the 'Editing' section of the 'Home' tab. The following will appear in the cell: =SUM(). Click on the first cell that you want included in the total, and drag down to highlight the last cell to include in the total. Press 'Enter' and the total will be inserted.

Wednesday, November 28, 2012

How to Delete a Macro Computer Virus

1. Run the program that the infecting macro was originally opened with, such as Microsoft Word or Excel, by double-clicking on its appropriate desktop icon.
2.
Click on the 'View' tab at the top of the screen. Scroll all the way over to the far right side of the screen and click on the button labeled 'Macros.' Click on 'View Macros' on the drop-down menu that will pop up underneath the Macro button.
3.
Wait for the new window to pop up and then scroll through the list of macros installed until you find the one that infected your computer. Click on the name of the macro and then click on the button that is labeled 'Delete.'
4. Close the 'View Macro' window and then click on the large, circular Microsoft icon at the top left of the screen. Click on the option that says the name of the program followed by 'Options,' such as 'Excel Options.' Click on 'Trust Center' and then click the button that says 'Trust Center Options.' Click the option marked as 'Macro' settings and then click the radio button next to the option that says 'Disable all macros.'
5. Open your web browser and navigate to a website that offers a virus scanning program such as AVG Free (see Resources below). Download the installation file and then open the folder where you saved the file. Double-click on it and follow the on-screen instructions to install the program. Open the software by double-clicking on its desktop icon and then click on the option to run a full system scan. Wait for the scan to finish and then click on the option to delete any virus threats found.

How to Format Rows and Columns in Excel 2003

1. Open your Excel worksheet and select the rows or columns you want to format. To select, hold down the left mouse button and drag.
2. Click 'Format' on the top menu bar to summon a list of options for formatting your worksheet.
3. Click either 'Row' or 'Column.' The rows are horizontal, the columns are vertical.
4. If you click 'Row,' a box will appear. Enter the row height. If you pick 'Column,' enter the column's width in the designated box.
5. When you've finished formatting, click 'OK' and save your work.

Tuesday, November 27, 2012

How to Restore Microsoft Excel to Its Default Spreadsheet

Excel 2003 or Earlier
1. Go to \'C:\\Documents and Settings\\
\\Application Data\\Microsoft\\Templates.\' Open \'My Computer\' and open the \'Documents and Settings\' folder. Open your username, then \'Application Data,\' \'Microsoft\' and finally find the \'Templates\' folder.
2. Rename the Book.xlt and Sheet.xlt files to something that you will remember. This step is optional, but allows you to save the settings for later use. Either move these files, or delete them if you don't want to use the template again. If there are no XLT files in this folder, continue below.
3. Go to \'C:\\Program Files\\Microsoft Office\\Office11\\XLStart.\' Navigate to \'Program Files,\' and open the \'Microsoft Office\' directory. Select \'Office11\' and then \'XLStart.\'
4. Look for any template files inside this folder--these are any files with a \'.xlt\' extension. If there are any, delete them. If there are none, continue below.
5. Open Microsoft Excel, and locate the \'Options\' settings under the \'Tools\' menu.
6. Click the \'General\' tab and note which folder is listed \'At Startup, open all files in.\' Minimize or close Excel and navigate to the folder listed in that box.
7. Delete any existing XLT files in that folder. If you want to save them for later use, rename the template using a name you will remember.
8. Open, or close and reopen Microsoft Excel to verify the settings returned to default.
Excel 2007 or Later
9. Open Microsoft Excel.
10. Navigate to the Help menu and open the \'Detect and Repair\' function. The Detect and Repair dialog will appear. If you want to restore your shortcuts at this time, check the appropriate icon.
11. Click \'Start.\' Wait for a short period while Office deletes all of the settings on all of the programs in the suite and restores it to its original installed state. This will apply the settings to Word, PowerPoint and Excel, and any other Office programs you have installed.

How to Flip Column Headings As Row Headings on an Excel Spreadsheet

1. Select and copy the entire data range you want to transpose. For example, if you have 10 columns and 10 headers, highlight them as well as all the data within. Excel will accurately transpose not only the column and header titles but also the data.
2. Click on a new location in your worksheet for the transposed information to go. The simplest thing to do is go down a few columns and click. This way you can compare the new information with the old before deciding which information to keep. You can delete the other data or decide to use the information in both formats.
3. Click on the “Edit” menu, then select “Paste Special” and click on the “Transpose” check box (located on bottom right of window). This will copy the information and transpose it at the same time, as opposed to the 'Edit' > 'Copy' function, which will merely copy the information but not transpose it.
4. Click the “OK” button and Excel automatically transposes the column and row labels, as well as all the data. This is important to understand because it will save you a lot of time when you realize you need to transpose headers and columns. It doesn't just transpose the header and column names, it also reorganizes all the data where it belongs.
5. Compare the new data with the old data and decide whether you want to keep both or just one set of data. It may be helpful to provide both sets of data as a draft to get an objective perspective on which works better. Then, once you've sought an outside opinion, you can delete one set of data. Either way, it's handy to have both available in case you decide the data was better in the original format before you transposed it.

How to Perform the Command to Center a Worksheet Both Horizontally Vertically

Excel 2003
1. Log on to your computer and open Excel 2003. Click the 'File' menu and choose 'Page Setup.'
2. Click the 'Margins' tab. Go to the 'Center on Page' section.
3. Check both the horizontal and vertical check boxes. Click 'OK' to print your centered worksheet.
Excel 2007
4. Open Microsoft Excel 2007 and open the spreadsheet you want to center. Click the Office button on the upper-left corner of the screen. Go to the 'Print' menu and select 'Print Preview.'
5. Choose the 'Page Setup' option and click the 'Margins' tab. Check the 'Horizontally' and 'Vertically' boxes in the 'Center on page' section. Click 'OK' to close the 'Page Setup' menu. Review the print preview of the document to make sure that it is properly centered.
6. Click 'Print' to send your centered spreadsheet to the printer. Click 'Close Print Preview' to return to the main screen.
Open Office Calc
7. Log on to your computer and open the Open Office Calc program. Click 'File' and then 'Open.'
8. Select the spreadsheet you want to center. Click the 'Format' menu and choose 'Page.'
9. Click the 'Page' tab. Check the 'Horizontal' and 'Vertical' checkboxes and click 'OK' to save the settings.

Monday, November 26, 2012

How to Compare Two Columns in VLookup

1. Open Excel 2010 and select a workbook. Click the 'File' tab and select 'Open.' Browse the files and locate the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Sort the values that will be included in the vlookup. The first column needs to sort in ascending order. Click in the first cell of the third column. Click the 'Formulas' tab and select the 'Lookup Reference' button. Select 'Vlookup' from the list of functions. The function arguments window opens.
3. Click the 'Lookup value' field. Click the red arrow. Select the first cell in the third row. Click the 'Table Array' field. Click the 'Red Arrow.' Highlight the data in the two columns. Click the 'Column Index Number' field. Type '2.' Once the matched vlookup value is found, it will return the cell value in the 2nd column.
4. Type 'False' in the 'Range Lookup' field. False indicates an exact match while true will find a close enough match. Click 'OK.' Excel will compare the lookup value against the two columns in your spreadsheet and display the cell value in the second column if a match is found.

How to Align Text in Excel Cells

1. Start Microsoft Excel and open the file you want to change.
2. Select the cells in which you want to align the text.
3. Open the Format menu and select Cells.
4. In the Format Cells dialog box, select the Alignment tab.
5. In the Text Alignment pane, click the horizontal text box.
6. Select the horizontal alignment type such as left, right, center.
7. Click the vertical text box.
8. Select the vertical alignment type, such as top or bottom.
9. Select OK to accept the changes.

Sunday, November 25, 2012

How to Make a Selection in Microsoft Excel

1. Click a single cell with the mouse to select it. This cell will be surrounded by a black border and the row and column it belongs to will be highlighted in the frame around the spreadsheet. If you're only looking to select one cell, you're done!
2. Click the initial cell, keep the mouse button pressed down, and drag the mouse horizontally, vertically or diagonally to select multiple adjacent cells. Now the entire group of cells (called a range) will be shaded gray and surrounded by a black border.
3. Click the name of a column or row in the frame around your Excel spreadsheet to select an entire row or column. For example, click the letter 'B' to select the entire second column. Doing this will deselect any cells that are currently selected.
4. Click the mouse to select a single cell, hold down the 'Control' key of your keyboard, and click another cell to select two non-adjacent cells. As long as the 'Control' key is held down, any number of cells can be selected. Additionally, clicking an already selected cell with the 'Control' key held will deselect it without affecting other selections.

How to Make an Ogive in Excel

1. Open a new Excel spreadsheet. Type 'Data' into cell A1, then type 'Bins' into cell B1. Enter the data set that you want to use to create the Ogive chart into column A, starting with cell A2.
2. Enter the bins for your histogram into column B, starting with cell B2. The bins are the numbers that represent the top value in the data ranges for your histogram. For example, if you want to determine the frequency of ranges from '0 to 5,' '6 to 10' and '11 to 15,' your bins would be '5,' '10' and '15.' The bins will appear on your Ogive chart as values plotted on the horizontal axis.
3. Click 'File' and select 'Options' from the list the appears. Click 'Add-Ins' once the Options window appears, then click 'Go.' Place a check next to 'Analysis ToolPak,' then click 'OK.'
4. Select the 'Data' tab at the top of the screen, then click the 'Data Analysis' button on the right end of the Ribbon. Click 'Histogram' from the list in the window that appears, then click 'OK.'
5. Place your cursor in the 'Input Range' field in the Histogram window. Click cell A1 and hold down the mouse button. Drag the cursor down to the last cell in the first column that has data, then release the button. Place your cursor in the 'Bin Range' field, and select all filled cells in that column. Place a check in the box next to 'Labels.'
6. Place the cursor in the text field next to 'New Worksheet Ply' and enter a name for your worksheet. Place checks next to 'Cumulative Percentage' and 'Chart Output,' then click 'OK.' Your histogram and chart will appear on the screen.
7. Click the blue bars in the middle of the chart, then press 'Delete.' This will remove the frequency data and leave you with a line graph of your cumulative frequency.

Saturday, November 24, 2012

How to Import OFX Files Into Microsoft Excel

1. Click 'Start,' type 'notepad' (without quotes) and press 'Enter.' Press 'Ctrl O.' Click the 'File type' drop-down menu and select 'All Files and Folders *.*.' Locate your OFX file, select it and click 'Open.'
2. Browse through your OFX file. You will notice that there is a pattern among all entries. Each OFX file will be different, but in general you will notice fields of data such as times, amounts or comments. There will also be a selection of letters or characters separating each line of data (e.g., hrt]). This 'separator' will be the same throughout. Determine what the separator is.
3. Click 'View,' then 'Find and Replace.' In the 'Find' field, type out the separator that currently exists in your document.
4. Enter '|' (bar, not an L) by pressing 'Shift \' in the 'Replace' field. Click 'Replace All.' This will replace all the existing separators and replace them with bar--an Excel compatible separator.
5. Click 'File,' then 'Save As.' Click the 'File type' drop-down menu and select 'All Files and Folders.' Enter 'toimport.txt' (without quotes) in the file name and click 'Save.'
6. Launch Microsoft Excel. Click the Microsoft Office logo in the ribbon. Click 'Open.'
7. Select 'Text Files' from the list, then navigate to 'toimport.txt' and double-click it to open the file. The Text Import Wizard will open.
8. Select 'Delimited' in the 'Original data type' field. Click 'Next.'
9. Set the 'Delimiters' to '|' ('Shift \') and click 'Next.' A preview of your import will appear. Click 'Finish' to import your data to Microsoft Excel.

How to Make Graphing Paper in Excel

1. Open a new Microsoft Excel 2010 spreadsheet. Click on the small square above the '1' and to the left of the 'A' in the spreadsheet's top-left corner. This will select the entire spreadsheet.
2. Move your mouse between the 'A' and 'B' at the top of the spreadsheet. Once the pointer changes to appear as a line with two opposite arrows coming off of it, click and hold the mouse button. Drag the mouse to the left to shorten the columns until you get the horizontal spacing that you desire for your graph paper. Make note of the number of pixels that appear in the small popup box above your mouse pointer.
3. Click on the line between the '1' and '2,' in the same manner as you did between the 'A' and 'B' columns. Drag the mouse up or down until the pixel number reads the same as it did when you adjusted your columns. When the number is the same, release the mouse button and your spreadsheet lines will new create perfect squares.
4. Click 'File' at the top of the Excel window and then choose 'Print' from the list on the left side of the screen. Click the 'Normal Margins' button and choose 'Custom Margins' from the menu that appears. Click the down arrow next to each margin to reduce it to zero, then click 'OK.' Click the 'Home' tab to return to your spreadsheet.
5. Click and hold the mouse button on cell 'A1.' Drag your mouse down and to the right until you get to the first cell that is in a corner created by the dotted page-break lines. Release the mouse button. Click the drop-down arrow next to 'Borders' in the Font area of the ribbon, then choose 'All borders' from the available options. Your spreadsheet will not have darkened gridlines that will show up when you print the page.
6. Click the 'File' tab and select 'Print' from the list of options. Click the 'Last Custom Margins Setting' button and choose 'Custom Margins' from the menu. Place a check mark next to 'Horizontally' and 'Vertically,' then click 'OK.'
7. Click the 'Print' button near the top of the window to print a page of your graphing paper.

Friday, November 23, 2012

How to Remove a Lost Excel Password

1. Open Excel and then the workbook file for which you wish to remove the password. If you are using Excel 2007 skip straight to Step 5; for all previous versions continue on to Step 2 below.
2. Click on the 'Tools' menu at the top of the Excel window. Select 'General Options' from the drop down list of choices that appear.
3. Locate the box labeled 'In the Password to open' and double-click on the asterisk in it. Click 'Delete', and click 'OK.' Repeat the process for the box labeled 'Password to modify.'
4. Click 'Save', and then 'Yes' to complete the removal of the password in versions prior to Excel 2007.
5. Click on the 'Review' tab at the top of the Excel screen in Excel 2007. Click on 'Protect Worksheet' and remove the check mark from the check box labeled 'Protect worksheet and contents of locked cells'. Click 'OK'.
6. Click on 'Protect Workbook,' delete the password in the password text box, and click 'OK' to finish the job.

Subscribe to: Posts (Atom)