How to Turn Off Page Lines in Excel

1. Click the 'File' tab in the Excel window, and then click 'New' if you want to make a new Excel document. Click on a template that you want to use, and then click 'Create.' A new Excel document will appear. Otherwise, click the 'File' tab, then click 'Open,' and then click on a folder where your file is stored. Click on the file to select it, and then click 'Open.'
2. Click the 'File' tab in the Excel window.
3. Click on 'Normal' in the 'Workbook Views' group. Excel will now display your spreadsheet without the page break lines. Click 'Page Layout View' if you want to see the lines again. If you still see page lines when working with a document in Normal view, that means Excel is set to turn them on automatically. To hide page breaks while in Normal view, you will need to adjust the display options for the document.
4. Click the 'File' tab, and then click 'Options' under 'Help.' Click the 'Show Page Breaks' check box to clear it under 'Display Options for This Worksheet' in the 'Advanced' category. When this option is unchecked, the page lines will not be displayed when you view a spreadsheet with Normal view.

Read more ►

How to Create a Workbook in Excel 2007

1. Decide what your needs are. You need a purpose for using Excel. Whether it is for a budget or to keep track of work, you need to know what you are planning to build.
2. Open Excel and you will see the option to create a workbook or use a template. You may want to check what templates are available and if you feel one of them meets your needs you could try using it. However, since most work is unique and you may have your own thoughts on how you want to present the data, you may be better off creating your own workbook.
3. Use column headings that will make your life easier when it comes time to manipulate the data. If you are using names, you may want to make columns with first names, middle names and last names. This will give you the choice to sort on any of these columns later on. Addresses should be broken up into street names, cities, states and zip codes for the same reason.
4. Enter the data. If you are creating an address book for yourself, you could make multiple sheets in your workbook for friends, families, co-workers, and businesses.
5. Use the tabs at the bottom of the page. The tabs signify the different pages or worksheets in your Excel workbook. You can rename them and make them more significant for your needs. For example, sheet 1 could be friends, sheet 2 family and sheet 3 co-workers.
6. Personalize the Excel workbook. The more you make the workbook your own, the easier it will be to work with the data. The examples in the above steps all involve work that you would do for your personal needs, but you can carry it over into your job also.
7. Add to the workbook as needed. The beauty of using Excel workbooks is that you only have to enter data once. After that, you can add or delete new entries or worksheets as you need. Editing data is also simple to accomplish.

Read more ►

How to Format Data Labels in Excel

1. Launch 'Microsoft Excel' by double clicking the program's shortcut on the desktop, or by selecting 'Microsoft Excel' from the 'Programs' or 'All Programs' menu.
2. Click 'File' or the 'Office Button' in the upper left corner of the program window and select 'Open.' Click on the downward pointing arrow to the right of the 'Look in' text box, and navigate to your Excel file. Select the file and click 'Open' or double click the file to open it.
3. Go to step 4 if you have Excel 2007; otherwise select the chart with the data labels you want to format. Click 'Chart' on the menu bar and select 'Chart Options' to open up the 'Chart Options' dialog box. Click the 'Data Labels' tab at the top of the window.
4. Do this step if you have Excel 2007. Click the right mouse button on the data labels and select 'Format Data Labels.' Click the 'Label Options' tab at the top of the window.
5. Select 'Series name,' 'Category name' or 'Value,' depending on what label you want displayed. Select more than one label if appropriate.
6. Select a 'Separator' of your choice if more than one data label was selected. Click 'OK' if you have Excel 2003 or an earlier version. Click 'Close' if you have Excel 2007.
7. Click the right mouse button on the data labels, and Select 'Format Data Labels.' Select a background color, border style, border color and border weight.
8. Click the 'Number' tab if your data labels are displayed as values. Select the appropriate 'Category' type for the values.
9. Click the '3-D Format' tab for Excel 2007. Select a '3-D Format.'
10. Click the 'Alignment' tab. Select a 'Horizontal' or 'Vertical' alignment for your text.
11. Click in the 'Degrees' or 'Custom angle' text box. Type a number to angle the text at.
12. Click 'Ok' for Excel 2003 and earlier, or click 'Close' for Excel 2007. Your chart should be displayed with your newly formatted data labels.
13. Click the data labels to select them. Select a 'Font', 'Font Color' of your choice, using the buttons on the 'Formatting Toolbar' or ribbon, if you are using Excel 2007.
14. Click 'File' on the menu bar, or the 'Office Button' and select 'Save' to save the spreadsheet. The spreadsheet can also be saved by clicking the save button on the toolbar.

Read more ►

How to Graph a Normal Distribution Curve in Excel 2007

1. Enter the number -4 into cell A2.
2. Select the cell with your mouse. Click on the down arrow under 'Fill' in the Editing group to the far right on the ribbon (the toolbar). Choose 'Series.' A pop-up will appear.
3. Click on the radio buttons to choose 'Columns and Linear.' Type .25 into the 'Step Value' box and 4 into the 'Stop' box. Press 'OK.' This should fill column A with the x-values for the normal distribution.
4. Type =NORMDIST(A2,AVERAGE($A$2:$A$34),STDEV($A$2:$A$34),FALSE) into cell B2. This calculates the mean and standard deviation from your x-values and also creates your first y-value in cell B2.
5. Copy the formula that you typed in Step 4 by highlighting the cell and dragging the fill tool (the tiny square at the bottom right) down to cell B34. This creates all of your y-values.
6. Select the 'Insert' tab from the ribbon. Click on the down arrow next to 'Scatter' and choose 'Scatter With Smooth Lines and Markers.'

Read more ►

How to Compare Data in Excel with VLOOKUP

1. Open the Excel 2010 spreadsheet that contains that data you want to compare. Click on the top cell of the first empty column in your spreadsheet.
2. Enter the following formula into the cell:=vlookup(A1,B:B,1,False)Change 'A1' to the top cell in the first column of data. Change the 'B's in 'B:B' to the letter of the column where the second set of data is located. Keep the '1,' as this tells VLOOKUP that you want to check the first column in the 'B:B' range. 'False' specifies that you want to search for an exact match, instead of an approximate one.
3. Press 'Enter' to complete the formula and have Excel run it. If the first entry in the first column appears in your second column, its name appears in the cell where you wrote the formula. If not, '#N/A' appears in the cell.
4. Select the cell where you entered your formula. Move your mouse pointer to the bottom-right corner of the cell. Click and hold the mouse button, then drag the mouse down until you reach the last cell where you want to use the formula. Release the mouse button. When you copy formulas like this, Excel will use relative references, so the 'A1' in the formula will change to, for example, 'A2,' 'A3' or 'A4' depending on what row the formula is in. The column where you entered the formulas will now display all the items from the first column that also appear in the second.

Read more ►

How to Create and Name an Excel 2007 File

Create New File Instructions
1. Open the 'Start' menu and type 'Excel' into the search box.
2. Click the 'Windows Office' icon in the top left corner of the Excel 2007 menu bar.
3. Click the 'Blank Workbook' in the 'New Workbook' window, then click the 'Create' button.
4. Click on the 'Microsoft Office' icon and select 'Save.' Create a name for your spreadsheet and ensure that the 'Save as type:' menu is set as 'Excel Workbook (*.xlsx).'
5. Click 'Save' in the 'Save As' window to save the new Excel 2007 file.
Existing Data Instructions
6. Open the 'Start' menu and type 'Excel' into the search box.
7. Click the 'Windows Office' icon in the top left corner of the Excel 2007 menu bar.
8. Click 'Blank Workbook' in the 'New Workbook' window, then click the 'Create' button.
9. Copy the information to be transferred into the new spreadsheet. Select cell 'A1' in the newly created spreadsheet. Paste the data by pressing both 'Ctrl' and 'V' on your keyboard.
10. Click on the 'Microsoft Office' icon and select 'Save' once the information has been copied. Create a name for your spreadsheet and ensure that the 'Save as type:' menu is set at 'Excel Workbook (*.xlsx).' Click 'Save' in the 'Save As' window to save the new file.

Read more ►

How to Calculate Pearson's R (Pearson Correlations) in Microsoft Excel

1. Put the values of the two variables you wish to correlate into two columns of the same length. For example, say you have data about the heights and weights of 50 people, and want to calculate the Pearson correlation between the two. Put the data into two columns: the heights in cells 1 to 50 of column A, and the widths in cells 1 to 50 of column B.
2. Select an unused cell and type '=CORREL(' (without the quotes). After typing the first open parentheses, select all of the cells in your first column, type a comma, select all of the cells in your second column, and type the closing parentheses ')'. In this example, since the data was in cells 1 to 50 of column A and cells 1 to 50 of column B, you could also simply type:=CORREL(A1:A50,B1:B50)Either method should yield the same result.
3. Press 'Enter.' The cell now contains the value of the correlation between the two columns.

Read more ►

How to Remove Hidden Macros in an Excel Spreadsheet

1. Double-click the Microsoft Excel document you want to edit. This opens the document and loads the Excel application.
2. Click the 'View' menu option and select 'Unhide.' In the section labeled 'Personal,' select 'Unhide workbooks' from the list of options.
3. Click the 'Developer' tab at the top of the Excel window. If you don't have the Developer tab, click the Office button and select 'Excel Options.' Click the 'Popular' tab and select 'Show Developer tab in the Ribbon.' Press the 'OK' button.
4. Click the 'Macros' button in the 'Developer' tab. A pop-up window opens with a list of macros programmed for the spreadsheet.
5. Click the name of the macro you want to delete. Click the 'Delete' button. Click 'Yes' to confirm that you want to delete the macro. It's now deleted.

Read more ►

How to Create Sequences in Excel

1. Click the first cell in the row or column that you want to contain the sequence and type the first item. For example, to create the sequence of 'Red,' 'Green' and 'Blue,' type the word 'Red.' Press 'Enter' to move to the next row or press the right arrow key to move to the next column.
2. Type the next item in the sequence, such as 'Green.' Press 'Enter' or the right arrow key.
3. Type the next item in the sequence, such as 'Blue.' Repeat this process until you have typed every item in the sequence.
4. Click and drag with the mouse pointer to highlight the cells containing the complete sequence plus the additional first item. You should see a large black square in the lower-right corner of the box outlining the highlighted cells.
5. Click and drag the black square to auto-populate the blank cells below or to the right with the sequence that you have entered. As you drag the square, Excel displays a small pop-up message showing the data that it will put in each cell.
6. Release the mouse button to stop auto-populating the sequence.

Read more ►

How to Open Large Files in Excel 2003

1. Launch Excel, and navigate to the 'tools' menu.
2. Choose the 'macro' submenu, and select the Visual Basic editor.
3. Choose 'module' from the insert menu.
4. Copy and paste the following text:Sub LargeFileImport()'Dimension VariablesDim ResultStr As StringDim FileName As StringDim FileNum As IntegerDim Counter As Double'Ask User for File's NameFileName = InputBox('Please enter the Text File's name, e.g. test.txt')'Check for no entryIf FileName = '' Then End'Get Next Available File Handle NumberFileNum = FreeFile()'Open Text File For InputOpen FileName For Input As #FileNum'Turn Screen Updating OffApplication.ScreenUpdating = False'Create A New WorkBook With One Worksheet In ItWorkbooks.Add template:=xlWorksheet'Set The Counter to 1Counter = 1'Loop Until the End Of File Is ReachedDo While Seek(FileNum)
5. Press the F5 key to run the macro.

Read more ►

How to Use the Conditional Sum Wizard in Excel

1. Open the file that contains that data that you need to run the conditional sum wizard on.
2. Add a header row to your data, if it lacks one, by right-clicking on the number next to the first row and choosing 'Insert.' Type in names for the columns in the cells directly above the data.
3. Click on the top-left cell of the data range. Move to the bottom-right cell in the data range. Press and hold the 'Shift' button and click on the cell to select the entire data range.
4. Click the 'Formulas' tab at the top of the screen, then click on the 'Conditional Sum' button located on the far right side of the ribbon, under the 'Solutions' group.
5. Click 'Next' on the first screen, as you have already defined the data area.
6. Select the column that you want to sum from the drop-down menu at the top of the window, identified as 'Column to sum.' Then select the column that you want to evaluate for a condition in the drop down menu in the middle of the window, identified as 'Column.' Next to that column you can choose an operator, such as 'equals,' 'greater than' or 'less than.' Finally, you can type in the value that you want to check the original column for in the 'This value' box. Press the 'Add Condition' button when you are done, and then press 'Next.'
7. Choose between having Excel export your result as just a formula in a cell, or as the formula in a cell as well as the conditional data next to that cell. Choosing the latter lets you change the conditions of the formula without having to go through the wizard again. Press 'Next' when you have made your selection.
8. Select a cell where you want the condition to be placed, if you chose that option, and the cell for the conditional sum formula. Press 'Finish' to complete the process.

Read more ►

How to Create a Macro to Run an Access Query Paste the Result Into Excel

1. In Access, create a table of sample data: enter the following data in a new table:the accidental tourist,12/1/2009,$6.01
the accidental tourist,12/3/2009,$7.98
iron john,12/5/2009,$4.98
iron john,12/6/2009,$5.98
2. Double-click the column headers (e.g. 'Field1') and replace each with these headers, in this order:book,datesold,netsaleSave the table ('control-s') with the name 'books.'
3. Create a query from the table, and press the 'Esc' key in the 'show table' dialog box. Right-click on the query's tab and select 'SQL view.' Enter the following in the code window:SELECT books.* INTO queryresults
FROM books
WHERE (((books.book) Like '*acc*'));Save the query ('control-s') and name it 'vbaquery.'
4. Open Excel and press the toolbar's 'Data>From Access' icon. Select the 'queryresults' table in the 'Select Table' dialog box. Click 'OK' on the 'Import Data' dialog box and notice the query's results: only the 'iron john' books are shown. Save the Excel file with any name, and close it.
5. Reopen the 'books' database in Access. Open the 'vbaquery' and revise its 'Criteria:' field to read 'Like '*acc*'' (Don't type the double quotes. Do type the inner, single quotes.) Resave the query.
6. Create a new query. Type the following SQL statement in the 'SQL view' window, then save the query as 'dropqueryresults':DROP TABLE queryresults;
7. Enter the Visual Basic integrated development environment (IDE) by pressing 'alt-f11,' then select 'Insert>Module.' Paste the following code into the new module's blank code window:Public Sub runquery()
'delete the results table first
On Error GoTo DO_QUERY
RunQueryForExcel ('dropqueryresults')DO_QUERY:
RunQueryForExcel ('vbaquery')
End SubPublic Sub RunQueryForExcel(qName As String)
DoCmd.SetWarnings False
CurrentDb.Execute qName
DoCmd.SetWarnings True
End Sub
8. Position the cursor anywhere in the 'runquery' subroutine and press 'F5' to run the query. Reopen the Excel workbook you previously opened and notice the updated data: your macro has replaced the 'iron john' rows with the 'accidental tourist' rows. (Access 2007 or later users can stop here.)
9. (For users of Access 2003 and earlier). Use step 7 to paste the following code into a new module in the Visual Basic IDE:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub pasteToExcel()Const qName = 'vbaquery'
Dim db As DAO.Database
Dim recset As DAO.Recordset
Dim s As String
Dim appXL As Excel.Application
Dim ro, co'''''''''''''''''''''''''
Set appXL = CreateObject('Excel.Application')
appXL.Workbooks.AddSet db = CurrentDb
Set recset = db.OpenRecordset(qName)
s = 'book' ', ' 'dateddsold' ', ' 'netsale' vbCr
appXL.ActiveSheet.Cells(1, 1) = s
ro = 2
co = 1
s = ''
Do While Not recset.EOF
s = s recset![book] ', ' recset![datesold] ', ' recset![netsale] vbCr
appXL.ActiveSheet.Cells(ro, co) = s
recset.MoveNext
ro = ro 1
s = ''
Loop
recset.Close
db.Close
appXL.ActiveWorkbook.SaveAs ('c:\dataFromAccess.xls')
appXL.QuitEnd Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Select 'Tools>References' and check the 'Microsoft Excel Objects Library.'
10. Return to Access and do steps 1 to 3. However, for step 3, paste this SQL code into the SQL code window:SELECT books.*
FROM books
WHERE (((books.book) Like '*acc*'));
11. Return to the Visual Basic IDE. Place the cursor inside the 'pasteToExcel' function and press 'F5' to run the function. Open the Excel file 'c:\dataFromAccess.xls' to view the results.

Read more ►

How to Create a Newsletter in Microsoft Office

1. Open Microsoft Word, and select a newsletter template by clicking the drop-down menu File > New Works Template > Tasks. Additional templates may be downloaded from the Microsoft Office Online website.
2. Create a nameplate for your newsletter. Using Word's 'header' is the simplest way to do this. Select View > Header and Footer from the drop-down menu to edit text boxes within the header and footer. Center your newsletter's title and add clip art, if desired, selecting Insert > Picture > Clip Art from the drop-down menu. If the software is available, you may use Copy and Paste to import clip art from Publisher.
3. Add content to your newsletter. Write or solicit articles from family members or co-workers, create a list of upcoming events or feature one-line quotes from members of the team.
4. Create a calendar to import using Excel. Select the Tools drop-down menu, and select CalendarMaker. The calendar may be copied and pasted into your Word newsletter.
5. If the software is available, create a mailing list using InfoPath.
6. Print enough copies for distribution. The easiest and most cost-efficient format for your newsletter is one page, front and back, using black ink.

Read more ►

How to Use VLookup in Excel 2003

1. Open the Excel 2003 file that contains the data table that you want to work with.
2. Select the top-left cell of the data table and hold down the mouse button. Move the mouse to the bottom-right cell and select it. Click the 'Data' menu at the top of the screen and select 'Sort.' Select 'Ascending' to sort the information from smallest to largest. The first column has to be sequential in order for VLookup to work.
3. Select any cell outside of the data table. Press the '=' key to start your formula. Type 'vlookup('. The open parenthesis is required to begin entering arguments.
4. Type in the value that you want to search for, or a reference to a particular cell in the first column. If you are searching for a text string, wrap the text in double quotes. You may include '?' and '*' as wildcards. '?' will replace any one value, while '*' will replace any string of values. If you need to search for a question mark or an asterisk specifically, precede the character with a tilde. Remember that VLookup will only search in the first column of your data table. Press the comma key when you are done.
5. Click on the top-left cell of the table and hold down the mouse button. Drag your mouse to the bottom-right cell of the table and release the button. You will see those two cells appear in your formula bar. Press the comma key.
6. Press the number key that corresponds to the column where your desired result resides. Columns in the table are numbered starting from the left and counting up by one for each column. If you enter '3' here, VLookup will find your value in the first column, then give you whatever is in that same row in the third column. While you may enter '1' here, VLookup will return the value that you searched for. Press the comma key.
7. Enter the word 'false' if you want VLookup to find an exact match for your search value in the first column. If you only want to find an approximate match, erase the last comma that you entered, as this command is optional and searching for an approximate match is the default setting. If you search for an approximate match, VLookup will use the next largest value if it cannot find an exact match. Type in a close parenthesis and press the Enter key to complete the process.

Read more ►

How to Remove Checkboxes in Excel 2007

1. Open Microsoft Excel 2007.
2. Click the 'File' tab, click 'Open' and browse to the spreadsheet with the check boxes to delete. Double-click the name of the file to open it.
3. Click once on the first check box to remove. Press the 'Delete' key to remove it. Scroll throughout the spreadsheet or page tabs at the bottom of the work area and continue clicking and pressing the 'Delete' key to remove the check boxes.

Read more ►