Excel

Browse » Home » All posts

Wednesday, March 28, 2012

How to Create a Drop Down List in Microsoft Excel

1. Type the list items into cells in a column of the current worksheet or another worksheet. Name the list if you enter it into a different worksheet. Select the list and click inside the 'Name' box at the top left corner of the worksheet next to the 'Formula Bar.' Type a name for the list and press 'Enter.'
2. Click on the cell in which you want to create a drop-down list. To enter the list into several cells, press and hold the 'Ctrl' key and click on each cell in which you want to use the list.
3. Go to the 'Data' menu in Excel 2003. Click 'Validation' and go to the 'Settings' tab of the 'Data Validation' dialog box. Go to the 'Data' tab in Excel 2007. Click 'Data Validation' and select 'Data Validation' from the menu. Go to the 'Settings' tab of the 'Data Validation' dialog box.
4. Select 'List' in the 'Allow' box. Enter an equal sign followed by the cell range containing your list into the 'Source' box if the list is on the same worksheet. Enter an equal sign followed by the range name into the 'Source' box if you created the list on another sheet. For example, if the range name is 'Fruits,' type '=Fruits' into the 'Source' box.
5. Clear the 'Ignore Blank' check box if you want to require users to select a list item rather than leave the cell blank. Go to the 'Input Message' tab if you want to display a message when the cell is selected. Type your message into the 'Input Message' box. Click 'OK' to create the drop-down list in the selected cell or cells.

Tuesday, March 27, 2012

How to Count All Rows With a Blank Cell in Excel 2007

1. Type '=COUNTBLANK(' in an empty cell.
2. Highlight the column of data you want to calculate the number of empty cells.
3. Press 'Enter.'

How to Create a Report in Microsoft Excel 2007

1. Complete the data and calculations in your worksheet. Even if your data and calculations span multiple worksheets, you can pull the data together into one report.
2. Roughly lay out how you want the report to look, including what you want in headers and footers, what text you want to be emphasized and what kind of graphical elements you want to include.
3. From the Insert tab on the Office Fluent Ribbon, click 'Header Footer' in the Text group to design the headers and footers, which are the text that displays on every page of the report. This opens the Header Footer Tools ribbon, where you can add page numbers, variables, date and time stamp, and pictures.
4. From the Insert tab, add visual interest to your report by inserting your own pictures, Microsoft clip art or SmartArt. You can also insert shapes, freehand drawings and text boxes. Using these tools, which are similar to the drawing tools in other Microsoft applications, you can do things such as add a stylized title to your report, include a pertinent graphic or illustration, or represent some aspect of your worksheet in a way other than a traditional graph.
5. From the Home tab, format the text and cells of your worksheet using features such as cell shading, cell borders, text alignment and fonts.
6. From the Page Layout tab, adjust printing aspects of your report, including the size of page margins, which rows or columns to repeat on subsequent pages and where page breaks occur. If you have more data on a given worksheet than you want to print on the report, use the Print Area command to explicitly identify the area of the worksheet to print.
7. From the Microsoft Office Button (the round, muticolored button in the upper-left corner of the Excel window), select Print and then Print Preview.
8. Iterate through steps 3 through 6, adjusting graphics, layout and formatting until your report is printing appropriately.

How to Calculate Sample Variance Using Excel

1. Add the numbers in the first column of your Excel 2007 document. For example, add the numbers 100, 200, 300, 400, 500 and 600 in A2, A3, A4, A5, A6 and A7.
2. For this example, click on the 'A9' cell. This is the cell where you will calculate the variance. When you calculate another variance, choose any cell at the bottom of the list of numbers you are using.
3. Click on the 'Formulas' tab while you are in the 'A9' cell. Click on 'Statistical' on the drop-down menu.
4. Click 'Insert Function' on the top left-hand side of the Excel spreadsheet. The 'Insert Function' window will pop open.
5. Click on the drop-down menu of 'Or select a category.'
6. Scroll down the 'Select a function' window. Choose 'VAR,' which is the function of variance based on the sample.
7. Click 'OK.' The 'Functions Arguments' window will pop open. Ensure that on 'Number 1' cell, A2:A7 is populated. If A2:A7 is not populated, enter A2:A7 manually. Click 'OK.'
8. The variance has been successfully calculated. In this example, the calculated value of the variance is 35,000.

How to Add a Subtotal to a Pivot Table

1. Select your items, row, or column that you would like to subtotal in your pivot table report.
2. Click 'Field Settings' in the 'Active Field' group on the 'Options' tab.
3. Click 'Automatic' under 'Subtotals' to subtotal the outer row or column label.
4. Select 'Custom' under 'Subtotals' to choose a function for an inner row or column label.

Monday, March 26, 2012

How to Adjust Chart Fonts in Excel

1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart in which you want to change the formatting of the font in that chart.
2. Right-click on the text in the chart that you want to change the font or other formatting feature. The 'Mini Toolbar' will appear over the shortcut menu that appears. The 'Mini Toolbar' contains everything you need to adjust the chart font.
3. Use the 'Font Type' drop-down list to choose a new font for the selected text. The 'Font Size' drop-down list allows you to change the size of the font that you have selected. The 'Increase Font' and 'Decrease Font' buttons allow you to quickly adjust the size.
4. Use the styles buttons to make the selected text bold or italic by choosing the 'B' button for bold or the 'I' button for italic.
5. Align the selected text by choosing the 'Right Align,' 'Center Align' or 'Left Align' buttons.
6. Change the color of the selected text by picking a color from the 'Text Color' drop-down box. Click 'More Colors' at the bottom of the drop-down box to display the Color Picker where you can create a custom color for the selected text.

How to Add a Counter in Microsoft Excel

1. Log on to your computer and open Microsoft Excel. Create a new spreadsheet.
2. Label the columns in your spreadsheet by giving them descriptive titles. For instance, you could create a 'Description' column, a 'Price' column, a 'Quantity' column and a 'Total Cost' column for a spreadsheet used to track orders.
3. Add a column and give it the title 'Count.' Go down to the bottom of the spreadsheet and type '=COUNT(firstrow:lastrow).' For instance, if the first cell in your spreadsheet that contains data is B2 and the last cell containing data is B50, your formula would read '=COUNT(B2:B50).'

How To Enable Excel Macros

1. Launch Microsoft Office Excel, click the 'File' tab and click 'Options' to open the Excel Options Window.
2. Click 'Trust Center' to open the Trust Center Window from the left pane of the Window.
3. Click 'Macro Settings' and choose the option that says 'Enable all macros (not recommended, potentially dangerous code can run).' Click 'OK' and click 'OK' again to close the Windows.
4. Exit Excel and restart it to enable all macros.

How to Select Multiple Non

1. Select the first range of cells by holding down the 'Ctrl' key, selecting the fist cell of interest with the left mouse button, and mousing over the range. When you have finished highlighting the range, release the mouse button but keep the 'Ctrl' key depressed.
2. Move the cursor to the next range of cells you would like to highlight.
3. Press the left mouse key to highlight the cell, then (keeping the left mouse button down) highlight the next area you would like to select. When finished highlighting, release the left mouse button.
4. Repeat Step 3 for as many ranges as you would like to select.

How to Convert XPS to Excel 2003

1. Download a preferred converter software. Websites such as Free Downloads Center or File Buzz have XPS-to-Excel 2003 converter software available for downloading. Simply click on the preferred software and select 'Run' when prompted. Install the software when the download is complete.
2. Open Excel 2003. Select 'File' and 'Open.' Browse through the files to find the XPS file. Select the file and click 'OK.' The software will convert the XPS file into a format that Excel 2003 can read.
3. Select 'File' and then 'Save As.' Create a preferred name and change the file extension of .xps to the Excel extension instead. Click 'Save.'
4. Close the file and reopen it to check that it saved properly. Close Excel 2003 when completed.

How to Un

1. Open the spreadsheet you wish to modify. Hold down the 'Ctrl' key, and tap the 'A' key twice. This should select all of the cells in the spreadsheet. Press 'Ctrl C' to copy the cells to the clipboard.
2. Click on the Microsoft Office logo at the top-left corner of the Excel screen and click 'New'. Click the cell labeled A1 to select it, then press 'Ctrl V' to copy the cells into the worksheet.
3. Press 'Ctrl S' to bring up the 'Save As' dialog box. Click the 'Tools' button, and select 'General Options'. Make sure the password box is empty. Delete the contents of the box if necessary. Click 'Ok' to close the options screen, then type in a new file name and click the 'Save' button.
4. Open the new spreadsheet file to confirm all of the data is present. Right-click on the old spreadsheet file and select 'Delete'.

Sunday, March 25, 2012

How to Create Macro Buttons in Excel 2007

1. Open an Excel workbook that contains a macro.
2. Click the arrow to the right of the Quick Access Toolbar to reveal a drop-down menu; from that menu choose 'More Commands....'
3. Click the left-hand drop-down menu in the resulting window. Choose 'Macros.'
4. Double-click a macro in the resulting list to move it into the 'Customize Quick Access Toolbar' list.
5. Click the 'Modify' button below the list of Quick Access Toolbar buttons.
6. Choose a button image in the resulting window, and type a new display name for the button, if you want. The button name appears in a tooltip when you hover the mouse cursor over the button.
7. Click 'OK' to close the Modify Button window, then 'OK' to close the Excel Options window and return to the main Excel window, where your macro button appears on the Quick Access Toolbar.

How to Make a Chart on Excel With Coordinates

1. Open a new Microsoft Excel 2010 spreadsheet.
2. Click on cell 'A1' and type in the header for the first half of your set of coordinates. This header will not appear on the spreadsheet, but may be of use for referencing purposes when you are entering your data. Click on cell 'B1' and type in the header for the second half of your set of coordinates. This header will wind up as the title for your chart, and will be displayed in the chart's legend as well.
3. Select cell 'A2' and enter the first part of your first set of coordinates. This part of your coordinates will be plotted on the horizontal axis. Select cell 'B2' and end the second part of that set of coordinates. This part of the coordinates will be plotted on the vertical axis. Continue to enter in sets of coordinates in the first two columns until you have added all your data to the spreadsheet.
4. Click on cell 'B1' and select the 'Insert' tab at the top of the screen. Click the 'Scatter' button, located in the Charts area of the ribbon. Choose any of the five different XY Scatter charts that appear in the pop-up menu. The different charts will all plot the same data, but will allow you to choose to add curvy lines, straight lines and data markers. Once you click on your desired chart type, the chart will appear on your spreadsheet.

How to Spell Check on Excel 2007

1. Launch Excel 2007 and open the file you want to spell-check.
2. Click the 'Review' tab at the top of the screen.
3. Click the 'Spelling' button in the 'Proofing' section on the ribbon at the top of the screen. The 'Spelling' button is represented by the letters 'abc.'
4. Click one of the options in the 'Spelling' window when Excel encounters a potential spelling mistake. You can choose 'Ignore' if the word is spelled correctly or you can select the correctly spelled word under 'Suggestions,' then click 'Change' to fix the misspelled word.
5. Click 'OK' after all spelling mistakes have been corrected.

Saturday, March 24, 2012

How to Add Gridlines to an Excel Chart

1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like to add gridlines.
2. Click the chart you want to add gridlines to so it is selected. You can tell the chart is selected because it will have a light blue border surrounding it.
3. Select the 'Layout' tab at the top of the Excel 2007 screen to display the settings in the 'Layout' ribbon. Find the 'Axes' group in the 'Layout' ribbon.
4. Choose the 'Gridlines' button in the 'Axes' group of the 'Layout' ribbon and point to 'Primary Horizontal Gridlines' to display the horizontal gridline options.
5. Use 'Major Gridlines' to display gridlines only at major units, 'Minor Gridlines' to display them at minor units or 'Major Minor Gridlines' to display the gridlines at both the major and minor unit markings.
6. Opt for 'Primary Vertical Gridlines' from the 'Gridlines' button to choose to display either 'Major Gridlines,' 'Minor Gridlines' or 'Major Minor Gridlines' at the vertical units in the chart.
7. Watch as the gridlines are displayed on the selected chart as you have indicated.

Subscribe to: Posts (Atom)