Excel

Browse » Home » All posts

Monday, September 16, 2013

How to Remove the Date From Excel When Printing

1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel' to open the file.
2. Press the 'Ctrl' and 'O' keys to open the 'Open' dialog window. Double-click the Excel file you want to open. The file opens in the spreadsheet software.
3. Click the 'Page Layout' ribbon. In the right side of the ribbon in the 'Headings' section, remove the check mark next to the 'Print' option.
4. Press the 'Ctrl' and 'S' keys to save the settings to the document. Print the document to your printer to create a hard copy without headers.

How to Synchronize Scrolling in Excel

1. Open Microsoft Excel.
2. Open your two worksheets, or split your workbook into multi-view.
3. Click 'View' on the toolbar.
4. Click 'Synchronized Scrolling' on the right.

How to Convert CSV Files to Excel 2007

1. Open Excel 2007.
2. Click the circular Microsoft Office button in the upper left corner. Select Open.
3. Click the 'All files (*.*)' drop-down box and select Text Files.
4. Locate your CSV file and click 'Open.'
5. Click the Delimited radio button if the CSV file's data is separated by commas, semicolons or similar characters. Click the 'Fixed width' radio button if the CSV data is arranged in columns. Click 'Next.'
6. Check the check box indicating the delimiter that separates the values, such as commas, semicolons or tabs. Click 'Next.'
7. Click a column to select it. Click the General, Text or Date radio buttons to select a text type for the column. Repeat for the remaining columns and click 'Finish.'
8. Click the circular Microsoft Office button and select 'Save as.'
9. Click the 'Save as type' drop-down box and select 'Excel workbook (*.xlsx).' Type in a file name and click 'Save.'

How to Move Information Inside an Excel Spreadsheet

Move Cell Content Inside an Excel Spreadsheet
1. Start Microsoft Excel, and open a spreadsheet that contains data that you would like to move to a different area within the spreadsheet.
2. Select a cell or group of cells that you would like to cut and paste to a new location by clicking and dragging with your mouse over the cell(s).
3. Right-click on top of the selected cell(s) and choose 'Cut' from the shortcut menu to delete the content from the cell(s) and add them to the Excel clipboard.
4. Click in the cell(s) where you would like the cell content that you just copied to the clipboard to be moved.
5. Use your mouse to right-click and select 'Paste' from the shortcut menu to finish moving the information to it's new location within your spreadsheet.
Move a Worksheet Inside an Excel Spreadsheet
6. Locate the 'Sheet' tabs at the bottom of your Excel spreadsheet.
7. Click on the sheet tab that refers to the worksheet you would like to move inside the Excel spreadsheet.
8. Drag the sheet tab either to the right or left and release the mouse button when the sheet tab appears in the desired location.

Sunday, September 15, 2013

How to Disable Automatic Hyperlink in Excel 2007

1. Click the Office button after opening Excel.
2. Select 'Excel Options' from the Office menu. An 'Excel Options' window will open.
3. Click 'Proofing' from the list of options. You can access AutoCorrect here to disable automatic hyperlinks.
4. Click 'AutoCorrect Options' near the top of the window. The 'AutoCorrect' window will open.
5. Click the 'AutoFormat As You Type' tab and clear the checked box next to 'Replace as you type Internet and network paths with hyperlinks.'
6. Click 'OK' to disable automatic hyperlinks and click 'OK' again to close the window. When you type a Web address in Excel 2007, it will no longer change to a hyperlink.

How to Insert a Calendar in Excel 2007

1. Display the 'Developer' tab in Excel, if it is not already displayed. Click the 'Microsoft Office Button' and then click 'Excel Options.' In the category labeled 'Popular,' under 'Top options for working with Excel,' check the box labeled 'Show Developer Tab in the Ribbon' and click 'OK.'
2. Click 'Insert' in the 'Controls' group on the 'Developer' tab. Under 'ActiveX Controls' click the 'More Controls' button. This button will look like a hammer and a wrench. The 'More Controls' dialog box will appear.
3. Select 'Calendar Control 12.0' and click 'OK'. Right-click the area into which you wish to insert the calendar. The calendar will then be displayed on your workbook. The current month will be displayed.
4. Edit the calendar by right-clicking on it and selecting 'Calendar Object' and then 'Properties.' This will allow you to change the dates in the calendar. If you wish to edit the font, color or any other properties of the calendar, right-click the calendar and choose 'Properties.'

How to Add Autofill to an Excel Spreadsheet

1. Open Excel 2010 and click the 'File' tab. Select the 'Open' option. Browse the files and locate the workbook. Click the workbook and the 'Open' button. The workbook opens.
2. Click the worksheet where the autofill will be added. Insert a column adjacent to the worksheet data. For example, if your data resides in cells A1 to D20, insert a column before column A. Do this by clicking the A column header. Right-click the column header A and select 'Insert.' A blank column appears to the left of the data.
3. Insert the number '1' in the first cell, A1, adjacent to the data. Insert the number '2' in cell A2. Highlight cells A1 and A2. Notice the black square in the right corner of highlighted cells. Move your cursor over this square and notice it becomes a black ' .' Click and drag the plus down in the spreadsheet. Autofill fills in the remaining numbers of the sequence. When you have reached the desired end result, stop dragging.

How to Calculate the Number of Days between Two Dates in Microsoft Excel

1. Open the Excel workbook with dates you want to work with.
2. Enter the first or starting date in an empty cell. For example, if you want to enter the first day of the billing cycle, you can enter that in cell A1. Make sure you enter the month, day and year. If you do not enter a year, Excel will default to the current year.
3. Enter the second or ending date in another empty cell, for example, cell B1.
4. Highlight both cells and click the number format window in the 'Number' group on the ribbon. Select 'Short Date' or 'Long Date' from the menu, depending on how you want the dates displayed. The number format window typically displays the word 'General' initially, but it can display 'Custom' or any of the other formats. Excel generally recognizes dates when they are entered in date format; however, it is always useful to make sure your cells are formatted as dates.
5. Click an adjacent cell such as C1 to enter your formula. Type the following formula, substituting your own cell numbers for 'B1' and 'A1,' and press 'Enter':=B1-A1Excel will subtract one date from the other and display the number of days between the two dates.

Saturday, September 14, 2013

How to Use Conditional Formatting in Excel

1. Open a new or existing spreadsheet in Excel 2007. Enter any necessary data.
2. Select the cells that require conditional formatting. Select all cells if you don't need any cells to be exempt from the conditions.
3. On the Home tab of the Ribbon, click 'Conditional Formatting.' Choose 'Highlight Cells Rules,' then select the condition that matches your needs: For text, choose 'Text that contains...,' or for numbers, one of the other options. Let's assume you want to highlight cells containing numbers greater than 10: Choose 'Greater than...'
4. In the resulting dialog box, type '10' into the empty field, then to the right, click the drop-down arrow and choose either an existing format or 'Custom Format' to choose your own cell background and border, font color and more.

How to Jump to a Cell in Excel

1. Press the 'F5' key on your keyboard. The 'Go To' dialog box appears on your screen. Alternatively, press the 'Ctrl' and 'G' keys simultaneously to open the 'Go To' dialog box.
2. Enter the row of the cell you want to jump to in the 'Reference' box. If you want to jump to cell C15, for example, enter 'C.'
3. Enter the column of the cell you want to jump to in the 'Reference' box. If you want to jump to cell C15, you would enter '15.' Your reference should look something like 'C15.' Press the 'Enter' key on your keyboard to jump to the cell.

How to Export Pivot Table Data to Excel

1. Load the Excel pivot table spreadsheet.
2. View the table and decide which components of the pivot data you wish to export to a new Excel spreadsheet. The pivot table divides the spreadsheet data into multiple categories, based on the table's formatting. However, the table does not show the original data, only the summation information. You may wish to export all the original spreadsheet data rows that correspond to a particular number that appears on the pivot table.
3. Double-click on the chosen numerical result in the pivot table. A new spreadsheet is immediately created and all the corresponding data from the original data source is exported from the pivot table field into the new spreadsheet.
4. Click on the pivot table spreadsheet tab to return to the pivot table.
5. Double-click on any other table row's numerical result to create an additional, separate data export into another new Excel spreadsheet. Repeat this process as needed until all the separate pivot data you require is exported. Excel conveniently places each export into a new separate spreadsheet to keep the data sets independent of each other for further analysis.

Friday, September 13, 2013

How to Attach a PDF Document to an Excel Spreadsheet

1. Open or create an Excel spreadsheet that has the information you want to attach the PDF document to. Make any changes and save the spreadsheet.
2. Click the area of your Excel spreadsheet where you want the PDF document attached. Click the 'Insert' drop-down menu in Excel 2003 or earlier versions. In later versions of Excel, click 'Insert' tab.
3. Click 'Object.' Make sure you are on the 'Create New' tab.
4. Select 'Adobe Acrobat Document' from the Object Type list. If you want to attach the entire PDF document as a viewable object on your spreadsheet, click 'OK.' If you want to insert an icon that will be a clickable link to the attached PDF document, select the 'Display as Icon' check box and click 'OK.'
5. Navigate to and select the PDF document you want to attach to your Excel spreadsheet. Click 'Open.' The PDF document will open in a separate window. Close it and you will be returned to to the spreadsheet with the PDF document -- or a link to it, depending on which you chose -- attached.

How to Remove an Excel 2003 Add

1. Open Excel on your computer. Go to the 'Tools' menu.
2. Select 'Add-Ins' from the 'Tools' menu. Check the box next to the program you want to remove in the 'Add-Ins Available' text box. Click 'OK.'
3. Restart Excel to finalize the removal.

How to Use Excel's SUBSTITUTE Function

1. Choose the cell that you want to enter the formula into. Click it with your mouse. Click inside the function (fx) box, just below the Excel menu.
2. Type in the function in this format: '=SUBSTITUTE(text, 'old_text', 'new_text', instance_num).' 'Text' is the cell that contains the text you want to subsitute. 'Old_text' is the text to be replaced and 'new_text' is the text to replace it with. 'Instance_num' is the instance of the text you want to replace.
3. Create an example to learn how the substitute function works. Click on the top gray cell so that the whole spreadsheet is highlighted. Click 'format,' 'cells' from the menu. Select 'text.'
4. Enter 'November 2, 2008' in A1.
5. Click on cell B1. Go to the function box, and type '=SUBSTITUTE (A1, '2', '4', 1). Hit enter. The text in B1 will now read 'November 4, 2008.' Then edit the formula and take out the '1.' All instances of '2' will be changed to '4.'
6. Go to cell B2. In the function box, type '=SUBSTITUTE (A1, '8', '12'). Hit enter. The year now read '2012.' In this case you don't need the 'instance_num,' since there is only one '8' in the cell.
7. Get help and other examples of the substitute function by clicking 'help' on Excel's menu. In the 'keywords' box, type 'substitute worksheet function.'

How to Wire a Macro to Pull From One Spreadsheet to Another

1. Open Excel 2007 and find a workbook. Click the 'Office' button and select the 'Open' icon. Browse your files for the workbook. Click the workbook and click 'Open.' The workbook opens.
2. Click the Developer tab and select 'Record Macro.' The Record Macro dialog box appears. Enter a name for your macro in the Macro Name field. Add a brief description in the Description field. Click 'OK.' The macro starts recording.
3. Link two spreadsheets together by clicking on one cell in your first worksheet. Type '=.' Click on the second worksheet and select a cell that you want to link. Click that cell. Press the 'Enter' key. These two spreadsheets are now linked and the first worksheet will be populated with data pulled from the second worksheet.
4. Click 'Stop Recording' on the Developer tab. You have completed the recording macro process.

Subscribe to: Posts (Atom)