Excel

Browse » Home » All posts

Tuesday, May 21, 2013

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

Subscribe to: Posts (Atom)