Excel

Browse » Home » All posts

Thursday, December 27, 2012

How to Define Names Labels in Microsoft Excel 2003

1. Open the Microsoft Excel 2003 program and then click the 'Tools' option from the top toolbar menu.
2. Click the 'Options' button and then click the 'Calculations' tab. Select the box next to the 'Accept labels in formulas' field, and then close out of the dialog box.
3. Use your mouse to select a range of cells in your worksheet that you want to define names for.
4. Click the 'Name' field from the left side of the formula bar. Enter the name you want for the group of cells.
5. Press the 'Enter' key on your keyboard, and the names will be added.

Wednesday, December 26, 2012

How to Toggle a Grid in Excel 2007

1. Press down the 'Alt' key on your keyboard.
2. Press the letter 'W,' then the letter 'V,' then the letter 'G.' Press them one at a time: don't try to press them all together at once. This keyboard action will make your gridlines disappear if you have them and will make them reappear if they are not showing.
3. Repeat steps 1 and 2 to toggle the gridlines between showing and not showing.

How to Create Excel Spreadsheets in HTML

1. Click on 'File' in Excel 2003 or the 'Office' button in Excel 2007.
2. Click on 'Save As Web Page' in Excel 2003. In Excel 2007, choose 'Other Formats,' then choose 'Web Page' from the 'Save as Type' box.
3. Choose a save location from the 'Save In' list. For example, click on your Desktop.
4. Type a name for your Web page in the in the 'File name' box.
5. Click on 'Publish' twice. In Excel 2003, select the item you want to publish in the 'Choose' box (Excel 2007 does not require this extra step). Excel will save your workbook as an HTML Web page.

How to Sort Information by Date in Excel 2003

1. Open Excel 2003 by double-clicking the icon. Click 'File,' then 'Open' and select the spreadsheet you want to work with.
2. Click on the top left cell of the data you want to sort. Hold down the mouse button, and drag the cursor to the bottom right box, highlighting all of the data.
3. Click the 'Data' button, then select 'Sort' from the drop-down menu that appears.
4. Click the small arrow by the 'Sort By,' and select the column that holds all of the dates. Choose 'Ascending' if you want older dates to appear first or 'Descending' if you want the newest dates to appear first. Click 'OK,' and Excel will sort the information provided by date.

Tuesday, December 25, 2012

How to Separate Cell Data in Excel

1. Start Microsoft Excel and open an existing workbook that contains cells with data you'd like to separate.
2. Select the cells containing data you want to separate by clicking and dragging or using the SHIFT or CTRL keys on your keyboard.
3. Click the 'Data' tab at the top of the screen to display the 'Data' ribbon.
4. Select the 'Text to Columns' button to display the 'Convert Text to Columns Wizard' dialog box.
5. Choose the 'Delimited' selection to separate the data by looking for commas or tabs or choose the 'Fixed width' selection to separate the data by looking for spaces. You will be able to see a preview of the selected data below these two choices. Click the 'Next' button to move to the next screen of the wizard.
6. Set the column breaks for the selected data by clicking at the point in the preview that you want to add a column. Double-click any column break lines to delete them, and move a break line by clicking and dragging it. Click the 'Next' button when you are finished setting the column breaks for your data.
7. Choose a column data format depending on the type of data you are separating, and then choose a destination where you want the new data to appear.
8. Click the 'Finish' button to close the 'Convert Text to Columns Wizard' and separate the selected cell data.

How to Resize Columns in a Microsoft Access Table

Resizing by Dragging
1. Open your database file in Microsoft Access.
2. Use the F11 key to open the Database window.
3. Click on the Table tab, then click Open. A listing of tables appears.
4. Select the table you want to modify, then click Open.
5. Click in the label or title of the column that you want to resize.
6. Move your cursor to the edge of the column. The cursor turns into a cross shape.
7. When the cursor is a cross, grab the edge of the column and drag it to the width you want.
Resizing From the Format Menu
8. With your table open, click in the label or title of the column that you want to resize.
9. Select Column Width from the Format menu in the toolbar.
10. Enter the exact width that you want the column to be. Alternatively, select Best Fit, which adjusts for the column's content.

How to Make a Histogram in Excel 2003

1. Open an Excel workbook. Click the Windows 'Start' button, click 'All programs,' click 'Microsoft Office' and then click 'Microsoft Office Excel 2003.' Alternatively, you can double-click the Excel shortcut icon on your computer's desktop.
2. Click 'Tools' in the menu bar to open a drop-down menu, then click the 'Add-ins' option. You must first add this feature to Excel before a histogram can be created.
3. Click the check box next to 'Analysis Toolpak' and click the 'OK' button in the Add-Ins window. This particular add-in will be loaded in to Excel.
4. Click 'Tools' in the menu bar a second time, then click 'Data Analysis.' This opens a new window. Click 'Histogram' in the list of options in the window and click 'OK.' This opens the Histogram window.
5. Enter cell names in the Input Range field. This is the data you want to analyze. You can enter these by dragging your mouse over the cells in your Excel worksheet. They will automatically load into the Histogram window.
6. Enter cells names in the Bin Range field as you did with the cells for the Input Range. These are the numbers you want to use as intervals in the histogram.
7. Select your output options. You have two main choices for what the output will look like: a table or a chart histogram. 'Pareto' will output your data in a table in descending order of frequency. 'Chart Output' is just that. The 'Cumulative Percentage' option can add this percentage to your table or chart. You can also have your histogram appear in a new workbook, and you can name it if desired. Click the check boxes of the output options you want, then click the 'OK' button. Your histogram will be generated.

How Do I Create a Histogram Using Continuous Data in Excel 2007?

1. Open the Microsoft Excel 2007 application on your computer. Enter the continuous data into the spreadsheet.
2. Make sure to create one column with an input range of continuous data and another column with a bin range of data, which must be entered in ascending order.
3. Click on the “Data” tab and then click on the “Data Analysis” option from the “Analysis” group. The Analysis Tools dialog box will then appear.
4. Click on the “Histogram” option and then click on the “OK” option. Click on the “Collapse Dialog” button in the “Input Range” section and select the input range of data that you want represented in your histogram.
5. Click on the “Collapse Dialog” button again to return to the Analysis Tools dialog box. Click on the “Collapse Dialog” button next to the “Bin Range” section and select the bin range data in the spreadsheet.
6. Click on the “Collapse Dialog” button. Click on the box next to the “Labels” field if you want labels included with the histogram.
7. Make any changes to the setting in the “Output options” section to meet your preferences. Click on the box next to the “Cumulative Percentage” field so that it’s deselected.
8. Click on the “OK” button to save the changes. The histogram with the continuous data will then appear on the spreadsheet.

Monday, December 24, 2012

How to Remove Trailing Commas in Excel

1. Open the worksheet that contains the data from which you want to remove trailing commas.
2. Right-click the header of the column directly to the right of the data column that you want to clean. Click 'Insert' in the menu to insert a new function column.
3. Type the following in the cell in the formula column adjacent to the first data cell:=IF(RIGHT(A1,1)=',',LEFT(A1,LEN(A1)-1),A1)Substitute the cell address of your first data cell in place of all instances of 'A1' in the above example.
4. Press 'Enter.' Excel first determines whether the rightmost value in the data cell is a comma. If so, it determines the number of characters in the cell using the 'Len' function and then returns only the leftmost N minus 1 characters, thus omitting the comma. If no comma is detected at the end of the string, then Excel returns the original cell value.
5. Right-click the formula cell and click 'Copy.' Paste the formula into the cell directly to the right of all cells from which you want to clean the commas. Excel will perform the comma-trimming function on all cells and return the update value in the formula column.
6. Highlight all formula cells, then right-click the array and choose 'Copy.'
7. Highlight the original data cells, then right-click the array and choose 'Paste Special.' Click the radio button next to 'Values,' then click the 'OK' button. Excel will copy the output strings from the comma-less formula cells into your original data cells as static character strings.
8. Highlight the formula column, then right-click the array and click 'Delete' from the menu. This will delete the formula column now that a permanent copy of the formula output has been saved in the original data column.

How to Change the Size of a Microsoft Office Drop

1. Open Microsoft Word by double-clicking the Word shortcut icon on your desktop or by clicking 'Start', pointing to 'Programs' or 'All Programs' and then finding Word in your programs list.
2. Click 'Tools' on the toolbar and then click 'Customize'. The 'Customize' dialog box will open.
3. Click on the 'Toolbars' tab. Make sure the name of the toolbar that contains the drop-down list you want to make wider or thinner has a checkmark beside it.
4. Leave the 'Customize' dialog box open and click on the drop-down box that you want to resize. You will notice that it will now have a thick black line around it.
5. Move your mouse to one of the outer edges of this thick black line. When you see a line with two arrows pointing right and left, click the box with your mouse and drag it to the size you want it, whether it is larger or smaller. When you are finished, close the 'Customize' box.

How to Convert Excel 2007 to 2003

1. Open Excel 2007 if your computer has it. Click on the Windows icon at the top left-hand side of the screen and scroll to 'Open.' Click the command and browse your computer for the Excel 2007 file (it will have a file extension of '.xlsx'). Click on the file to select it, then click 'Open.'
2. Let the file open. Click on the Windows Icon and scroll to 'Save As.' Click on 'Excel 97-2003 Workbook.'
3. Name the file (the program will automatically name the file the same name as the 2007 version) in the space provided. Browse through your computer folders to find where you want to save it. Click 'Save.' The file can now be opened with earlier versions of Excel.
4. Download the Microsoft Compatibility Pack if you don't have Excel 2007 (www.Microsoft.com/downloads/details.aspx?familyid=941b3470-3ae9-4aee-8f43-c6bb74cd1466). Click 'Download' once the page opens.
5. Click on the 'Run' button when the download screen appears. Click to accept the EULA and click 'Continue' to start the install. Click 'OK' once the pack has been installed. You should now be able to open Excel 2007 docs with Excel 2003 and earlier.

How to Place a Picture in a Cell in Microsoft Excel 2007

1. Select the cell of the spreadsheet into which you want to insert a picture.
2. Open the 'Insert' tab. This tab is located in between the 'Home' and 'Page Layout' tabs at the top left of the screen in the Ribbon, Office 2007's menu system.
3. Press the 'Picture' button, located in the Illustrations section (second from the left).
4. Navigate to the location of the picture you intend to insert and double-click the file to insert the picture into the selected cell of your Microsoft Excel document.

Sunday, December 23, 2012

How to Create a Scrolling Section in an Excel Spreadsheet

1. Split the screen. To split the screen horizontally, place the cursor over the dash above the scroll bar at the far right of the spreadsheet just above the up arrow. The cursor becomes two lines with up and down arrows when placed over the dash. Left click on the dash and drag down under the last row you wish to freeze. The spreadsheet has been split into two sections. Each section can be scrolled independently. To split the screen vertically, place the cursor over the dash at the far right of the bottom scroll bar just after the right arrow. Click and drag to the end of the last column you wish to freeze. The spreadsheet can be split into four sections.
2. Freeze the panes. On the top menu, select 'View.' In the 'Window' section, under the 'View' menu, choose 'Freeze Panes.' Choose the first option, 'Freeze Panes.'
3. Scroll through the spreadsheet. When scrolling, check to see that the column headers and row descriptions you need are frozen. If you need to re-adjust the splits, return to 'Freeze Panes' under the 'Window' sub-menu, below 'View,' and choose 'Unfreeze Panes,' and try again.

How to Use the Drop

1. Open Excel 2007 and select a workbook. Press 'Control' and 'O.' Browse your computer for the Excel workbook. Click the workbook and click 'Open.' The workbook opens.
2. Select a cell or group of cells to apply the data validation. Select the 'Data' tab and click 'Data Validation.' Select 'Data Validation' from the drop-down list. On the 'Settings' tab, select 'List' from the 'Allow' drop down criteria. Type 'Yes,No,Maybe' in the 'Source' field.
3. Click the 'Input Message' tab. Check the option for 'Show Input Message When The Cell Is Selected.' Type the title of your input message in the 'Title Field.' Type a custom message for the input message in the 'Input Message' field.
4. Click the 'Error Alert' tab. Check the option for 'Show Error Alert After Invalid Data Is Entered.' Type the title of your error alert in the 'Title Field.' Type a custom message for the error message in the 'Error Alert' field. Click 'OK.'
5. Click a cell and notice the appearance of the drop-down list. Data validation has added drop-down boxes in Excel.

How to Protect One Cell in an Excel Spreadsheet

1. Open the Excel spreadsheet that contains that data and cells that you want to separately protect. Click on the individual cell that you want to lock.
2. Click on the “Format” option from the top tool bar menu. Click on the “Cells” option and then click on the “Protections” tab.
3. Click on the box next to the “Locked” field. Click on the “OK” button and you’ll be returned to the spreadsheet.
4. Click on the “Tools” option from top tool bar menu. Scroll over the “Protection” option and then click on the “Protect Sheet” option.
5. Type a password that will protect the cell from being unlocked by anyone else and then click on the “OK” button. The single cell in your spreadsheet will now be protected.

Subscribe to: Posts (Atom)