Excel

Browse » Home » All posts

Thursday, May 17, 2012

How to Find Blank Cells in a Column in MS Excel

Sort Method
1. Open the Excel file that you want to work with. Select the correct spreadsheet from the list of sheets at the bottom of the window. Click on any cell in the column where you are looking for blank cells.
2. Click the 'Data' tab at the top of the window. Locate the 'Sort Filter' area of the ribbon.
3. Select either the 'ZA' button, to sort the information from largest to smallest, or the 'AZ' button, which will do the opposite.
4. Click on any cell in the column you just sorted. Move your mouse to the very bottom of the selected cell, so that the pointer changes into a set of four arrows. Double-click the mouse button to immediately go to the last entry in that column. The blank cells are all located below this entry.
Filter Method
5. Open the Excel spreadsheet that you want to work with and choose the correct sheet from the bottom of the window. Click on any cell in the column where you are looking for blank cells.
6. Click the 'Data' tab and find the 'Sort Filter' area. Click the 'Filter' button. You will see drop-down arrows appear at the top of each column.
7. Select the drop-down arrow for your desired column to open up a small window. Click in the box next to 'Select All' to remove all of the check marks. Scroll to the bottom of the list and place a check mark next to 'Blanks.'
8. Click 'OK' and you will see the report get filtered to only show rows where your selected column has a blank cell.

How to Unlock a File in Excel 2007

1. Open the Excel file.
2. Click the 'Review' tab on the command ribbon.
3. Click the 'Unprotect Sheet,' 'Unprotect Workbook' or 'Unprotect Shared Workbook' button in the 'Changes' group.
4. Type the password if a dialog box opens. Click 'OK.'
5. Edit the unprotected file.
6. Press 'Ctrl S' to save this file.

How to Convert Military Time on an Excel Spreadsheet

1. Select the cell with military time. Place your mouse over the cell. Click your left mouse button once to select the cell. The cell is active when outlined with a black border. In addition, the status bar will indicate 'Ready' in the lower-left corner of the program window.
2. Right-click the cell. Open the short-cut menu by right-clicking the cell. Click 'Format cells...' from the short-cut menu. The 'Format Cells' dialogue box will open.
3. Choose a format. Make sure the current tab is 'Number.' Select 'Time' from the 'Category' section of the 'Number' tab. In the 'Type' section, select the time format you would like to use. Time formats marked with an asterisk adjust based on the regional settings of your operating system.
4. Accept the changes. Click 'OK' to change the military time to your selected format.

How to Make a Button for a Macro in Excel

1. Open the Excel 2010 spreadsheet where you want to add a macro button. Click the 'File' tab at the top of the screen and choose 'Options' from the list items on the far-left side of the screen.
2. Select 'Customize Ribbon' on the 'Options' window. Locate the 'Developer' entry in the rightmost column under the 'Customize the Ribbon' heading. Click the empty box next to 'Developer' to add that tab to the Excel ribbon. Click 'OK' to go back to the spreadsheet.
3. Click the 'Developer' tab at the top of the Excel spreadsheet. Locate the 'Controls' area of the ribbon and click the 'Insert' button. Under the 'Form Controls' heading, select 'Button.' The cursor will change to a ' ' sign.
4. Click and hold the mouse button anywhere on the spreadsheet. Drag the mouse to the left and you will see a box appear under the cursor. When the box is the size that you want the button to be, release the mouse button.
5. Select the macro you want the button to run when the user clicks it and click 'OK.' If you haven't created the code for the macro yet, you can type in a name for the macro and click 'New.' The VBA console will open up and you can enter in the code for the macro between the 'Sub' and 'End Sub' lines in the console.
6. Click the 'Design Mode' button on the ribbon and right-click the button. The mouse pointer will change to a ' ' sign and you can click and drag the button to another location. Click the 'Design Mode' button again to lock the button in place.
7. Right-click the button and choose 'Edit text' to change the text that appears on the button. Enter in whatever text you want and press 'Enter.'

How to Use the Multiple Rows Function in Excel

1.
In Excel, place your cursor in an empty cell located below the group of numbers that you would like to reference.
2.
Click on the 'Function' key located to the left of the Formula task bar.
3. Select 'Rows' from the Function list.
4. Click 'OK'.
5.
Highlight the section of rows containing the set of numbers you would like to add or enter the row and column reference points in the Formula bar.
6.
Click 'OK' to exit.
7.
Test the formula's accuracy by adding a couple of new rows. Highlight two rows within the middle of the range. To do this, left-click your mouse and hold down the 'Down-Arrow Key.' Release. The rows should still be highlighted.
8.
Right-click your mouse, then select 'Insert.'
9.
Verify that the cell containing the formula references all of the rows including the new row additions you just made.

Tuesday, May 15, 2012

How to Duplicate Words in Excel

1. Double-click the icon on the desktop or click 'Start,' 'All Programs,' 'Microsoft Office' and Microsoft Excel 2010' to launch Microsoft Excel.
2. Type a word into the first cell and press 'Enter' to accept the entry and move to the next cell down.
3. Type a different word that starts with a different letter into the second cell down the first column and press enter.
4. Type the first letter of either word previously entered into the column and Excel will offer to automatically complete the entry to match the previously entered cell. Press 'Enter' to accept the entry or keep typing to make a new entry.
5. Press the 'Delete' key and then press 'Enter' if typing a shorter version of the previously entered word to accept the information as typed instead of the automatic completion suggestion.
6. Click 'File,' 'Options' and click on the 'Advanced' menu choice on the left to review the settings for automatic completion. Turn automatic completion on by checking the 'Enable AutoComplete for Cell Values' box or turn it off by clearing the check box. Click 'OK' to save the setting.

How to Merge Cells in Excel

1. Start Microsoft Excel, and open the spreadsheet that contains the multiple cells you would like to merge into one cell. If you like, you can just start a new spreadsheet to perform the task of merging cells.
2. Select the cells that you would like to merge by clicking on the first cell with your mouse, holding down your left mouse button and dragging until all of the cells you want to merge are highlighted.
3. Right-click to bring up the Excel menu and click on 'Format Cells...' to open the 'Format Cells' dialog box. In the 'Format Cells' dialog box you can change all of the formatting options related to the cells and text you have within the cells, including the option of merging cells together.
4. Click the 'Alignment' tab at the top of the 'Format Cells' dialog box to show the alignment options for the currently selected cells.
5. Use your mouse to click the check box before the option 'Merge Cells' under the 'Text control' section of the 'Format Cells' dialog box.
6. Press the 'OK' button with your mouse to close the 'Format Cells' dialog box and merge the selected cells together in your Excel spreadsheet.

How to Write a Protected Excel Spreadsheet

1. Launch Excel and create or modify the data in an existing worksheet to suit your particular needs. Click on 'File,' and 'Save' to save your work.
2. Click on the 'Tools' menu. Select 'Protection' from the drop-down menu that appears, and then select 'Protect Sheet.'
3. Type in a password of your choosing when prompted, and click 'OK.' Remember this password as it will be required when making changes to the worksheet.
4. Protect the entire workbook of worksheets you have created by clicking on the 'File' menu, and then selecting 'Save As.' The Save As dialog box opens.
5. Click on the button labeled 'Tools.' Select 'General Options,' and the Save Options dialog box opens.
6. Type in the password of your choosing in the text box, and click 'OK.' This password will now be required to open the workbook file.

How to: PMT Function in Excel 2003

1. Click on an empty cell in the Excel 2003 worksheet and then type PMT( interest_rate, number_payments, PV).
2. Replace 'interest_rate' with the actual interest rate. For monthly payments, divide the interest rate by 12 and for weekly payments, divide by 52. For example if you have a 6 percent interest rate and will be making monthly payments, type '6%/12.'
3. Replace 'number_payments' with the number of payments to be made. For example, if you have four years of monthly loan payments, then type '4*12.'
4. Replace 'PV' with the loan's principal value. For example, if you have a $6,500 loan then type '$6500.' In this example, the final function should read 'PMT(6%/12, 4*52, 6500).'
5. Press the 'Enter' key to get the monthly payment.

How to Change MS Excel From English to German

1. Purchase the German language pack and download it if you haven't already done so. Install the language pack by double clicking its icon and following the on-screen prompts.
2. Open Microsoft Excel and click the 'File' tab at the top of the page.
3. Click the 'Options' link in the left pane and select 'Language.'
4. Click the drop-down menu under Editing Languages and select the German version you want to use for spell checking, according to your country preference. Click the 'Add' button next to the selection. Select the entry in the list and click the 'Set as Default' button to the right of the list.
5. Move down the window to the Choose Display and Help Languages section. Click the German entry in the Display Language list and click the 'Set as Default' button below the list. Repeat the process for the Help Language list.
6. Click 'OK' to close the window and apply the changes.

How to Tell Which Version of Excel Created a Spreadsheet

1. Locate the Excel file on your computer. For example, run Windows Explorer to find the file.
2. Look at the file extension for the file. The file extension is the set of characters that come after the dot. For example, you might see .xls.
3. Decide which version of Excel created the spreadsheet. If the file extension is .xlsx, Excel 2007 created the file. If the file extension is .xls, Excel 2003 (or an earlier version of the software) most likely created the file. There is an exception: Excel 2007 has the capability of being able to make backwards compatible (.xls) files.

How to Put a Password in My File in Excel

1. Launch Excel.
2. Click the 'File' menu option, then click 'Info.'
3. Click the 'Protect Document' icon and select 'Encrypt With Password.' A window pops up and prompts you to enter a password.
4. Type a password into the box. Mix upper- and lowercase letters with numerals and special characters -- such as question marks and dollar signs -- to improve the strength of the password. Excel allows up to 255 characters in a password.
5. Click 'OK' to input the new password. Save the file normally to lock in the encryption.

How to Create a Macro in Excel 2007

1. Open Excel 2007 and click the 'Developer' tab at the top-right end of the window. Click the 'Record Macro' button at the top of the 'Code' heading.
2. Type a name for the new macro in the 'Name' text box, such as 'Adding Columns' or 'My New Macro.' Type a key in the 'Shortcut Key' text box if you want to map a shortcut to the new macro. Pressing the 'Ctrl' key and the key you specify will automatically run the new macro.
3. Type a brief description of what the macro is intended to accomplish in the 'Description' box if you think other users may potentially need your macro. Click 'OK' to begin recording the new macro.
4. Perform the specific operations you want recorded in the macro, such as adding together the values in columns, creating new rows or calculating the average of two cells.
5. Click 'Stop Recording' when you are done performing operations. Click the 'Macros' button when you need to run the macro. Click the macro's name and then choose 'Run.'

Monday, May 14, 2012

How to Delete Multiple Rows in Excel 2007

1. Open 'Microsoft Office Excel 2007.'
2. Click the 'Office' button, and select 'Open.' Locate the file you want to edit, and click the 'Open' button.
3. Click the row heading on the left side of the sheet for the first row you want to select.
4. Press and hold the 'Ctrl' key, and click the row headings for the remaining multiple rows you want to select.
5. Click the arrow beside the 'Delete' icon in the 'Cells' group under the 'Home' tab in the Office ribbon menu at the top of the program.
6. Click 'Delete Sheet Rows.'
7. Click the 'Save' icon located next to the Office button to save the changes you've made to the file.

Sunday, May 13, 2012

How to Get a List of the Unique Values in an Excel Spreadsheet

Filtering
1. Highlight the cells you wish to filter for unique values.
2. Click the 'Data' tab and select 'Sort Filter,' then 'Advanced.' Inside the box that pops up, choose 'Filter the list, in place' if you wish to filter the data within the spreadsheet. You can also copy the filtered list into a new table by clicking 'Copy to another location,' then selecting the cell range you wish to paste by entering the range in the 'Copy to' field.
3. Choose the check box for 'Unique records only,' then click 'OK' to generate the list of values.
Removing Duplicate Values
4. Highlight the cells from which you wish to remove duplicate values.
5. Navigate to the 'Data' tab and select 'Remove Duplicates,' which is located in the data tools group.
6. Select one or more columns under the 'Columns' section in the pop up box, based upon those columns from which you wish to remove duplicates. Click 'OK.'
7. A message will display showing how many values were removed from the data table and how many unique values are left. Document the information you need, then click 'OK' to view the table.
Applying Conditional Formatting
8. Highlight the cells to which you wish to apply conditional formatting for unique values.
9. Select 'Conditional Formatting' under the 'Home' tab and within the 'Style' group,
10. Choose 'Manage Rules,' then select 'New Formatting Rule.'
11. Ensure the cell range and worksheet displayed in the 'Show formatting rules for' box are appropriate. Then select 'Edit rule.'
12. Select 'Format only unique or duplicate values.' in the Edit Formatting Rule box that pops up. In the 'edit the rule description' field, choose 'Format all' as the list and then choose 'unique' or 'duplicate,' depending upon which items you want to highlight. Choose the type of formatting you want (i.e., color, font, fill) then click 'OK.'

Subscribe to: Posts (Atom)