Excel

Browse » Home » All posts

Saturday, June 22, 2013

How to Create a Drop

1. Click the 'Office' button, and then click 'New.' Click the 'Create' button on the dialog box to accept the default values for a new Excel workbook.
2. Click the 'Office' button again, and then click the 'Options' button. Click the 'Show developer tab' check box to display the tab containing the drop-down calendar control.
3. Click the 'Developer' tab, and then click the 'Insert' button of the 'Controls' panel. Excel will display a list of controls you can insert in your workbook. None of the displayed controls is a drop-down calendar, but Excel enables access to more controls than those shown.
4. Click the icon in the lower-right corner of the control gallery that appears, to run the 'More controls' command. Excel will display a dialog box that lists additional controls you can enter in your workbook.
5. Click the item labeled 'Microsoft date and time picker,' and then click 'OK' to insert the calendar in your worksheet.
6. Right-click the calendar, and then click 'Properties' to display a window allowing you to process the date that a user picks with the calendar control.
7. Type the reference for a spreadsheet cell in the row labeled 'Linked cell.' For example, type 'G4' to indicate cell G4 of the current worksheet. The calendar control will insert the date the user picks in this cell.
8. Click the 'Design mode' button of the 'Controls' panel to exit design mode and enter user-input mode.
9. Click the down arrow of the calendar control, and then click any date in the drop-down calendar that appears. The cell whose reference you entered in Step 7 will fill with the date you chose.

How to Get the Ruler to Show Up in MS Excel

1. Open 'Microsoft Excel.'
2. Select the 'View' tab.
3. Click 'Page Layout,' located in the 'Workbook Views' section.
4. Change your measurement options by clicking the 'Microsoft Office' button. Select 'Excel Options' and choose 'Advanced.'
5. Click the units to use from the 'Ruler Units' box located under 'Display.'

How to Make a Normal Distribution Curve in Excel 2007

1. Open a new worksheet in Excel.
2. Fill in your x-values in column 1, starting at cell A1. For example, if you want your x-values to be from -10 to 10, write '-10' in cell A1, '-9.5' in cell A2, and '-9' in cell A3, all without quotes. Continue down the column until you have entered all of your values through to cell A41.
3. Enter '=NORMDIST(A1,0,1,0)' without quotes into cell B2. This formula is for the standard normal distribution, with a mean of 0 and a standard deviation of 1. You can enter any mean or standard deviation into this formula. For example, if you want a mean of 10 and a standard deviation of 2, enter '=NORMDIST(A1,10,2,0)' without quotes .
4. Highlight the two columns by left clicking on cell A1 and dragging your cursor down to cell B41.
5. Choose 'Insert | Scatter| Scatter with Smooth Lines and Markers.'

Friday, June 21, 2013

How to Display Fractions in Excel

1. Start the Microsoft Excel program.
2. Open the document you wish to add the fractions to by clicking 'Open' on the toolbar. Select 'Open' from the drop-down list. Select the file from the list.
3. Select the box, or cell, where you want to display the fraction. If you want to display fractions in multiple cells, highlight all of the cells.
4. Right-click on the cell and select the 'Format Cells' option. Another alternative is to click on 'Format' in the toolbar at the top and then select 'Cells' from the drop-down menu.
5. Pick the 'Numbers' tab. A list of categories is displayed. Select 'Fraction' from the category list.
6. Select the type of fraction you want to display. Click 'OK' to format the cell and close the display.
7. Type the fraction into the cell. For example, type '1/2' without any spaces. Excel automatically reduces fractions; if you enter 4/8, it changes to 1/2.
8. Input a whole number and a fraction in the same cell by typing the whole number, a space and then the fraction.

How to Convert Excel to Access

1. Edit the Excel file. Eliminate any rows or columns that you don't want to import. If you only want some of the data in your Access database, create a new named range that contains only the cells you want to include.
2. Create the new Access database. Click on File, get external data, import. Change the file type to excel (xls) and then select the spreadsheet to import. You will have to do one table within the spreadsheet at a time. If import errors occur, you may need to make corrections to the spreadsheet, then do the import process again.
3. Access will add a primary key by default. Primary keys are helpful if you will be linking tables. If you already have an idea of what tables to link, you can define the field that will be the primary key during the import. For example, if two tables both contain a household inventory and you want these tables to be combined on a form, you can select the name of the inventory item as the primary key.
4. Import spreadsheets manually by selecting the entire spreadsheet in Excel, if you prefer. Click the top-left corner to highlight the whole worksheet. Click ctrl-c to copy the columns. Create a new table in Access, then click the top left corner, and click ctrl-v to paste. Your Excel tables will be pasted into the Access database.
5. Create forms for entering data and reports to generate reports by using the wizards for forms and reports.

How to Make Column Titles in Excel 2007 Appear at the Top of Each New Page

1. Open the Excel 2007 file that contains your worksheet. Select the worksheet that you want to print from the list of worksheets at the bottom of the Excel window.
2. Click the 'Page Layout' tab at the top of the screen. Locate the 'Page Setup' area of the ribbon and click the 'Print Titles' button.
3. Select the 'Sheet' tab in the window that appears. Find the entry that reads 'Rows to repeat at top' and click the small box to the right. The 'Page Setup' window will minimize.
4. Select the row that contains the titles you want to repeat. This is normally the first row, but can be any row you desire. If you choose a row that is not on the first page, the column titles will not appear on any page before the one you selected. Click the small box in the minimized 'Page Setup' window to bring the window back up. You'll see your select row next to the 'Rows to repeat' entry.
5. Select 'OK' to close the window and complete the process, or you can click 'Print Preview' to get an idea of how the pages will look.

How to AutoFit Column Width in Excel

1. Open Microsoft Excel using the 'Start' menu or a shortcut. Open the document with which you want to work.
2. Click the column you want to modify. To select more than one column, press and hold the 'Ctrl' key while clicking on each column.
3. Click the 'Format' drop-down menu in the 'Cells' group of the 'Home' tab. Select the 'AutoFit Column Width' option in the 'Cell Size' section.

Thursday, June 20, 2013

How to Use Excel for a Questionnaire

1. Click the 'Developer' tab, and then click the 'Insert' command on the 'Controls' panel. A gallery of controls for your questionnaire will appear.
2. Click the 'Text box' button, whose icon looks like a rectangle with text. Drag on the current spreadsheet to grow the text box.
3. Right-click the text box, then click 'Properties.' Excel will display a window showing modifiable attributes of the text box control. Type'J5' in the 'LinkedCell' rows of the 'Properties' window. Excel will use this cell reference to insert questionnaire data entered into the text box control.
4. Use the instructions from the Step 2 and Step 3 to create another text box below the first one. However, type 'K5' for the 'LinkedCell' reference this time.
5. Use the instructions from Step 1 through Step 4 to insert a check box control below the second text box. This control looks like a small square. Type 'L5' for the LinkedCell reference.
6. Type the following labels in the cells to the left of your three controls: 'First name,' 'Last name,' and a sample yes or no question, such as 'Check here if you think insects should be given equal rights.'
7. Click in cell 'J4,' and then type the following column labels. Press the 'Tab' key in place of the commas:'First name,' 'Last name,' 'Question.'
8. Run the questionnaire by clicking the 'Design mode' button of the 'Controls' tab. Type your name in the text boxes, and click the check box control. Notice that Excel automatically populates the columns you typed in the previous step with your answers.

How to Convert Excel 2007 to Excel 97

1. Open the workbook in Excel 2007.
2. Click the 'Office' button.
3. Choose 'Save As'.
4. Click on 'Excel 97-2003 Workbook' from the list of 'Save As' options. Excel will save the file in Excel 97 format. The file can now be opened in Excel 97.

How to Make a Field Mandatory in an Excel Spreadsheet

1. Launch Microsoft Excel.
2. Click the 'Office' button at the top of the screen, then select the 'Excel Options' button at the bottom of the window. Choose the 'Show Developer Tab in the Ribbon' option, then click the 'OK' button.
3. Press 'Alt F11,' then double -click the sheet in the left-hand column that will have the mandatory field.
4. Type the following VBA code into the window:Sub Button1_Click()If Range('A2').Value = '' ThenMsgBox ('Please insert value in cell A2')Exit SubEnd IfEnd SubYou can replace each instance of 'A2' to correspond to the cell or range of cells you are making mandatory. Additionally, whatever function this button will perform will be defined between 'End If' and 'End Sub.'
5. Return to your spreadsheet, then click the 'Developer' tab at the top of the screen and click the 'Insert' button.
6. Select the 'Button' option from the 'Forms' toolbar, then select a location on your spreadsheet for the button.
7. Click the 'Button1_Click' option from the list under 'Macro Name,' then click the 'OK' button.

Wednesday, June 19, 2013

How to Remove Conditional Formating in an Entire Sheet in Microsoft Excel 2003

1. Click 'Start,' then 'All Programs,' then 'Microsoft Excel.'
2. Press 'Ctrl' 'O.' Locate and open your workbook that contains conditional formatting.
3. Press 'Ctrl' 'A' twice to highlight your entire worksheet.
4. Click 'Format,' then 'Conditional Formatting.'
5. Click 'Delete,' then check all three of the check boxes. Click 'OK' to remove the formatting.

How to Apply Themes and Styles in Excel 2007

Themes
1. Open a spreadsheet in Excel 2007. A spreadsheet allows you to enter data in the format of rows, columns and individual cells.
2. Type data into rows and columns. Organize your numerical data or details into an organized spreadsheet.
3. Open the 'Page layout' tab. Click on the 'Theme' group. View options to change colors, fonts or effects.
4. Apply a template theme by clicking the theme under the Built-In tab.
5. Create a custom theme. Click the document theme that you want to use under 'Custom.'
6. Look for more by clicking on 'Browse for Themes' to search for more theme ideas.
7. Change fonts, effects and colors to make your theme become more unique and original.
Styles
8. Apply a Quick Style to your spreadsheet. A Quick Style is a collection of formatting alternatives that make designing your documents more convenient.
9. Utilize a cell style. A cell style is a defined set of formatting uniqueness in fonts, font sizes, number formats, shading and borders.
10. Select a built in cell style that is modified immediately after being selected.
11. Create your own cell style by modifying and duplicating a built in cell style.

How to Hide X on the UserForm Title Bar in Excel 2003

1. Open your Microsoft Excel 2003 document and scroll to the right until you get to the “X” column.
2. Right-click on the column header (“X”) in the user form title bar and select the “Hide” option. The “X” column will now be hidden from view in the spreadsheet.
3. Click and drag from the “W” column to the “Y” column to select both columns and then right-click on one of them. Select the “Unhide” option to unhide the “X” column again.

How to Revert Excel 2007 to A1 Format Instead of R1C1

1. Click on the Windows 'Start' button and select 'All Programs.' Open the 'Microsoft Office' folder, and then click on 'Microsoft Office Excel' to launch the application.
2. Click on the 'Office' button in the top-left corner of the screen and then click the 'Excel Options' button at the bottom of the menu.
3. Click on the 'Formulas' tab in the 'Excel Options' window. Under the 'Working with Formulas' section, clear the checkbox labeled 'R1C1 Reference Style.'
4. Click the 'OK' button to apply the setting. The change to the cell referencing scheme happens instantly, and you will see the columns are now labeled with a letter rather than a number.

How to Capitalize the First Letter in Excel

1. Open the Excel 2010 file that contains the text that you want to capitalize.
2. Right-click on the column letter directly above the first cell that contains your text. Choose 'Insert' from the pop-up menu and a new column will appear to the left of your selected column.
3. Click on the cell directly to the left of the first cell that holds your text.
4. Enter the following formula into the cell: =upper(left(XX,1))right(XX,LEN(XX)-1) where 'XX' is the column letter followed by the row number of the cell to the right of the current one. Press 'Enter' when you are finished entering the formula and you should see your text repeated in the formula cell, except with the first letter capitalized.
5. Click the cell where you entered your formula and press 'Ctrl' and 'C' to copy the formula to the clipboard. Paste the formula, using 'Ctrl' and 'V', to an empty cell to the left of any text you want to capitalize this way. You may need to insert additional columns to get an empty cell to the left of your text.
6. Right-click the cell with your formula and choose 'Copy' from the list of options. Even if you already have this cell in the clipboard, it is better to be sure that you are copying the correct cell.
7. Right-click on the cell with the original text. Move your mouse over 'Paste Special' in the pop-up menu and choose 'Paste Values.' Repeat this for any other cells that you are capitalizing.
8. Right-click on the letter above the column you created earlier in the process. Choose 'Delete' from the menu to remove the column, leaving you with just the capitalized text. Repeat this for any other columns you created.

Subscribe to: Posts (Atom)