How to Have Excel Solver Give Only Whole Numbers

1. Go to the 'Data' tab in Excel, and click the 'Solver' button to launch the Solver dialogue box.
2. Click the 'Add' button under Constraints to launch the 'Add Constraints' dialogue box.
3. Enter the name of the cell in the 'Cell Reference' box, or highlight a range of cells with your cursor to fill this box automatically.
4. Click the drop-down menu in the middle of the 'Add Constraints' dialogue box, and select 'int' from the menu. The word 'integer' will appear in the Constraint box.
5. Click 'OK'

Read more ►

How to Make Columns Wider in Excel

Click Drag
1. Place your mouse pointer on the top row where the column letters are displayed.
2. Move the cursor over the lines separating the columns and notice that the cursor becomes a cross.
3. Place the cursor over the line to the right of the column that you want to make wider. Be sure that it turns into a cross before going to the next step.
4. Click and drag the column over until it is the width that you want.
Use the Toolbar
5. Go to 'Format.'
6. Select 'Column' and then 'Width.'
7. Enter the width that you want the column to be in the 'Column Width' box.
8. Click 'OK.' The column's width will change.

Read more ►

How to Get the Row Number of a Cell in VBA

1. Open the Visual Basic Editor (VBE) by clicking on the 'Developer' tab and then clicking on 'Visual Basic.'
2. Click on the 'Insert' tab and then click 'Module.' This opens a blank window.
3. Copy and paste the following code into the blank window:Sub myActiveRow()myRow=ActiveCell.RowMsgBox myRowEnd Sub
4. Press 'F5' to run the routine.

Read more ►

How to Create a Button in Excel 2007

1. Click the 'Microsoft Office' button located in the top left corner of Excel.
2. Click 'Excel Options' (a rectangular button on the bottom right of the panel).
3. Click the 'Customize' category from the selection list on the left. Select 'All Commands' from the 'Choose commands from' drop-down list. Scroll down the list to select the type of button you want to add to your Excel 2007 spreadsheet. For example, if you want to add a macro to a button, choose 'Option Button (Form Control)' and click 'OK.' This adds a button to the 'Quick Launch' toolbar (located above the ribbon).
4. Click the button on the Quick Launch toolbar. Left-click anywhere on the spreadsheet and drag the mouse to create a box.
5. Right click-on the button to format the button or assign a macro.

Read more ►

How to Print Continuing Row and Column Headings for an Excel Worksheet

1. Open Microsoft Excel and the file you want to change.
2. Open the File menu and select Page Setup.
3. Select the Sheet tab.
4. Enter the cell numbers you want to use as your row headings in the Row to Repeat box. Enter the first cell number for the heading, then a colon, then the last cell number. For example: B4:F4.
5. Enter the cell numbers you want to use for your column headings in the Columns to Repeat box. Enter the first cell number for the heading, then a colon, then the last cell number. For example: A1:A2.
6. Click Print Preview to see how your document will look.
7. Select Close to exit from the Print Preview function.
8. Click OK to accept your changes.

Read more ►

How to Convert XLSM to XLS

1. Open the 2007 version of Excel or later. Click “Open” in the “File” drop-down menu. Open the .XLSM file from the folder tree that opens up.
2. Click “Save As…” in the “File” drop-down menu. Select “Excel Workbook” as the format. Check the portability notes of the format at the bottom of the Save panel to confirm the format has adequate backward portability for your needs.
3. Convert the XLSM file using an older version of Excel the same way by first downloading the converter titled “Microsoft Office Compatibility Pack for 2007 Office Word, Excel and PowerPoint File Formats.” Before downloading the converter, it is important that you update your version of Microsoft Office to the latest service pack (see Resources).

Read more ►

How to Insert Charts in Microsoft Excel 2003

1. To create a chart, you first need to select the cells you wish to be contained within that chart. To do this, left-click and hold the mouse button, then drag the cursor over the groups of cells you wish to include. Then let go of the mouse button, and the cells will remain highlighted.
2. Access the chart tool. Scroll to the “Insert” tab on the command bar and select “Charts.”
3. Select the chart for your spreadsheet. A Charts Properties menu will open, in which you can select the style of the chart you want from a drop-down menu. Select the desired chart and click “Next” to continue.
4. In the next menu, you can specify the specific columns and rows to include in the chart. If you have already highlighted the cells and columns from Step 1, you can click “Next” to continue; otherwise, type in the series of charts and rows by entering their corresponding column letters and row numbers.
5. You can label the chart, name the fields and adjust the categories, gridlines and table information simply by selecting the corresponding tab and entering the required information into the fields. Click the “Next” button to continue after making the desired entries.
6. Select where to place the chart. Choose by selecting the corresponding radial button to place the chart in a new sheet or inside of the current sheet; after making your selection, click on the “Finish” button to implement your completed chart.

Read more ►

How to Change Security Settings in Excel 2010

1. Open Microsoft Excel 2010.
2. Click the 'File' tab menu.
3. Click 'Options' to open the 'Excel Options' window.
4. Click 'Trust Center' on the left side bar.
5. Click the 'Trust Center Settings' button under 'Microsoft Excel Trust Center.'
6. Click 'Add-ins' on the left side bar to disable add-ins or require them to be signed by trusted publisher.
7. Click 'ActiveX Settings' to enable, disable or prompt before enabling ActiveX.
8. Click 'Macro Settings' to enable or disable macros with or without notifications.
9. Click 'Protected View' to select options to enable when opening potentially dangerous files. With protected view, you can open unknown files in restricted mode, which disables unknown macros, ActiveX controls or add-ins.
10. Click 'File Block Settings' to choose which file types you want to block or file types you want to open in 'Protected View.'
11. Click 'OK' to close and accept all settings.

Read more ►

How to Paste Special With Word Documents in Microsoft Excel 2003

1. Open your Word document and copy the desired portions of text you wish to paste special into Excel.
2. Open your spreadsheet and scroll to the “Edit” tab on the command bar. Select “Paste Special.”
3. Select the options to paste special. Under the “As” menu, left-click on 'Microsoft Office Word Document Object,' which will paste the copied contents from the Word document into the spreadsheet as a movable object.
4. Left-click on the 'OK' button to paste the Word document into Excel as an object.

Read more ►

How to Disable Automatic Refresh on Office Excel 2003

1. Log onto your computer and open Microsoft Excel 2003. Open the spreadsheet you want to recalculate.
2. Click the 'Options' menu. Choose 'Tools' from the list.
3. Go to the 'Calculation' tab. Choose the 'Manual' calculation option. Click 'OK' when you are done.

Read more ►

How to Merge Workbooks in Excel 2003

1. Create a shared workbook by enabling the Track Changes option. Then send it out to others to make changes.
2. Find the copy of the workbook on which you will merge the changes. In Excel, go to 'File > Open' and find the file.
3. Click 'Tools > Compare' and 'Merge Workbooks.'
4. Look for the files you want to merge and add them into the current file.
5. Click 'OK' to merge the files. This is your master copy of this workbook.
6. Save the file.

Read more ►

How to Remove Duplicates in Excel 2003

1. Open Excel 2003 and copy your two (or more) columns of data into the spreadsheet.
2. Highlight the titles of the columns from which you want to remove the duplicates then click 'Data' and then click 'Filter.'
3. Click 'Advanced Filter' and then click 'OK' if necessary.
4. Click on the 'Copy to another location' option and then click 'Unique records only.'
5. Highlight the records that you want to remove the duplicates from, repeat this process when requested and then select an empty column in the 'Copy to' section.
6. Click 'OK' and your data set is copied into the column you indicated as the 'Copy to' column, excluding any duplicate values found in the data.

Read more ►

How to Stop Excel From Rounding Up or Down the Numbers to the Nearest Whole Number

1. Launch Microsoft Excel. If you have an existing file you wish to work with, click 'File' at the top of the window, and then click 'Open' to browse to that file.
2. Locate the cell(s) containing the values you want to prevent Excel from rounding. If there are multiple cells not in the same column or row, hold down the 'Ctrl' key as you click each cell with your mouse. If you want to adjust all of the files in a row or column, click the number to the left of the row or the letter at the top of the column to select the entire row or column.
3. Click the 'Home' tab at the top of the window.
4. Click 'Format' in the 'Cells' section of the ribbon at the top of the window, and then click 'Format Cells.'
5. Click the 'Number' tab at the top of the window.
6. Click 'General' under the list of options in the column at the left side of the window, and then click 'OK.'

Read more ►

How to Import Macros for Excel

1. Open the workbook with the macro you want to import in Excel. Press 'Alt' and 'F11' at the same time to open the built-in visual basic editor.
2. Navigate to the macro you want to move using the left navigation pane.
3. Click to highlight all the VBA code for the macro. Press 'Ctrl' and 'C' at the same time to copy the macro.
4. Open the workbook you want to import the macro to. Press 'Alt' and 'F11' at the same time to open the built in visual basic editor.
5. Double click 'ThisWorkbook' in the left navigation pane. Press 'Ctrl' and 'V' at the same time to paste the macro you copied earlier into the VBA code window of the new workbook. Your macro is now imported.

Read more ►

How to Use the Scroll Bar in Excel

1. Open the Excel workbook that you want to navigate by double-clicking on it. If you have a different program set your default application to open Excel files, right-click on the excel file select 'Open with' and then choose 'Microsoft Office Excel.'
2. Left-click on the arrow buttons at either end of the main scroll bars to scroll down the spreadsheet one row or column at a time. The vertical scroll bar is located at the far right of the Excel window and the horizontal scroll bar is located in the bottom right-hand portion of the Excel window.
3. Left click on the position bar (the lighter colored bar segment located within either scroll bar), hold down the mouse button, and drag the bar to quickly scroll through the area of the spreadsheet that is being used.
4. Right click on any spot along a scroll bar and select 'Scroll Here' to automatically move the position bar to that spot.
5. Left click on the position bar, hold down the mouse button and the shift key and then drag the bar to quickly scroll through large numbers cells. Holding the shift key and moving the bar will scroll through thousands of cells quickly.

Read more ►