Excel

Browse » Home » All posts

Tuesday, June 11, 2013

How to Search a Cell Range for a Match in Excel VBA

1. Open the Visual Basic Editor (VBE). Click the 'Developer' tab in Excel and then click 'Visual Basic.'
2. Click 'Insert' and then click 'Module.'
3. Cut and paste the following code into the blank window:Sub Find_First()Dim FindString As StringDim Rng As RangeFindString = InputBox('Enter a Search value')If Trim(FindString)
'' ThenWith Sheets('Sheet1').Range('A1:A20')Set Rng = .Find(What:=FindString, _After:=.Cells(.Cells.Count), _LookIn:=xlValues, _LookAt:=xlWhole, _SearchOrder:=xlByRows, _SearchDirection:=xlNext, _MatchCase:=False)If Not Rng Is Nothing ThenApplication.Goto Rng, TrueElseMsgBox 'Nothing found'End IfEnd WithEnd IfEnd Sub
4. Change the range to suit your needs. The range in the code is specified as A1 through A20 (Range('A1:A20')). For example, if you want the range to be B1 through B100, change the range to 'Range('B1:B100').'
5. Press 'F5' to run the macro. A pop-up window will appear on the worksheet. Type the number or phrase you want to search for into the box and then press 'Enter.'

How to Move Rows in Excel

1. Select the row heading (A, B, C and so forth) that you wish to move and highlight it. You can also select several rows by holding down your left mouse button and sliding it across the rows which you would like to select, if you want to move more than one row at a time.
2. Keep your mouse placed on the highlighted row(s) and right click your mouse button. This will bring up a pop up menu with several options to choose from.
3. Choose 'Cut' from the menu that pops up. This will make the row you want to move disappear, but your computer will store it in its temporary memory. As long as you don't cut anything else or close the program, your information will not be lost.
4. Pick the row that is directly below where you want your selected row to be placed, so that it is highlighted. This is how you let your computer know where you want to place the row that you are moving.
5. Locate the Insert menu and click on the 'Cut Cells' option. The row(s) that you chose to cut will be pasted into your selected area.

How to Put a Combo Box in an XLS Cell

1. Open the Excel document to which you want to add a combo box.
2. Click on 'File' in the top-left corner of the Excel window. Choose 'Options' from the list that appears.
3. Select 'Customize Ribbon' on the left side of the new window. Place a check mark next to 'Developer' on the list located to the far right side of the window. Click 'OK.'
4. Click the 'Developer' tab at the top of the screen. Select 'Insert' and choose 'Combo Box (Form Control),' which will be the second option from the left in the top row. Select a cell where you want to enter the combo box.
5. Right-click the combo box and choose 'Format Control' from the drop-down menu. Click the 'Control' tab at the top of the new window. Type in a range of cells into the 'Input Range' field. These cells will contain the data that the user can choose from. Type in a single cell into the 'Cell Link' field. This is the cell where the user's choice will appear. Click 'OK' when you are finished.
6. Click 'Design Mode' at the top of the screen to exit the mode. This will make the combo box unable to be moved or edited. If you need to change the location or data for the box, click 'Design Mode' again, and you will be able to make the changes.

Tuesday, May 28, 2013

How to Insert Hyperlinks in Microsoft Excel

1. Select the cell where you want your hyperlink to be placed. You can either insert a hyperlink into a blank cell or into a cell already filled with information. If the cell is blank, the hyperlink is displayed by its file or Internet location unless you specify different text later. If the cell is already filled, the text will be the link.
2. Choose 'Hyperlink' from the 'Insert' menu. A hyperlink wizard pops up.
3. Note the 4 icons on the left side of the wizard. These icons represent the 4 types of hyperlinks you may insert into Excel. The 'Existing File or Web Page' option is probably the one you used most often. Click it and insert the file location of a file on your hard drive or website address. Change the linked text with the form field at the top if you wish.
4. Pick 'E-mail Address' to create a link to an email address. Enter the email address and the subject any email sent via the link should have. When users click on this link, an email to the linked email address opens in their default mail program.
5. Click 'Place in Document' to link to another sheet in the document.
6. Use the 'Create New Document' option to allow the Excel user to open a new Microsoft Office document with a link. The document can be from any Microsoft Office software you've installed.
7. Hit the 'OK' button to insert your hyperlink into your Excel document.

Monday, May 27, 2013

How to Sort Information in Excel 2007

1. Access a worksheet with data that can be sorted. Open Excel 2007 and either open an existing worksheet or add information into an empty worksheet. Data should be in a table, preferably with assigned column headers to make selecting the sorting criteria easier. Make sure that all of the data in a given column is of the same format so that it is sorted consistently.
2. Choose the data to be sorted. Left-click and drag to select the data. Ensure that both the data and the headers are included.
3. Open the sort dialog. Select 'Data' on the Excel 2007 ribbon, and then click on the 'Sort' icon.
4. Configure the sort options and apply the sort. If you have created column headers, make sure 'My data has headers' is selected. Select the column to sort by in the 'Sort by' drop-down list. Select how to sort the data by selecting from the drop down list, typically by the cell values. You can also sort by cell color, font color or cell icon. Select whether to sort in ascending or descending order by selecting from the 'Order' drop down list. To sort on more than one column, choose 'Add Level' and repeat these selections. To make the sort case sensitive or to sort from bottom to top instead of top to bottom, select 'Options' and choose accordingly. Click 'OK.'

How to Set up a PivotTable

1. Open the Excel worksheet that contains the data you want to make a PivotTable out of.
2. Look through your data and make sure the top cell in every column is the column title. If any of the top cells are empty, add a title that will tell you what the data in that mean. If none of your columns has a titles, right-click on the '1' on the left side of your screen and choose 'Insert.' A new row will appear at the top of the page. Enter column titles into this row.
3. Click on the top-left cell in the range of data you want to use for the PivotTable. Scroll to the bottom-right cell in the field, then hold 'Shift' and click on the cell. The entire data field will now be highlighted.
4. Click on the 'Insert' tab at the top of the screen, and the click the 'PivotTable' button. Click 'OK' on the window that opens up, and a new worksheet will be created for your PivotTable. You will see a blank table on the left side of the screen and a field list on the right.
5. Place check marks next to the fields you want to compare in the PivotTable. Excel will automatically place the fields into one of the four PivotTable categories located to the right of the field list.
6. Drag and drop your chosen fields into the categories where you want them. Each category corresponds to a different area of the PivotTable where the data will show up. You can choose to make the field into a column on the table, a row on the table, the data that makes up the meat of the table or a top-level filter above the table.

Sunday, May 26, 2013

How to Import Delimited ASCII127 in Excel

1. Run the Excel 2010 application.
2. Click the 'File' tab and click 'Open' to open a file browser window. Click the 'Files' drop-down list, and choose 'Text Files.'
3. Navigate the file browser to the delimited ASCII TXT file that you wish to import, then click 'Open.' Excel will initiate the Import Text Wizard.
4. Click the radio button next to 'Delimited,' and click the 'Next' button.
5. Click the radio button next to the character that the ASCII file uses for delimiting fields. If your delimiting character is not in the list, then click 'Other,' and type the character into the adjacent field.
6. Click 'Next' and 'OK' to complete the Text Import Wizard. Excel will import data from your delimited ASCII text file and will automatically assign cell formatting that it deems appropriate for the type of value that is contained in each field.

How to Remove an Unknown Password From a Protected Excel Spreadsheet

1. Download an Excel password recovery software, such as Free Word/Excel Password Recovery Software, Excel Password Remover 2008, or Password Recovery Engine for Excel (see resources). Save the file, then click 'Install' at the prompt.
2. Open the application. Click 'Open,' followed by 'Browse.' Select your protected Excel spreadsheet from the menu. Click 'Recover' and wait for the program to recover your password.
3. Open the Excel spreadsheet using the password. From the 'File' menu, select 'Save As.' Under 'Tools,' select 'General Options.'
4. Double-click the asterisks in both the 'Password to Open' and 'Password to Modify' boxes, then press the 'Delete' key. Click 'OK,' then 'Save.'

How to Convert Microsoft Project to Microsoft Excel

1. Double-click on your Microsoft Project file.
2. Click 'File' at the top of the screen (Project 2003 or earlier) or the Windows button in the top-left corner (Project 2007).
3. Choose the 'Save As' option (Project 2003 or earlier) or 'Save As,' and then 'Other file formats' (Project 2007).
4. Click the arrow for the drop-down menu next to 'Save file type as' and select the 'Microsoft Excel (.xls)' option.
5. Select the location for your saved file and change the name, if necessary.
Click the 'Save' button.

How to Create a Personal Budget Using Excel 2007

1. Open Excel 2007 and select the 'Office' button. Select 'New.' Excel displays the templates available. Click on the word 'Budgets' in the left task pane. Review the available templates and download one of the personal budgets by clicking the 'Download' button.
2. Update your personal budget by adding your income. Locate the income section of the template and add your income. Locate the expense section of the template and add your expenses. If necessary, right click on the row and select 'Insert Row' to add additional rows to the template.
3. Save your changes to the budget by clicking the 'Save' icon in the Quick Access Toolbar.

Saturday, May 25, 2013

How to Have Different Footers on a Word Document

Insert Different Footers in a Word 2003 Document
1. Place your cursor on the page before the one in which you want to create a different footer.
2. Go to the Insert menu and select Break. In the Insert Break dialog box that appears, select Next Page under Section Breaks.
3. Go to the View menu and select Header and Footer. Place the cursor inside the footer section of the first page in which you want to make a different footer.
4. Click the Link to Previous button on the Header and Footer toolbar. This will disconnect the footer from the previous section.
5. Enter the information you desire into the footer and save your document.
Insert Different Footers in a Word 2007 or 2010 Document
6. Place your cursor on the page before the one in which you want to create a different footer.
7. Go to the Page Layout tab and select Breaks from the Page Setup group. Select Next Page under Section Breaks in the Breaks drop-down menu.
8. Click on the page in which you want to start the different footer. Go to the Insert tab and click Footer. Click Edit Footer near the bottom of the Footer drop-down menu.
9. Click the Link to Previous button in the Navigation group on the Design tab of the Header and Footer Tools tab. This will disconnect the footer from the previous section.
10. Enter the information you desire into the footer and save your document.

How to Open a Specific Spreadsheet Each Time Excel Starts

1. Right-click the Windows desktop. Mouse over 'New' and select 'Shortcut.' The 'Create Shortcut' window will open.
2. Click the 'Browse' icon.
3. Locate and select the 'Excel.exe' file. This is the executable file that opens the Excel program. If you installed the program in the default location, the path to the file for the different versions is as follows:Excel 2003 - C:\Program Files\Microsoft Office\Office11\XlstartExcel 2007 - C:\Program Files\Microsoft Office\Office12\XlstartExcel 2010 - C:\Program Files\Microsoft Office\Office14\XlstartIf you can't find the 'Excel.exe' file in the default location, use the search feature on the Windows Start menu to search for it. Click 'OK.'
4. Add the command line to the end of the file path, outside the closed parentheses. The file command to open a specific spreadsheet is as follows:'c:\My Folder\book1.xlsx'The path inside the parentheses is the location of the spreadsheet file you want to open when Excel starts.
5. Click 'Next.' Type a name for the new shortcut. This should be something that you can associate with the spreadsheet that will open. Click 'Finish.' The new shortcut will appear on the desktop represented by the Excel icon.
6. Click the new shortcut to open Excel directly to the desired spreadsheet.

How to Activate Macros in Excel

1. Open Microsoft Excel.
2. Find and click the 'Office' button. It's not labeled, but it's the large round button with the Microsoft logo in the upper left corner of your Excel window.
3. Click on the 'Excel Options' button at the bottom of the resulting menu.
4. Click on the 'Trust Center' option in the list at left, and then click on the 'Trust Center Settings' button.
5. Click on 'Macro Settings' in the list at left, and then click the radio button next to 'Disable all macros with notification.' Using this setting, Excel will notify you when a macro opens and ask you to choose whether or not to run it.
6. Click the 'OK' button to return to your document and exit Excel completely. When you re-open, your settings will be changed.

How to Open XLS Files in a Microsoft Spreadsheet

Using Excel
1. Click the 'Start' button or orb in the lower left corner of your screen, and point your cursor to 'All Programs.' Scroll down, click the 'Microsoft Office' folder, and click 'Microsoft Office Excel.' A blank Excel spreadsheet will open.
2. Click the circular Microsoft Office orb on the top left of the spreadsheet. Click the second option, 'Open.' Click the location of your Microsoft Excel spreadsheet on the left side. People usually store documents on their desktop or in the 'My Documents' folder.
3. Click the drop-down box on the lower right corner of the dialog box, and click 'All Excel files.' This will ensure that you can open the Excel file even if it was created with a different version of Microsoft Excel than the version that you have on your computer. Double-click the folder that the Excel file is located in within the main window. Click the 'Open' button in the bottom right corner.
Without Excel
4. Visit Microsoft's Excel Viewer web page (see Resources).
5. Scroll down the page until you see a blue box with a 'Download' button at the top. Click the button. A dialog box will pop up. Click 'Save File.' The download may take anywhere from a few seconds to a minute, depending on your connection speed.
6. Double-click on the file in your downloads box (ExcelViewer.exe) when it has finished downloading. Click on the 'Yes' button if the computer prompts you for permission to install the file. The installation wizard will launch.
7. Click the check box at the bottom of the wizard to agree to the agreement terms, and click the 'Next' button in the bottom right corner. Click the 'Install' button in the bottom right corner. The program may take several minutes to install. Click 'OK' when the installation has finished. You can now double-click on any Microsoft Excel file to view it.

How to Not Show MS Excel 2007 Page Breaks

1. Open the Excel 2007 program by double-clicking on the Excel document icon.
2. Click on the circular Microsoft Office Button at the top-left corner of the Excel 2007 window.
3. Choose 'Excel Options' from the very bottom of the drop-down box that appears. A new window will open up.
4. Click on 'Advanced' on the left side of the window.
5. Scroll down until you find the 'Display Options for this Worksheet,' which is about half-way down the page.
6. Click on the check mark next to 'Show page breaks' to remove it. Page break lines will no longer show up on your worksheet.

Friday, May 24, 2013

How to Create a Control Chart in Excel

1. Download a control chart template for Microsoft Excel. A good free template is available from www.vertex42.com/ExcelTemplates/control-chart.html.
2. Double-click the downloaded Excel template. The file will open in a Microsoft Excel window.
3. Choose the desired control chart format for your project. The two common control chart designs are listed as separate tabs at the bottom of the Excel window. The 'XbarR' worksheet tab creates a control chart for mean and range calculations. The 'XbarS' spreadsheet works with a control chart for mean and standard deviation.
4. Click on the control chart tab most appropriate for your project.
5. Type in the sample size, 'n', and the 'k' value control limit into the top of the worksheet in the labeled areas.
6. Paste or type the X-bar and standard deviation or range values for your data set into the worksheet's table located below the control chart display. Do not alter the grayed-out columns or the 'Sample' column.
7. View the control chart for your data and parameters. The chart in the main worksheet window automatically updates based on your criteria.

How to Link an Excel 2007 Chart to a PowerPoint 2007

Prepare Your Chart in Excel 2007
1. Open the spreadsheet containing your chart in Excel 2007.
2. Click the chart to select it and then press the 'Ctrl' and 'C' keys simultaneously to copy the chart to the clipboard.
3. Select a blank sheet using the tabs near the bottom of the Excel 2007 window.
4. Press the 'Ctrl' and 'V' keys simultaneously to paste the copied chart into the selected sheet.
5. Save the spreadsheet to confirm the changes.
Link Excel 2007 Chart in PowerPoint 2007
6. Open your presentation in PowerPoint 2007 and then navigate to the slide where you want the chart inserted. If you want to insert the chart within a text box, select the box by clicking it.
7. Select the 'Insert' tab and then click the 'Object' button.
8. Select the 'Create from file' option on the left side of the window.
9. Click the 'Browse' button, locate your Excel 2007 spreadsheet in the resulting window and select it by double clicking its icon.
10. Check the 'Link' box and then click 'OK.'

How to Filter Data in Microsoft Excel 2007

1.
Select the Home Tab on the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.
2.
Locate the Editing Group on the far right side of the Home Tab. Click on the 'Sort and Filter' button to reveal a drop-down menu of options. Click on 'Filter'.
3.
Click on the drop-down arrow that Excel added next to the field you would like the data filtered by. Select the choice you would like to see filtered.
4.
Click on another drop-down menu if you would like to filter the remaining data by an additional criteria.
5.
Click on the filter icon next to the field header used to filter the data to remove the filter and return the list to its original state.

How to Remove Certain Colors in Excel 2007

1. Open the Microsoft Excel 2007 document.
2. Highlight the cells that you want to remove color from.
3. Scroll to the top of the screen and click the 'home' tab.
4. Scroll to the right where there are six boxes stacked on top of one another. Click on the box that says 'normal.' This will remove all color and restore the cells back to normal.
5. Scroll to the paint-bucket icon.
6. Click on it to reveal a drop-down menu.
7. Scroll down to where its labeled 'no fill' and click on it. This removes background color.
8. Scroll to the icon that is a letter 'A' with a color pallet beneath it.
9. Click on this to reveal a drop-down menu.
10. Scroll to where it labeled 'automatic' and click on it to make it black.

Thursday, May 23, 2013

How to Create a Pivot Table in Excel

Create a Basic Pivot Table From an Excel List
1. Ensure your data source contains all data elements you want to include in your report. All data ranges need to have labeled rows, columns and associated data values.
2. Select the 'PivotTable and Pivot Report' option from the Data menu. This option launches a wizard to aid you in creating a report.
3. Choose the 'Microsoft Office Excel List or database' data source option from the wizard. This option specifies that your data resides in an internal spreadsheet.
4. Specify PivotTable as your desired report. This triggers the data range prompt to appear.
5. Highlight the range that contains the data you would like to include. Start from the top left corner of your data set and end with the bottom right cell.
6. Decide whether you want the report to display in the worksheet with the raw data or in a separate worksheet. Although it is cleaner to have the report in a new worksheet, there is no functional difference between the two options.
7. Drag and drop the data objects from the pivot table field list onto the worksheet. The report areas include space for row, column, data and page field values. You can move the objects around until the report layout is satisfactory.
Make a Pivot Table From Multiple Ranges
8. Follow Steps 1 and 2 in Section 1.
9. Choose 'Multiple Consolidation Ranges' as your data source. This will allow Excel to pull from more than one list.
10. Specify how many page fields you want, from zero up to four. You can either create them manually or allow Excel to create them.
11. Add all ranges that contain report data. You can add, delete and browse for additional .xls files that include relevant information.
12. Complete Steps 6 and 7 in Section 1.
Generate a Pivot Table From External Files
13. Follow Steps 1 and 2 within Section 1.
14. Choose 'External Data Source.' Excel lists five options as possible data sources: dBase, Excel, HT8PRD, MS Access Database and Visio Database Samples.
15. Specify the appropriate data source(s). Browse through your directory to select the correct file.
16. Complete Steps 6 and 7 in Section 1.

How to Create Bins in Excel 2007

Creating Bins in Excel 2007
1. Click the Microsoft Office link in your start box and click on Microsoft Excel 2007. Microsoft Excel 2007 is the green icon, located among the choices of Microsoft programs.
2. Click 'Data Analysis,' located in the analysis group on the data tab. In the dialog box after clicking data analysis, click 'Histogram' and then click OK.
3. Type your desired input range in the 'Input Range' box.
4. Type your desired range to create a new bin in the 'Bin Range' box.
5. Click 'New Workbook' which is located under 'Output Options.' Check the 'Chart Output' check box then click OK. Your new bin within the workbook is created.

Wednesday, May 22, 2013

How to Use the Selection Pane in Excel

1. Start Microsoft Excel 2007, and open an existing workbook from your files that contains multiple images that you would like to view with the selection pane.
2. Click to select any one of your images so you can gain access to the Format ribbon. You can tell an image is selected, because it will be surrounded by white sizing handles and the 'Format' tab will appear at the top of the Excel screen.
3. Select the 'Format' tab to display the Format ribbon. This ribbon displays all the settings you can use to format images in Excel.
4. Choose the 'Selection Pane' button in the 'Arrange' section of the Format ribbon. The 'Selection and Visibility' pane will appear on the right side of the Excel screen. This pane displays a list of all the images that appear in the Excel worksheet.
5. Click the button with a picture of the eye to the right of the names of the pictures to hide any of the pictures. Clicking the button again to display the eye will make that image visible.
6. Choose the 'Show All' button at the bottom of the 'Selection and Visibility' pane to show all of the pictures in the workbook. Choose the 'Hide All' button to hide of the pictures.
7. Select the name of a picture and use the up and down arrow buttons at the bottom of the selection pane to change the order of the images on the worksheet.

How to Write Macros in Excel 2010

1. Open Excel on your computer. Click the 'View' tab on the ribbon and then click the 'Macros' icon.
2. Enter a descriptive name for the new macro. Do use spaces in the macro name. Create a sample macro to use during the process of learning to write a macro in VBA. Therefore, enter the macro name 'Change_Worksheet_Names' in the macro name field and then click 'Create.' The Microsoft Visual Basic for Application window opens and displays a 'Module (Code)' window with the blank 'Sub Change_Worksheet_Names()' code document.
3. Continue creating the sample macro, which changes all of the generic 'Sheet1,' 'Sheet2' worksheets in a workbook to a value from a header or title cell in each worksheet. For example, if you enter the title for each sheet in cell 'A1' of each worksheet, this macro changes the name displayed at the bottom of each worksheet to the value in the referenced cell -- in this case 'A1.' To create the macro using VBA, enter the following code between the 'Sub Change_Worksheet_Names()' and 'End Sub' tags:Dim myWorksheet As WorksheetFor Each myWorksheet In Worksheets'The next line of code verifies that cell A1 in each worksheet is not empty. This text is a code 'remark.' The single quote mark at the beginning of the line informs Excel not to include this text in the macro code. Remarks are a good way to leave notes in VBA code that explain commands or syntax used.If myWorksheet.Range('A1').Value
'' Then'This command renames the worksheet to the text value in cell 'A1' of the first worksheet.myWorksheet.Name = myWorksheet.Range('A1').ValueEnd If'The 'Next' command instructs Excel to repeat the above code commands until it has finished renaming all the worksheets in the active workbook.Next
4. Click the 'Save' icon beneath the menu bar in the VBA editor window. Enter a name for the Excel template that contains the macro code. Select 'Excel Macro-Enabled Workbook' as the file type and then click 'Save.' Close the VBA editor window.
5. Test the macro for the sample VBA code. Enter a header or sheet title name in cell A1 of each worksheet. For instance, enter 'Daily,' Weekly' and 'Monthly' in cell A1 of three worksheets. The tabs for each worksheet at the bottom of the Excel window should read 'Sheet1,' 'Sheet2' and 'Sheet3,' respectively.
6. Click 'Macro' on the 'View' tab. Highlight the 'Change_Worksheet_Names' macro and then click 'Run.' The worksheet tab names at the bottom of the window change to 'Daily,' 'Weekly' and 'Monthly.'

Tuesday, May 21, 2013

How to Insert Error Bars in Excel 2007

1. Open the Microsoft Excel 2007 application that contains the chart that you want to add error bars to.
2. Click on the area of the chart where you want to add the error bars. Click on the “Format” tab and then click on the arrow next to the “Chart Elements” box.
3. Click on the chart element from the list that you want to add the error bars to. Click on the “Layout” tab and then click on the “Error Bars” option from the “Analysis” group.
4. Select the “Error Bars with Standard Error”, “Error Bars with Percentage” or “Error Bars with Standard Deviation” option.
5. Click on the “More Error Bar Options” button and then click on the “Vertical Error Bars” or “Horizontal Error Bars” option.
6. Click on the “OK” button and the type of error bars you selected will appear on your chart.

How to Paste a Function in Excel 2003

1. Click to select the cell that you want to copy. To select multiple cells, click and drag your mouse to highlight the range or hold down the Ctrl key and click each cell you wish to copy.
2. Press Ctrl C on your keyboard to copy the cells, or click 'Edit' and then 'Copy' on the main toolbar.
3. Click in the blank cell where you want to paste the formula.
4. Right-click, then choose 'Paste Special,' or click 'Edit' and then 'Paste Special' to display the 'Paste Special' dialog box.
5. Select the 'Formulas' radio button, then click 'OK.' The formulas or functions are pasted into your spreadsheet.

How to Add the Microsoft Date Time Picker in Excel 2007

1. Download Microsoft Windows Common Controls-2 6.0 from the Microsoft website.
2. Extract the .cab file's contents. Newer Windows versions open .cab files directly. Older versions need extraction software such as WinZip or WinRAR.
3. Run the 'mscomct2.bat' file. This installs the Microsoft Date Time Picker.
4. In Excel, click the Office button on the screen's top left corner. Select 'Excel Options.' The 'Excel Options' window will open.
5. Click 'Popular' from the top of the left column.
6. Check the box next to 'Show Developer tab in the Ribbon.' Close the window. Excel's ribbon now contains the Developer tab to the far right.
7. Click on the Developer tab to show its controls. Click the 'Insert' button, and select the bottom right icon, which shows additional ActiveX controls. The 'More Controls' window will open.
8. Choose 'Microsoft Date and Time Picker Control 6.0 (SP4)' and close the window. Your mouse cursor will turn into cross hairs.
9. Click and drag anywhere on your spreadsheet to create a date box.

Monday, May 20, 2013

How to Create Multiple Directories From an Excel Spread Sheet

1. Click on the 'Office' button.
2. Mouse over 'Save As.' This displays an options list. Click on the type of file you want to save. For example, 'Excel workbook.'
3. Click on the directory you want to save the file in, or click on the 'New Folder' icon to create a new directory. For example, click on 'My Documents' or create a new folder called 'My business tax documents.'
4. Enter the name of your file in the 'File name' box.
5. Click on 'Save.'
6. Repeat steps 1 to 5 to save the file to as many directories as you wish.

How to Record an Excel Macro

1. Open the Tools menu and select Macro, then select Record a Macro.
2. Fill in the blanks - the macro name, description, shortcut-key combination and worksheet where the macro will be available - in the Record Macro box that appears. The macro can be available from only one worksheet or from any worksheet.
3. Click on OK.
4. Notice that the word 'recording' appears in the lower left of the Excel screen while recording is in progress.
5. Perform a series of keystrokes or mouse actions. The macro will record them. You can record any normal Excel action.
6. Open the Tools menu and choose Stop Recording to complete the macro.
7. Activate the macro by pressing the shortcut keys you have selected or by selecting the macro from a list on the Tools menu. The macro will then reproduce the series of keystrokes you recorded.

How to Create Microsoft Flow Charts

1. Open Excel 2010 and click the 'Insert' tab. Select 'SmartArt.' Click the 'Process' category in the left task pane. Click on the different process flow charts in the center task pane. Select a flow chart. For example, select the 'Basic Bending Process' flow chart. Click 'OK.' The SmartArt flow chart and Text Pane appear.
2. Type your flow chart tasks in the bulleted 'Text' pane. Move to each shape in the flow chart by pressing the 'Enter' key as you complete each task. Click on the outside border of the flow chart pane.
3. Notice the new 'SmartArt' tab on the ribbon. Use the 'Design' tab to add color schemes to the flow chart. Click the 'Change Colors' button to add a new color scheme to the flow chart. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar. The Quick Access Toolbar is located above the ribbon.

How to Remove a Hyperlink in Excel 2003

1. Right-click the cell with a hyperlink.
2. Click 'Hyperlink.'
3. Select 'Remove Hyperlink.' The hyperlink is now removed from your Excel document.

How to Set Page Breaks in Microsoft Word 2003

1. Set your cursor where you want to page break to occur. Scroll to the “Insert” tab and the click on “Break.” Select “Page Break” from the drop down menu.
2. Insert a page break using hotkeys. Select where you wish for the break to occur and the press hotkeys “CTRL ENTER” to create the break.
3. Remove a page break. To remove a page break, left-click on the page that the break begins and press the “Backspace” button to delete the added page.
4. Insert a custom page break. Scroll to “Insert” and then select “Break.” You can choose several custom breaks including: page break, a column break and a text wrapping break. Select the desired break by clicking on the corresponding radial button.
5. View page breaks in print layout. Click on “View” and then click on “Print Layout.” This will show you what the printed version of your document will look like, including any added breaks.

Sunday, May 19, 2013

How to Align Images in Excel

1. Open Microsoft Excel 2007 and open a workbook from your files that contains at least two images you wan to align to each other.
2. Hold down the 'Shift' key on your keyboard and click to select the first image you want to align. Continue holding down your shift key and click to select any other images that you want to align in relation to the first image. Once selected, the images will be surrounded by a thin, black line and white sizing handles.
3. Select the 'Format' tab from the top of the screen (if necessary) to display the Format ribbon. Locate the 'Arrange' section in the Format ribbon. The Arrange section is the second to last section in the Format ribbon and contains all the commands related to arranging images in Excel.
4. Click the 'Align' button from the Arrange section in the Format ribbon to display a drop-down menu of the options you can use to align the selected images.
5. Choose to align the images left, center, right, top, middle or bottom by choosing a selection from the Align drop-down menu. The selected images will immediately align according to the direction you chose from the drop-down menu.

How to Graph Linear Equations in Excel 2007

1. Open a new document in Microsoft Excel 2007.
2. Enter 'x' in the top left cell. Press 'Tab' and enter 'y' in the next cell over.
3. Enter a series of values in the first column under 'x' (cells A2 and down). These values will make up the parameters of the horizontal axis. For instance, enter '-5' in cell A2. In the next cell down, enter '-4' and onward: -3, -2, -1, 0, 1...all the way up to 5.
4. Define the name of the x values so you can enter the formula for the 'y' column. Use your cursor to highlight the cell values in column A. In other words, highlight cell A2 and drag your cursor down to the last cell in column A. Click the 'Formulas' tab. In the 'Defined Name' group, click 'Define Name.' Enter 'x' in the 'Name' field and click 'OK.' This tells Excel that all the numbers in column A are equal to a value of x.
5. Enter a linear equation in cell B2. The equation will appear in the Formula Bar. With the format y=mx b, type '=m*x b.' When you go to a new cell, Excel will calculate the result of the formula, which will now appear in the cell.
6. Copy the formula you entered is Step 5 to the other cells in column B. Select cell B2, which contains the formula. Click the 'Home' tab. Under 'Clipboard,' click the 'Copy' icon. Highlight all the cells below B2. In the 'Clipboard' group, click 'Paste.' Select 'Formulas.' This will add the formula to each cell. You'll now see the y-value for each corresponding x-value.
7. Select all the cells to begin making a graph for the data.
8. Click the 'Insert' tab. Click the type of chart you want in the 'Charts' group. For the purpose of making a linear graph, choose 'Scatter.' This will plot the x and y values against each other. Choose the chart subtype you want. To show a simple line graph with your data points, click the 'Scatter with Smooth Lines and Markers' icon.
9. Click the chart to display the 'Chart Tools' tabs. Use the 'Design' tab to change the chart's layout and style. Click the 'Layout tab to make modify the position of title and axis labels. Select the graphed line and click the 'Format' tab to adjust shape styles.
10. Click the 'Microsoft Office Button,' and click 'Save as' to save the data and graph.

How to Make Text Vertical in Excel

1. Click the Excel cell containing the text you want to rotate.
2. Click 'Home' at the top of the Excel window.
3. Click on the 'Orientation' button. The 'Orientation' button has 'AB' written diagonally with a diagonal arrow pointed up on it. It is located under the words 'Data' near the top of the Excel window.
4. Select 'Vertical Text:'

How to Do a Calibration Curve on Excel

1. Run a set of known X variables through the equipment to produce a series of Y outputs.
2. Open Microsoft Excel. In cell A1 type 'concentration.' In cell B1 type 'output' or the appropriate unit of measure for your experiment.
3. Enter experimental concentrations for the X variable in column A, starting at A2.
4. Enter experimental outputs for the Y variable in column B, starting at B2
5. Highlight entire data set.
6. Click the 'Insert' ribbon.
7. Click the 'Scatter' button and select the 'Scatter plot with markers and straight lines.' The graph should now appear and the Excel ribbon should default to design view.
8. Click on the graph. In the Design ribbon under 'Chart layouts,' select 'Layout 9.' The calibration curve is complete. A y=mx b equation will appear in the graph along with the R2 value.

Saturday, May 18, 2013

How to Make a Graph on Excel With X Y Coordinates

1. Open a new Microsoft Excel 2010 worksheet. Click on cell 'A1' and type in the header for your first series of data. This series will wind up on the horizontal, or 'X,' axis.
2. Click on cell 'B1' and enter the header for your second series of data. This series will be plotted on the vertical, or 'Y,' axis.
3. Select cell 'A2' and enter the first value you want to plot. Then select cell 'B2' and enter the corresponding coordinate. Continue entering your coordinates down the columns until you have entered all your data.
4. Click on any cell in the data field you have just created. Click 'Insert' at the top of the Excel window, and then click the 'Scatter' button in the Charts area on the ribbon.
5. Click on one of the five scatter chart types that appear in the pop-up menu. You can choose to plot just the markers for the data points, straight lines between the points, curved lines between the points, or either of the types of lines and the markers at the same time. Click on a scatter chart type and your graph will appear on the spreadsheet.

How to Create Criteria Ranges in Excel for Database Functions

1. Open the Excel application on your computer that contains the table of database functions that you want to create criteria for.
2. Highlight the range of cells containing numeric data using your mouse. Click on the “Data” tab for Excel 2007 or the “Data” option from the top toolbar menu for Excel 2003.
3. Click on the “Filter” option and the click on the drop-down arrow located in the column header.
4. Move your mouse over the “Number Filters” option and then click on the “Custom Filter” option. The Custom AutoFilter dialog box will appear.
5. Select the number criteria for your ranges by entering the numbers you want to only be included in your criteria range. An example is to enter “25” and “50” for the lowest and highest number.
6. Select the “And” option if you want to filter so that all criteria is true, or click on the “Or” option for the table column or section to be true together or separately. Your criteria will then be created.

How to Restore Gridlines in Excel 2003

1. Click 'Tools' in the menu bar.
2. Select 'Options.'
3. Click on the 'View' tab, then check the 'Gridlines' box.
4. Save your document.

How to Convert a Microsoft Word Table Into an Excel Workbook

1. Open Microsoft Word and Excel from the desktop menu. In Word, find the document containing the text to be converted. In Excel, open a blank worksheet.
2. Return to the Word document and confirm that the text appears in true table format. If there are gridlines or a compass symbol enclosed in a box at the top left corner of the first column, the text is a true table. If not, the text appears in columned format only and must be converted to a true table.
3. Convert columned text to a true table by inserting a tab, a comma or an asterisk in between each row of the columns. Highlight all the text. Click 'Table' on the 'File' menu and scroll down to 'Convert.'
4. Choose the text appearing to the right, 'Text to Table.' A dialog box opens entitled, 'Convert Text to Table.' Enter the number of columns and rows. Go down to 'Separate Text At' and type in the symbol used to divide the columns. Hit 'OK.'
5. Click on the box containing the compass symbol in the top left corner to select the entire table for exporting to Excel. Under 'Edit' on the 'File' menu, choose 'Copy.' Switch back to the Excel program and click inside the cell where the table should be placed. Go to 'Edit' and choose 'Paste.'
6. Format the Excel worksheet to accommodate the table with a new look. Adjust the width of columns and the height of rows so that the text fits neatly.
7. Keep the same appearance it had in the original Word document by locating the 'Paste Options' icon in the lower right corner of the pasted text. Click on the drop-down arrow and choose 'Keep Source Formatting.'

How to Unfreeze Panes

Freezing Panes in Excel 97/2000/XP/2003
1. Open the spreadsheet you want to freeze.
2.
Select the row below the one you want to freeze by clicking on the row header.
3. Select 'Window' from your menu and choose the 'Freeze Panes' option.
Unfreezing Panes in Excel 97/2000/XP/2003
4. Open the spreadsheet with frozen panes.
5. Select 'Window' from the menu.
6. Choose the 'Unfreeze Panes' option.
Freezing Panes in Excel 2007
7. Select the row just below the one you want to freeze by clicking on the row header. This will make a specific row or rows visible as you scroll within Excel. Select the 'View' tab in the 'Windows' group, and choose 'Freeze Panes.' Select 'Freeze Panes' from the pop-up list to keep rows visible while your worksheet scrolls.
8.
Make a specific column or columns visible as you scroll within Excel by selecting the column to the right of the one you want to freeze and clicking on the column header. Select the 'View' tab from the 'Windows' group and choose 'Freeze Panes.' Select 'Freeze Panes' from the pop-up list to keep columns visible while your worksheet scrolls.
9. Make both the column(s) and rows(s) visible as you scroll within Excel by selecting the cell below and to the right of the columns and rows you want to freeze. Select the 'View' tab from the 'Windows' group and choose 'Freeze Panes'. Select 'Freeze Panes' from the pop-up list to keep rows and columns visible when you scroll.
10. Freeze or lock only the top row of an Excel worksheet by selecting the 'View' tab from the 'Windows' group. Choose 'Freeze Panes' and select 'Freeze Top Row' from the pop-up list.
11. Freeze only the first column of an Excel worksheet by selecting the 'View' tab from the 'Windows' group. Choose 'Freeze Panes' and select 'Freeze First Column' from the pop-up list.
Unfreezing Panes in Excel 2007
12. Selecting the 'View' tab from the 'Windows' group.
13. Choose 'Freeze Panes'.
14. Select 'Unfreeze Panes' from the pop-up. This will unfreeze any locks you've created in the worksheet.

Friday, May 17, 2013

How to Move Averages in Excel 2010

1. Enter the date, or other reference information, in column A, and enter its corresponding data in column B. Repeat for each data point. As an example, you might have 'Jan 1, 2011' in cell A1 and '$43.25' in cell B1. On the next row, you might have 'Jan 2, 2011' in cell A2 and '$44.50' in cell B2. This continues for however many data points you have, but you will need several data points to construct a moving average.
2. Click the cell in column C whose row number corresponds to the number of data points you want to include in the moving average. As an example, to include the last 20 data points, click cell C20.
3. Type '=AVERAGE(range)' and press 'Enter,' replacing 'range' with the actual range of data points. In the example, this would be 'B1:B20,' so you would enter '=AVERAGE(B1:B20)' in cell C20.
4. Click the cell you just modified, hold the 'Ctrl' key and press 'C' to copy the cell formula.
5. Hold the 'Shift' key and click the last cell in column C that corresponds to the last data point. This selects all the cells between those two cells. As an example, if your last data point was at B40, hold the 'Shift' button and click cell C40. This selects cells C20 through C40.
6. Hold the 'Ctrl' key and press 'V' to paste the formula in all the selected cells. The formulas are automatically altered to reflect their new positions and will include the same number of most recent data points. All these calculations are your moving averages for the corresponding data points.

How to Make Double Line Graphs on Excel

1. Open the Excel worksheet that contains the data values.
2. Click and drag on the categories and two data series you want to plot.
3. Click the “Insert” tab in the Command Ribbon.
4. Click the arrow for the “Line” button in the Charts group. A gallery of seven thumbnail charts appears.
5. Click the preferred chart sub-type.
6. Click “OK.” The two data series convert to a line graph. The Chart Tools Ribbon displays.
7. Customize the chart with the commands in the Chart Tools Ribbon. Chart Tools contains three tabs: “Design,” “Layout” and “Format.” Experiment with the commands in each tab to increase readability and add visual interest. For example, the Design tab contains 12 chart layouts and 48 chart styles.

How to Format Dates in Microsoft Excel 2003

1. Select a cell and activate it. You will need to first left-click on a cell that you wish to enter a date into.
2. Create a date inside of a cell. Enter in any date that you like in the cell and then press enter; this will store the information into that cell.
3. Access the cell formatting menu. To access this menu, right-click on the cell that you just entered the date into and select “Format Cells.”
4. Format the date as desired. Under the cell formatting properties menu, left-click on the “Number” tab and then scroll to and highlight the “Date” field. Under the “Type” box you can scroll to the desired date format. When finished, click on the “OK” button to implement the changes.

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.

Thursday, May 16, 2013

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.

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.

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.'

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.

Wednesday, May 15, 2013

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.

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.

Subscribe to: Posts (Atom)