Excel

Browse » Home » All posts

Wednesday, December 19, 2012

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.

Subscribe to: Posts (Atom)