Friday, December 28, 2012

How to Make a Monthly Budget on Excel


1. Click 'Start' > 'All Programs' > 'Microsoft Office' > 'Microsoft Office Excel.'
2. Click cell 'A1,' type 'Expenses,' click cell 'B1' type 'Amount,' click cell 'D1,' type 'Income' and then click cell 'E1' and type 'Amount Earned.'
3. Click cell 'A2,' type the name of a monthly expense (rent, car payment, food etc.) press the 'Enter' key, type the name of another expense and then continue in this manner until all expenses you plan to incur are listed in column A. Repeat this step, clicking on cell D2 and entering sources of income on each line instead of expenses (income from each job, tips, interest, gifts, etc.)
4. Click cell 'B2,' type the amount of money you plan to spend on the expense in cell A2, press the 'Enter' key, and then continue typing in the anticipated amount you will spend on each expense. Repeat this step, clicking on cell E2 and entering amounts corresponding with each source of income. You will now have lists of all of your planned expenses and sources of income.
5. Click cell B2, hold down the mouse button and then drag the mouse down to select the values of all planned expenses.
6. Click the 'Auto sum' button under the 'Editing' group on the ribbon (the main toolbar at the top of the Excel interface.). A total of all planned expenses will appear underneath column B.
7. Repeat steps 5 and 6, selecting cell E2 instead of B2. The total of all planned income sources will appear under column E2.
8. Click on an empty cell underneath the lists press '=' click on the cell containing the total of all income, press '-' and then click on the cell containing the total of all expenses and press 'Enter.' The cell will display the difference between planned income and expenses (the amount of money you will have left over after you pay all your planned expenses.).
9. Hold the 'Control' key, press 'S,' enter a name for the budget in the space provided and then click 'Save.'
Read more ►

How to Hide the Excel Pivot Table Data Area


1. Move the data you want to hide to the right. The easiest way to hide a large data field is to hide everything that lies on the right side of a selected column. Therefore, start by moving all the data you want to hide to the right. Click on 'Pivot Table Wizard' on the Pivot Table Toolbar. Now click on 'Layout.' Drag and drop the columns to arrange the data in such a way that all the data you wish to hide is to the right of the column(s) that will be visible and click 'OK.'
2. Click on the first column from the left. Now go to the Pivot Table Toolbar and click on the icon that looks like a list with a green plus sign on the side (fifth icon from the left). A new window, titled 'Show Detail,' will appear. Here, select only the fields for which you wish the data to show. If you select no fields at all, only the data in the very first column will show. You can also hide the data in the fist column if you so desire.
3. Click on the first column. Click the icon from the Pivot Table Toolbar that looks like a list with a red bar on the side (fourth icon from the left). The data in the column will now disappear. If you press the same button again, the data will reappear. This way, you can hide all the data in the data area of the Pivot Table. If you wish to keep all the column headings and hide the data beneath the headings, simply click on the fourth icon (which hides data in one column) once for each column.
Read more ►

Thursday, December 27, 2012

How to Add a Custom Menu to an Excel Toolbar


Adding a Custom Menu to a Toolbar
1. Open Microsoft Excel.
2. Display the toolbar that contains the menu where you want to add your command.
3. Open the Tools menu and select Customize.
4. Select the Commands tab.
5. In the Categories box, click New Menu.
6. Drag New Menu from the Commands box to the desired location on toolbar.
7. Right-click the new menu and select Name.
8. Type a name for the menu in the Name box. Press Enter.
9. Now add commands to the new menu, following the steps in the next section.
Adding a Command to a Menu
10. Open the Tools menu and select Customize.
11. Select the Commands tab.
12. Click your new menu on the toolbar. A box will appear below it.
13. In the Categories box, select the category for the command.
14. Drag the command you want from the Commands box to the new menu's box on the toolbar. When the menu displays a list of menu commands, point to the location where you want the command to appear on the menu on the toolbar, and then release the mouse button.
Read more ►

How to Convert Lotus 123 Files to Excel


From Lotus 123
1. Open your file in Lotus 123.
2. Go to the 'File' menu, select 'Save As,' and save it as a Microsoft 97 file (*.xls).
3. Open the resulting .xls file in Excel.
From Excel
4. Open Excel.
5. Locate the Lotus 123 file (with an extension of *.wk4 in most cases).
6. Click 'Open.' Excel will prompt you for approval before converting the file.
7. Go to the 'File' menu, and select 'Save As' to save a copy of the file as an Excel native format.
Read more ►

How to Create Diagonal Lines in a Cell in an Excel Spreadsheet


1. Open Microsoft Excel by double-clicking the Excel icon on your screen or selecting 'Programs' from the 'Start' menu and then selecting 'Microsoft Excel.'
2. Select the cell you would like to place the diagonal line in by clicking on it once. The cell will be highlighted with a bold border around it to indicate that it has been selected.
3. For Excel versions before 2007, move your cursor to the 'Format' pull-down menu and select the first item: 'Cells.' A pop-up box will appear named 'Format Cells.' Select the tab that says 'Border' within the box.
4. For Excel 2007 or more recent versions, within the 'Home' tab at the top of the page, click on the down-facing arrow next to the image of a box within the 'Font' section. This is called 'Format Cells.' This arrow will select a pull-down menu. Select the last entry: 'More Borders.' The 'Format Cells' pop-up box will appear within the 'Borders' tab.
5. Select either the left or right diagonal line amongst the line options surrounding the sample box. Your selection will show up as an example in this box.
6. Confirm your selection by pushing 'OK.' The box will close and you will to your spreadsheet with the diagonal line appearing in the cell.
Read more ►

How to Make a Sales Order Form in Excel 2007


1. Open a new workbook in Microsoft Excel 2007. Click on the 'Office' button on the top left hand corner and select 'New.'
2. Select the Microsoft Office Online under 'Templates' and type sales order in the search box. Hit 'Enter.'
3. Select a template such as 'Sales order (Simple Blue design).' Click on the 'Download' button.
4. Fill in information on the company name, slogan, date, invoice, customer ID, return address, and shipping address. In order to add the company logo, go to cell A1 and click on the 'Insert' tab in the toolbar. Click on 'Picture' and find the picture on your computer and click on the 'Insert' button.
5. Enter the following information pertaining to the order: salesperson's name, job, shipping method, shipping terms, delivery date, payment terms, due date, quantity, item number, description, unit price, discount, and sales tax. The line total cell is automatically calculated by multiplying the quantity by the unit price, and subtracting any discounts. The total discount cell, subtotal, and total cells are also automatically calculated using formulas.
Read more ►

How to Insert Bullet Points Into Excel 2007


1. Select the cell or cells in which you want to insert bullet points. The cells can be empty, or they can have text already entered.
2. Click the 'Format' button, which is found in the 'Cells' group under the 'Home' tab. Scroll to the bottom of the list that appears, and under 'Protection,' choose 'Format Cells.'
3. Click 'Custom' under the 'Number' tab. If the selected cells are empty, the '@' symbol will appear in the 'Type' box. If there is already text in the cells, there will be text in the 'Type' box. Scroll down in the 'Type' menu until you see the '@' symbol, and select it; it will replace whatever text is already in the box.
4. Place the cursor before the '@' symbol in the 'Type' box. Hold down the 'Alt' key, and type 0149 on the number pad. Release the 'Alt' key; a bullet will appear before the @ symbol. Insert a space between the bullet and the @ symbol if you want a space between the bullet and the text in the spreadsheet. Click 'OK' to exit the 'Format Cells' menu.
5. Enter text into the selected cells. A bullet will appear in front of any text entered. If there was already text in the cells, the bullet point will appear before it.
Read more ►

How to Use Excel Spreadsheet for Bills


1. Select cell 'A1,' located in the top-left corner of the Excel 2010 worksheet.
2. Type 'Bill Type' into the the cell. This column will contain the name of the bill, like 'Rent' or 'Electricity.' Select the cell to the right of the first one and enter in 'Bill Amount.' Continue moving one cell to the right and enter in 'Bill Month,' 'Due Date,' 'Amount Paid' and 'Date Paid.' You can also add more fields if there is some other aspect of each bill that you want to track.
3. Select cell A2. Enter in the bill type for the first bill you want to track. Then move to the cell directly to the right, and enter in the bill amount. Continue until you have entered all the available information for that bill. Leave the 'Amount Paid' and 'Date Paid' fields blank until you have paid the bill.
4. Enter another bill's information into row 3. Continue until you have all your bill information for the month entered in.
5. Select cell A1 and hold the mouse button down. Move the mouse to the rightmost cell in the last row of the information that you entered and release the button. Select the 'Insert' tab at the top of the window and click the 'Table' button. Select 'OK' to build the table. This will format your information to make it readable and provide drop-down arrows that will let you narrow down your information.
6. Right-click anywhere in the table, move your mouse over 'Table' in the pop-up menu and choose 'Totals Row' to add a summation row at the bottom of the table. Select the cell in the totals row under 'Bill Amount' and choose 'Sum' from the menu that appears. This will now give you the total amount for all the bills you entered into Excel. You can do the same thing for the 'Amount Paid' column, or any other column that contains numerical data.
7. Right-click the row number for the totals row and select 'Insert' if you need to add another bill to the field. This will create a blank row at the bottom of the table. Generally you will have to do this several times each month to add that months bills.
8. Select a drop-down arrow next to each header to narrow down the table to just include rows that contain certain entries. You can look at just bills of a certain type, or just bills from a certain month. The totals row will automatically update when you narrow down the table.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

How to Remove a Check Box in Excel


1. Open the Excel file that contains the check box you want to remove.
2. Click the 'Design Mode' option on the 'Controls' group of the 'Developer' tab.
3. Click once on the check box that you want to delete. Press the 'Delete' key on your keyboard. Repeat this step for each check box that you want to remove.
Read more ►

How to Use Excel's TAN Function


1. Enter your input values into a row or column in a blank spreadsheet. The 'input value' refers to the angle, represented by the 'x.' Let's assume you enter an input value of 5 and you enter it in cell A1.
2. Click your cursor in a different cell and type the formula '=Tan(A1).' This gives you the result of -0.14255. This number represents the tangent of your angle.
3. Change the number of decimal points in your answer if necessary. Go to 'Format' and 'Cells.' Select the 'Number' tab, then 'Number' and enter the number of decimal points you would like.
4. Compare your answers with the most popular tan values: 'Tan(0 degrees) = 0.0000;' 'Tan(30 degrees) = 0.5773;' 'Tan(45 degrees) =1.000;' 'Tan(60 degrees) = 1.7320;' 'Tan(90 degrees) = infinity.'
Read more ►

How to Set Row Height in Excel 2007


1. Open the Excel spreadsheet. Select the rows for which you want to set the row heights. Click on a row in the left column to select the entire row. Press and hold 'Ctrl' and click more rows to select multiple rows, or click the diagonal arrow in the top-left corner of the spreadsheet, above the first row, to select every row.
2. Click the 'Home' tab from the Ribbon if it's not already selected. Click on 'Format' from the Cells section and select 'Row Height.' This opens a dialog box.
3. Type a number into the text box and click 'OK.' This sets the row height for your selected cells to the number you entered.
Read more ►

How to Use Autofill With Numbers in Excel 2007


Autofill the Same Number
1. Type the number in the first cell that you would like to contain that number.
2. Click on the cell to highlight it.
3. Click the small square in the lower-right corner of the cell and hold the mouse button down.
4. Drag the square in whichever direction you would like to AutoFill. Release the mouse button when all desired cells are highlighted. The value is copied into the cells.
Autofill a Sequence
5. Type the first two numbers of your sequence in the first two cells in your list. For instance, to start an integer sequence in the 'A' column you would type '1' in cell A1 and type '2' in cell A2.
6. Highlight the cells that contain the first two numbers in your sequence.
7. Click the small square in the lower-right corner of your selection and hold the mouse button down.
8. Drag the square in the direction that you would like to autofill. Release the mouse button when all desired cells are highlighted. Sequential values are copied into the highlighted cells.
Read more ►

How to Enable Extra Rows in Excel 2007


1. Select the row above which you want to place the new row. You can do this by left- or right-clicking the row number on the left-hand side. For example, if you want to insert a single row between rows three and four, select row four. If you want to insert three rows between rows three and four, select rows four, five and six by clicking on row four, holding down the left mouse button and dragging the cursor down over rows five and six to highlight them as well. To select rows that are not adjacent to each other hold down the 'Ctrl' key and click on the rows you wish to highlight.
2. Click the 'Insert' option at the top of the screen. Navigate down to 'Rows' and click the left mouse button. If done correctly, the new row or rows will appear above the row you've selected. In the case of multiple or non-adjacent rows, they will all appear at once. Alternately, you can right-click the row below where you want to insert a new one. Choose 'Insert' from the box that pops up and select 'Rows.' This will perform the same function.
3. Hold 'Ctrl' and press 'Y' on your keyboard to repeat the row insert. The insertion operation that is repeated will be the same one you just performed. This means if you inserted three rows, then you hold down 'Ctrl' and press 'Y,' three new rows will be inserted.
Read more ►

Saturday, December 22, 2012

How to Use Spin Buttons in Excel


1. Use Excel to create your chart or document. Note: Be sure to save your work every few minutes.
2. Open the 'Developer' tab, select 'Controls,' and then 'Form.'
3. Select the 'Insert' option on that tab. You will see this with a little folderlike icon and some tools.
4. Click on your Excel document where you want the spin button to appear. The place where you click is where the upper-left hand corner of the button will appear.
5. Select the 'Properties' option from the 'Developer' tab and 'Controls.'
6. Start setting your options for the spin button. Let's say that you decided on the age of a person. Select a standard age, such as 25. Then select a starting (minimum) age, such as 21. Next, select an ending (maximum) age, such as 100. Then select the increments in which the numbers go up, such as one year at a time, five years, etc. Finally, fill in the spot that asks for cell information.
7. Click to save your changes and then once again click to save your Excel document.
Read more ►

How to Split a Merged Cell


1. Right click on the merged cell. Click 'Format Cells.'
2. Click on the 'Alignment' tab.
3. Uncheck the 'Merge Cells' check box by clicking on it.
4. Click 'OK'. The merged cells are now split.
5. Click on the icon for merged cells on the menu bar if available to enable or disable merged cells when needing a quick shortcut.
Read more ►

How to Password Protect an Excel Worksheet Using a Macro


1. Open the Microsoft Excel spreadsheet you wish to protect.
2. Press 'ALT F11' to open Visual Basic.
3. Click on the 'Insert' menu and select 'UserForm.'
4. Click on the 'TextBox' tool and drag it onto the 'UserForm' work area. Adjust the size of the text box as desired.
5. Press 'F4' with the 'TextBox' still selected to enter the 'Properties' pane and scroll down to 'PasswordChar.' Insert an asterisk (*) in the value column.
6. Click on the 'UserForm' to bring the toolbox back and drag a 'CommandButton' onto the 'UserForm.' For aesthetic purposes, position the button to the right of the 'TextBox.'
7. Press 'F4' with the 'CommandButton' still selected and change the caption to 'OK.'
8. Click on the 'UserForm,' press 'F4' and change its caption to 'Protect/Unprotect All Sheets.'
9. Press 'F7' and paste the following code as it appears below:Private Sub CommandButton1_Click()Dim WSheet As WorksheetFor Each WSheet In WorksheetsIf WSheet.ProtectContents = True ThenWSheet.Unprotect Password:=TextBox1.TextElseWSheet.Protect Password:=TextBox1.TextEnd IfNext WSheetUnload meEnd Sub
10. Select 'Module' from the 'Insert' menu and paste the following:Sub ShowPass()UserForm1.ShowEnd Sub
11. Press 'ALT Q' to return to Excel.
12. Press 'ALT F8,' then press 'Options' after selecting 'ShowPass' from the list.
13. Assign a shortcut key to the macro and press 'OK.'
14. Press the shortcut key combination to launch the macro, enter a password, then press the 'OK' button to password-protect the sheets in the workbook.
Read more ►

How Do I Repeat Headers on Every Page In Excel?


1. Open the Excel spreadsheet that you want to print with repeating headings.
2. Click the 'Page Layout' tab on the ribbon.
3. Click the 'Print Titles' button in the Page Setup group. This will open a window with several tabs. Select the 'Sheet' tab if it is not already open.
4. Click the little blue icon with a red arrow at the far right of the box called 'Rows to repeat at the top.' This will bring up a narrow window with a blank line. Click the row in your spreadsheet that you want repeated. Usually this is the first row with column headings. To select several rows, click and drag down until you see all the rows you want, highlighted. This will insert the row numbers into the narrow box.
5. Click the little icon on the far right of the box to insert the row numbers into the printing window.
6. Click 'Print Preview' to see how the headings will look on multiple pages. Click 'Print' if you are satisfied.
Read more ►

How to Change Cell Font Alignment in Microsoft Excel 2003


1. Select all of the cells. To change the alignment in all of the cells, make sure that you have your spreadsheet file open, then use the hotkeys “CTRL-A” to select all of the cells in the spreadsheet.
2. Open the cell properties box. Once you have selected all of the cells, right-click on the highlighted cells; a cell submenu will open. Select “Format Cells” and a cell properties box will open.
3. Select the desired font alignment. Scroll to the “Alignment” tab in the cell properties box to change the alignment. You can set the vertical and horizontal attributes as well as specifying whether the text will wrap, shrink to fit, or merge inside of the formatted cells. Make the desired alignment selections and then click “Okay” to implement the changes.
4. Make sure to save the updated file. After you have made the desired changes, save your file by scrolling to the “File” tab on the command bar and left-clicking on “Save.”
Read more ►

How to Format Worksheet Groups in Excel


1. Start Microsoft Excel 2007 and open an existing workbook that contains worksheets that are grouped together to form a group.
2. Look at the sheet tabs in the lower left corner of the Excel screen. The sheet tabs that are white represent the worksheets that are part of the group.
3. Click on a white sheet tab so you bring up one of the worksheets that are in the worksheet group. It does not matter which one you choose, the formatting will be applied to all the worksheets in the group.
4. Select the cells in the worksheet that you want to apply formatting to in the selected cell on this worksheet and the other worksheets in the group.
5. Apply the formatting to the cells such as a border, shading, formula, function or column width.
6. Highlight and click on another white sheet tab to bring up another worksheet in the same group. Notice that the same cells in that worksheet have also had the same formatting applied.
7. Ungroup the worksheet group once you have finished formatting all the cells you want to format in the worksheets.
Read more ►

How to Sort Microsoft Excel Rows Alphabetically


1. Select the data you would like to sort alphabetically. Maybe it's just a small selection in a large worksheet, so simply select only the cells and any corresponding data; everything else remains the same.
2. Click on 'Data' from the main toolbar at the top of the worksheet and in the sub-menu that drops down, click on 'Sort.' The Excel program automatically selects the entire set of data and opens a new window.
3. Check to see that the data you select matches what you want to sort. If the program indicates that there isn't enough data or the data selection is too limited in some way, a 'Sort Warning' box appears. You can change the data selected before you continue.
4. Look at the choices in the 'Sort' sub-window and let the program know if your data has column headers or not. If you select 'Header Row' option, Excel will not sort that row of data. If you select 'No Header Row,' then all rows of data, including any header rows like Name, Address, City, State and Zip, will sort alphabetically in the process.
5. Decide how you want the data sorted. You can choose to only have one column sorted and all corresponding data will match up with it or have subsequent columns sorted after. For example, you can sort by last name from A to Z (ascending order) then sort the data alphabetically by first name, then again have the list alphabetically sorted by city.
Read more ►

How to Create a Normal Distribution Graph in Excel


1. Enter -4 in cell A1. Enter -3.75 in cell A2. Highlight both cells and grab the fill handle (the tiny box in the bottom right hand corner) with your mouse. Drag the fill handle to cell A33 and release the mouse.
2. Enter =NORMDIST(a1,0,1,0) into cell B1. This tells Excel to calculate the standard normal distribution from the value you entered in cell A1 with a mean of 0 and a standard deviation of 1. Press enter.
3. Using the same motion you used in Step 1, drag the fill handle from the corner of cell B1 down to cell B33.
4. Highlight cells A1 through A33 by holding the the left mouse button down and dragging the cursor.
5. Select 'Insert' from the toolbar, then 'Scatter,' and 'Smooth Line Chart.'
6. From Chart Tools at the right hand side of the toolbar, select 'Layout,' 'Axes,' 'Primary Vertical Axis,' then 'None'. This step will make the y-axis disappear.
7. Select 'Axes' from the center toolbar, then 'Primary Horizontal Axis'. Select the bottom option ('More Options'). Change the minimum x-value to -4 and the maximum x-value to 4 by pressing the appropriate radio button and filling in the values.
Read more ►

Friday, December 21, 2012

How to Create a Drop


1. Open Excel 2007 and click a blank cell in the workbook. Select the 'Data' tab and select 'Data Validation.' A drop-down list appears. Click 'Data Validation.' On the 'Setting' tab, change the Allow field to 'List.' In the source field, type 'yes,no,maybe.'
2. Click the 'Input Message' tab. Click 'Show input message when the cell is selected.' Add a title for your input message in the 'Title' field. Add a custom input message in the 'Input Message' notes field.
3. Click the 'Error Message' tab. Click 'Show error message when invalid data is entered.' Add a title for your error message in the 'Title' field. Add a custom input message in the 'Error Message' notes field. Select a style for your error message in the 'Style' drop-down list. Click 'OK.'
4. Click the cell where you started the data validation process. Notice the drop-down list that appears. You also will see the input message.
Read more ►

How to Calculate Correlation Coefficient Between Two Data Sets


1. Open Excel 2007 and sum in one column the numbers for the first set of data. For example, you would add the numbers 10, 20, 30, 40, 50 and 60 in the A2, A3, A4, A5, A6 and A7 cells of your Excel worksheet. In a second column, sum the numbers for the second set of data. For example, you would add the numbers 5, 2, 6, 6, 7 and 4 in the B2, B3, B4, B5, B6 and B7 cells of your Excel worksheet. Your goal is to find the correlation coefficient for these two sets of data.
2. Click on the 'A9' cell. This is the cell where you will calculate the correlation coefficient.
3. Click on the 'Formulas' tab and choose 'Insert Function' (this is found on the top left hand side of Excel spreadsheet). The 'Insert Function' window will open. Click on the drop-down menu of 'Or select a category' and choose 'Statistical.' Scroll down the 'Select a function' window. Choose 'CORREL.'
4. Click 'OK.' The 'Function Arguments' window will open, and you will see two cells: 'Array1' and 'Array2.' For Array1, enter A2:A7 for first set of data and for Array2, enter B2:B7 for the second set of data. Click 'OK.'
5. Read your result. In this example, the calculated value of the correlation coefficient is 0.298807.
Read more ►

How to Freeze a Row in Microsoft Excel


1. Open the Excel worksheet.
2. Click the top row heading. The row heading displays a number just left of the first column of cells. The selected row appears shaded.
3. Click the 'View' tab on the command ribbon.
4. Click the 'Freeze Panes' button in the 'Window' group. A list of options appears.
5. Click the 'Freeze Top Row' option. A black horizontal line appears on the worksheet. This line indicates the locked row that stays on the screen as you scroll down the worksheet.
Read more ►

How to Add a Title to an Excel Chart


1. Start Microsoft Excel 2007 and open a spreadsheet that contains a chart to which you would like to add a title.
2. Click in the white area of the chart to select the entire chart. There should be a light blue outline surrounding the chart indicating that you have selected the entire chart.
3. Select the 'Layout' tab at the top of the Excel screen to display the layout options for the selected chart.
4. Click the 'Chart Title' button in the 'Labels' section of the 'Layout' ribbon. A drop-down menu will appear that will display the different locations that you can add a title for the selected chart.
5. Choose 'Centered Overlay Title' to add a centered title that lies on top of the existing chart so the chart does not have to be resized. Choose 'Above Chart' to add a centered title that goes above the chart and resizes the rest of the chart so it can fit.
6. Click the chart title you have just added to the chart and move the chart by clicking and dragging it to its new location. You must click on the outline of the chart with a 4-headed arrow before you can successfully move the entire title.
Read more ►

How to Use a Filter in Excel


1. Isolate column headings to one cell. If a column heading spills over into another cell, use text wrapping to place the heading in one cell. Highlight a multi-cell column header, press 'Format' on the menu bar, click 'Row', select 'Autofit and then Format', select 'Cells' and 'Wrap Text' to place headers in one cell.
2. Format the row that contains the column heading differently than the rows that contain data, so Excel recognizes it is a row heading. Embolden characters, change the font color or place a border around the column heading to differentiate from data cells.
3. Ensure each column contains one type of data. For example, a spreadsheet with student data should have a column for test grades, one column for averages, one column for student names and so on.
4. Click any cell inside the spreadsheet you want to filter. If you select an entire column as opposed to a single cell, Excel will present the option to filter that particular column, not all of the columns in the data set.
5. Press 'Data' on the menu bar, scroll down to 'Filter' on the drop-down list and click 'Auto-Filter.' The 'Auto-Filter' drop-down arrows will appear to the right of every column heading of the single column you selected for filtering.
6. Click the drop-down arrow near the column heading for a column of data to display the filter options for the particular column. Observe that Excel displays only the data that applies to your filter selection. Excel hides any rows that do not contain the selected filter.
7. Filter the top or bottom numerical records in a column of data with Excel's 'Top 10' Filter. Click on a data cell in the column and click 'Top 10' at the top of the Auto-Filter drop-down menu in Excel 2003. For Excel 2007, click 'Numbered Filters' and choose 'Top 10.' Select 'Top' or 'Bottom' in the Top 10 Auto-Filter dialog box, choose a number of records from 1 to 500, select 'Items' or 'Percent,' then click 'Apply.'
8. Set custom filters to show records that meet two criteria instead of one. Click the Auto-Filter drop-down menu of the column heading you want to set a filter for and press 'Custom.' Enter two filtering conditions for the column of data. For example, you can see which students scored from 90 to 100 and 60 to 70. Check either the 'And' or 'Or' button or else the results will not display. Click 'OK' to set the custom filter.
9. Click 'Data' on the menu bar, point to Filter and press 'Show All' to turn off the filter and display the hidden data.
Read more ►

Thursday, December 20, 2012

How to Put Roman Numerals in Microsoft Office 2003


1. Open Microsoft Office Excel 2003.
2. In the Excel spreadsheet, click on a cell where you wish to put a Roman numeral.
3. Type '=Roman(58)' and press 'Enter.' The Roman numeral 'LVIII' that represents '58' will appear in the cell. Note that you can enter any number from 1 to 3,999 in parentheses.
4. Repeat Step 3 for other cells and/or numbers.
5. Click on a cell with the Roman numeral created in steps 3 or 4, and press 'Ctrl-C' on the keyboard to copy it.
6. Launch Microsoft Office Word 2003. Click the menu 'File' and select 'New' to create a new document, or 'File' and 'Open' to open an existing one.
7. Place the mouse pointer where you wish to insert the Roman numeral and press 'Ctrl-V' on the keyboard.
Read more ►

How to Use a Saved Template for Pivot Charts


1. Click 'Start' and 'All Programs.'
2. Navigate to the 'Microsoft Office' folder, click it once to display the folder contents and then click 'Microsoft Excel' to launch the program.
3. Click the 'Office' button in the upper left corner, and then select 'New' to create a new document. Or click 'Open' to locate and open an existing Excel spreadsheet.
4. Click and hold your mouse on the uppermost cell containing your target data, and then drag the mouse until all of your target data has been selected.
5. Click the 'Insert' tab at the top of the screen. Then click on the 'PivotTable' icon, and select the 'PivotChart' option.
6. Select whether you would like the PivotChart to be inserted into the existing worksheet or into a new worksheet. Click 'OK.'
7. Locate the 'PivotChart Tools' section at the top of the screen, and click the 'Design' tab in this section.
8. Click the 'Change Chart Type' icon at the top of the screen.
9. Select the 'Templates' option in the left column, select your saved template and click the 'OK' button.
Read more ►

How to Create a Calendar in a Pull


Create a Calendar Using Excel 2007
1. Enable the 'Developer' tab. The Excel 'Developer' tab is inactive by default, so you may need to activate it to create a pull-down menu. To activate the 'Developer' tab, click the 'Office' button in the top-left corner of Excel. Locate and click the 'Excel Options' button along the bottom right of the menu. Select and click the 'Show Developer in Ribbon' check-box from the pop-up menu, then click 'OK' to exit.
2. Insert a pull-down calendar. Click the 'Developer' tab from the Excel main menu ribbon. Click 'Insert' to display a menu of options and then click the 'More Controls' icon from the ActiveX controls section. The 'More Controls' icon is the last icon in the second row. It appears as a hammer and wrench crisscrossing in an 'X' pattern. A long list of additional controls will appear, so scroll down until the 'Microsoft Date and Time Picker 6.0' option appears. Click the option and then click 'OK' to return to your spreadsheet.
3. Draw, position and size the pull-down calendar. Draw a rectangle the size you desire for the calendar. Use the resize handles on the drop-down box to adjust the size if necessary. To move the calendar to another location on the Excel spreadsheet, click inside the calendar, hold the mouse button down and drag it to a new location. Resize the cell to fit the calendar by double-clicking the top margin lines.
4. Test the calendar. Deselect the 'Design Mode' tab in the main menu ribbon by clicking it once. Click the black triangle to the right of the calendar date, and your calendar will appear. If you need to make additional adjustments, go back to 'Design Mode.'
Link Calendar to Another Cell
5. Select the link cell. Select the cell you want to update when the date on the calendar changes, such as H9. In the formula bar, type in '=B5' (or whatever cell the calendar is in). Then click anywhere outside the cell to deselect it.
6. Create a link between the calendar and the update cell. Click 'Design Mode' and then click on the drop-down calendar to select it. Select 'Properties' option from the options box next to the 'Design Mode' button and locate the 'Linked Cell' option, and in the 'Linked Cell' information box, type in 'H9' (or whichever cell you select for the date update). Close the 'Properties' box and click on 'Design Mode' to deselect.
7. Activate the update. Change the date on the pull-down calendar. The update will appear in the linked cell.
Read more ►

Wednesday, December 19, 2012

How to Make a Dashboard in Excel


1. Open an Excel workbook containing data you want to manipulate. Create a new worksheet, inserted at the front of the workbook, and call it 'Dashboard.'
2. Click 'File' and then 'Options.' Click 'Customize Ribbon.' Tick the checkbox next to 'Developer' and click 'OK.' This enables your form control. Forms can be a vital component in an Excel dashboard.
3. Insert form items by clicking 'Developer,' 'Insert' and selecting a form option. With forms, such as a combo box, you can 'Format Control' and choose an output cell. That cell, in the case of a combo box, will show a number representing your selected option. For a combo box displaying the days of the week, for example, the output cell (hidden) will contain a value between '1' and '7.' This number can then be used by a graph or macro to display data only for that day.
4. Create a linked cell to link to other locations. This is useful if, for example, you have a totaled set of figures on the second worksheet and want to create a graph on page one but also show those totals. Type out 'Total' and 'Subtotal,' for example, on the first page, and then click the cell to the right of 'Total.' Type an equal sign ('='), then click the second worksheet, select the 'Total' cell figure and press 'Enter' to create a link to that cell. You can change the data on that page and it will still update on the first page.
5. Use color formatting to improve the look of your dashboard. This is useful not just for block color but also for conditional formatting. For instance, select a table of data and click 'Home,' 'Conditional Formatting,' 'Color Scales' and then 'More Rules.' You can select a different color scale and edit the figures so that high figures, for example, are displayed as red and low figures as green. Use these basic techniques to start creating your dashboard.
Read more ►

How to Make a Graph in MS Excel That You Change Every Day


1. Open the Microsoft Excel 2010 spreadsheet that contains the data you want to make into a dynamic graph.
2. Click anywhere in your data, then click the 'Insert' tab at the top of the screen. Click the chart button that corresponds to the type of graph you wan to use, then select your desired graph from the popup menu to make it appear on the spreadsheet.
3. Click the 'Formulas' tab at the top of the screen, then click the 'Define Name' button in the middle of the ribbon. Type a name for the first data series in your chart. This is normally the same text as appears in the header above the data series on your spreadsheet, but can be anything you desire.
4. Place your cursor into the 'Refers to:' box. Delete whatever is in the box, then type in '=OFFSET(' and click on the first cell in the column or row where the data for this series is located. Type in a comma, the number one, another comma, then number zero, then another comma. Then type in 'COUNTA($A:$A)-1)' but change each 'A' after the dollar signs to the column letter or row number where your data is located.Your final formula should look similar to this:=OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)Click 'OK' to close the Define Name window. Click the 'Define Name' button again and repeat the entire process for each data series in your graph.
5. Right-click on your graph and choose 'Select Data' from the popup menu. Click on your first data series, listed on the left side of the window that appears, then click 'Edit.'
6. Place your cursor into the 'Series values' text box and press backspace several times to delete everything after the exclamation point. Type in the name of the range you created for this set of data.
7. Click 'OK' to go back to the Select Data Source window. Click on a different data series and repeat the process. Continue until you have entered your names for each data series, then click 'OK' to close the Select Data Source window. Your chart now changes as you add new data each day.
Read more ►

How to Make an Address Book on a Computer


1. Open up Microsoft Excel on your computer. You will see that a spreadsheet opens up with predetermined rows and columns. You will use these rows and columns to set up your address book.
2. Add a title to the top of the spreadsheet. This will help you remember what information is in a particular address book. You may need to set up different address books for various purposes--for example, one for members of a book club and one for family members. Having a clear title will help you keep everything organized. Make the title bold and increase the size of the font, using the tools on the top toolbar.
3. Skip a couple of lines after the title and enter headings for the columns in the address book. The following column headings will be very helpful: Name, Address, City, State, ZIP Code, Phone Number, Fax Number and E-mail Address. Type in these headings into columns A to H, respectively. It is very important that each item is entered into a separate column.
4. Center and bold the column headings. To do this, click on the row number to the left of the row of headings. This will highlight, or select, the entire row. And, then, use the bold and center tools on the top toolbar.
5. Enter the contact information in each column. Information for each contact should be entered on a separate row. Depending on how many contacts you have, this could take some time. But, remember, you will only need to do this one time. In the future, you will just have to add new contacts and update information. If you do not have certain items of information for certain contacts, leave the cell blank. Remember to use the two-letter state abbreviations that the post office requires.
6. Format the text in the ZIP Code column to make all of the ZIP codes look the same. Highlight all of the text in the column, except for the column heading. Right-click on the highlighted cells and select 'Format Cells.' Click on the 'Number' tab, and then click on 'Special' in the 'Category' box. Click on 'Zip Code' or 'Zip Code 4' in the 'Type' box. Click 'OK.' This will make all of the ZIP codes look the same.
7. Format the text in the Phone Number column so that all of the phone numbers in the column look the same. Highlight the text in the Phone Number column, except for the column heading. Right-click on it and select 'Format Cells.' In the 'Number' tab, select 'Special' from the 'Category' box. Click on 'Phone Number' in the 'Type' box. Click 'OK.'
Read more ►

How to Draw a Histogram With MS Excel


1. Type your data into column A of your worksheet. For example, click on cell 'A1' and type your first data item, click on cell 'A2' and type your second data item. Continue down the column, clicking on cells and typing your data in until all of your values are on the worksheet.
2. Place your bin widths into column 'B.' Type the top value of each bin, starting in cell B1. For example, if your bin widths were 0 to 4 feet, 5 to 6 feet and 6 feet to 8 feet, type '4' into cell B1, '6' into cell B2 and '8' into cell B3.
3. Click on the 'Data' tab, then click 'Data Analysis.'
4. Click on 'Histogram,' then click on 'OK.'
5. Type the range of your inputs ito the 'Input Range' box. Your inputs are the values you entered into column A. For example, if your inputs are in cells A1 to A10, type A1:A10.
6. Click on the 'Bin Range' box and type the location of the bin ranges. For example, type B1:B3 to indicate the bin ranges are in cells B1 to B3.
7. Click 'New Workbook' under 'Output Options' then click on the 'Chart Output' check box.
8. Click on 'OK' to create the histogram.
Read more ►

Tuesday, December 18, 2012

How to Insert an Excel Spreadsheet to a VB Form


1. Open Microsoft Excel and type 'A' in 'A1,' 'B' in 'B1,' 'Column A' in 'A2,' and 'Column B' in 'B2.' Save your spreadsheet to 'C:\' as 'ExcelFile.xlsx.'
2. Open Microsoft Visual Basic 2010 Express, click the 'File' menu and select 'New Project.' Click 'Installed Templates,' select 'Windows Forms Application' and click 'OK.'
3. Press 'Ctrl' 'Alt' 'X' to open the 'Toolbox' window. Double-click 'DataGridView' to add a new Data Grid View control to 'Form1.' Double-click 'Button' in 'Toolbox' to add a new button to 'Form1.'
4. Double-click 'Button1' to open the 'Form1.vb' module. Type the following above 'Public Class Form1':Imports System.Data.OleDb
5. Type the following inside 'Private Sub Button1_Click' to declare a 'DataSet' and define the Excel connection:Dim ds As New DataSet()Dim connectionString As String = 'Provider=Microsoft.ACE.OLEDB.12.0;' _'Data Source=C:\ExcelFile.xlsx ;' _'Extended Properties=Excel 12.0;'
6. Type the following to connect to the 'ExceFile.xlsx' file and fill the 'DataSet':Dim excelData As New OleDbDataAdapter('SELECT * FROM [Sheet1$]', connectionString)excelData.TableMappings.Add('Table', 'ExcelSheet')excelData.Fill(ds)
7. Type the following to display the spreadsheet in your Data Grid View:Me.DataGridView1.DataSource = ds.Tables(0)Me.Refresh()Press 'F5' to run your program and press 'Button1' to import the Excel spreadsheet.
Read more ►

How to Tell If a Worksheet Is Protected in Microsoft Excel 2003


Single Worksheet
1. Click on the 'Tools' menu.
2. Select 'Protection->Protect Sheet.'
3. Read the dialog box. If 'Protect worksheet and contents of locked cells' is checked, the sheet is protected.
Workbook (All Worksheets)
4. Open the workbook. If you cannot open the workbook, it is protected at the file level.
5. Add another worksheet to the workbook. If you can't add another sheet, the workbook is protected.
6. Resize the Excel window. If you can't, the workbook is protected.
Read more ►

Blogger news