Wednesday, September 28, 2011

How to Insert a Button in Excel 2007


1. Open Excel and check that the 'Developer' tab is available on the ribbon. If it is not, click the Windows button, and then click the 'Excel Options' button. In the 'Popular' options, click the check box next to 'Show Developer tab in the Ribbon.' Click 'OK.'
2. Click the 'Developer' tab and click 'Insert.'
3. Click the first icon in the upper left corner of 'Form Controls' for inserting a form control button, or click the first icon under 'ActiveX Controls' for an ActiveX button.
4. Left-click on the portion of the Excel sheet where you want to create the button and drag to create a box.
5. Assign a macro to the button in the 'Assign Macro' window that pops up for the form button. You can create a new macro by clicking the 'New' button or record one by clicking the 'Record' button. 'New' opens Visual Basic so you can enter your code. To record a macro, press the 'Record' button and give the macro a name. Assign a shortcut key and description if desired and click 'OK.' Then perform the actions the macro should perform. When finished, click the 'Stop Recording' button in the ribbon and the macro is saved. To add code to the ActiveX button, double-click the button and then add in your code. Press 'F1' for help and press 'Alt' 'Q' to save and return to Excel.
Read more ►

How to Convert Excel to a Visio Organization Chart


1. Create a table in Excel listing all of the names and titles you want to use in the organization chart. Enter the headings 'Title,' 'Name' and 'Reports To' in the first row of the worksheet. Enter each person's title, name and to whom they report. For the CEO or anyone else who does not report to anyone, leave that cell blank. Save the worksheet and close Excel.
2. Open Visio. Go to the 'File' menu or tab and select 'New.' Select 'Business' as the template category and then click on 'Organization Chart Wizard.' The wizard will open.
3. Select 'Information That's Already Stored in a File or Database' and click 'Next.' Choose 'A Text, Org Plus or Excel File' and click 'Next' again. Click 'Browse' and locate the Excel worksheet you created. Select this file and click 'Next' to import it.
4. Select the appropriate headings that correspond to those in your Excel worksheet, such as 'Name' and 'Reports To.' Click 'Next.' Add the columns from which you want to display data in the organization chart and click 'Next.' Click 'Next' again and then click 'Finish.' A basic organization chart will appear.
5. Make changes to the organization chart as desired. Right-click a shape and select 'Format' to fill it with color or change the line. Select text in a shape, right-click and select 'Font' to change the font, size or color. Save the organization chart when you are finished.
Read more ►

How to Add 12 Weeks to a Date in Excel


1. Open Microsoft Excel.
2. Write the start date in cell A1 using the familiar format month/day/year and press 'Enter.' As an example, to enter January 1, 2010, you would enter '1/1/2010' in cell A1. Alternatively, enter the function '=today()' to enter the current date.
3. Type '=A1 84' in cell B1 and press 'Enter' to calculate the date exactly 12 weeks from the start date. Alternatively, enter '=A1 (7*12)' to break up the number of days in the week times the number of weeks. This allows you to easily change the number of weeks. You can even reference the number of weeks entered in another cell, such as A2, by changing the formula to '=A1 (7*A2).'
Read more ►

How to Name a Chart Object in Excel


1. Right-click your Excel spreadsheet file and select 'Open With.' Click 'Microsoft Excel' in the list of programs.
2. Click the chart you want to edit in the spreadsheet. Click the 'Layout' tab or ribbon if you have Excel 2007.
3. Click the 'Properties' button. Type a new name for the chart in the 'Chart Name' text box. Click 'OK' to save the changes.
Read more ►

How to Protect Individual Cells in Excel 2003


1. Launch 'Excel 2003' and open the file that contains the individual cell that you want to protect. Click once on the cell that you want to protect. This action will display a border around the cell to indicate that it is selected.
2. Click the 'Format' drop-down menu, and select the 'Cells' option. Select the 'Protection' tab. Click the check box next to the 'Locked' listing.
3. Click the 'Tools' menu and highlight 'Protection' to display a list of available options. Click the 'Protect Sheet' listing, and click 'OK.'
Read more ►

Tuesday, September 27, 2011

How to Convert a Number to a Date in Excel 2003 When the Format Is yyyymmdd


1. Create a grid with your numbers in Excel, if you haven't already done so. Open a new spreadsheet and type your numbers into column A. Leave column B blank – it will be used momentarily for the data calculation.
2. Type the following formula into column B:=DATEVALUE(MID(A2,5,2)'/'RIGHT(A2,2)'/'LEFT(A2,4))This sets up the formula to convert your numbers in column A to a date format, and the result will be displayed in column B. The output won't actually look like a date until you format the cell. Continue this formula for as many rows as you have data. You do this by moving your mouse to the corner until the cursor changes to a cross; then, click and drag down the number of rows required.
3. Highlight the cells in column B that you want to convert to a date. Select 'Format' and then 'Cells.' Select 'Date' in the Format Cells box. Choose any date format desired. Readable dates instantly populate in column B of your spreadsheet.
Read more ►

How to Unprotect a Word Doc


1. Click the 'Review' tab at the top of the screen.
2. Click 'Restrict Editing' in the Protect group.
3. Click the 'Stop Protection' button that appears within the Restrict Formatting and Editing window.
4. Enter the password in the blank field in the Unprotect Document dialog box that opens. Click 'OK.'
Read more ►

How to Make an Invoice Spreadsheet in Excel


1. Open Microsoft Excel. A blank spreadsheet will open, and you will notice that predetermined rows and columns are already set up.
2. Enter a title at the top of the spreadsheet. Start typing your title into cell A1. Having a clear and specific title for the spreadsheet will help you remember what invoices are on a particular spreadsheet. Perhaps you need separate spreadsheets for invoices received and invoices sent out. Bold the title by clicking on the cell and using the bold tool on the top toolbar.
3. Set up column headings a few lines down from the title. Begin entering column headings in column A. Some helpful column headings include Invoice Date, Invoice Number, Person or Company being invoiced, Invoice Amount, Date Received, Date Paid and others depending on your exact purpose for the invoice spreadsheet.
4. Bold and center the column headings. Click on the number to the left of the row where the column headings are entered; the entire row will be selected. Use the tools on the top toolbar to bold and center the column headings.
5. Enter data into the spreadsheet. Be sure to plug everything into the appropriate column. If you are missing a piece of information, simply leave the cell blank.
6. Format cells that contain numbers so that all of the numbers in the column look the same. To format cells, highlight all of the cells that have the same types of numbers in them, such as amounts. Then right-click on the highlighted cells and select 'Format Cells.' In the box that opens up, click on the 'Number' tab. Click 'Currency' in the 'Category' box to format all cells that contain amounts in the same way. Then choose how many decimal places you want to include and click 'OK.' You can also format dates, ZIP codes, phone numbers and other data. Select these options from the 'Category' box--if you don't see something listed there, it is most likely listed under 'Special.'
7. Add totals to the spreadsheet. To do this, click in the cell where you want the total to be located. Click on the sigma symbol (it looks sort of like a capital E) on the top toolbar. Click on the first cell to be included in the total, then drag down until the last cell to be included is selected. The cells will be outlined in a moving-dashed box. Press the 'Enter' key and the total will be inserted.
Read more ►

Monday, September 26, 2011

How to Alphabetize a List in Works/Word Documents


1. Type your list, in no particular order, with each item on a separate line.
2. Highlight the complete list.
3. In the 'Home' tab, in the 'Paragraph' group, select 'Sort.'
4. In the Sort Text dialog, navigate to 'Sort by.' Then select 'Paragraphs and Text, and then click either 'Ascending' or 'Descending.' Add more entries to the bottom of the list as necessary. Repeat the sort procedure of the list with the additions.
Read more ►

How to Close the Header Footer Screens in Excel 2007


Reset Normal View
1. Press the 'Esc' key if you are in 'Full Screen' mode and cannot see the Office Ribbon.
2. Click the 'Views' tab on the Office Ribbon.
3. Select 'Normal' in the 'Workbook Views' section. This will work from any view unless you have an open dialog box that you must address first. In that case, click 'Cancel' or 'OK' in the dialog box, depending on what the screen prompts say, then select the normal view.
Within Page Layout View
4. Place your cursor at the top edge of the page, where the white that represents the page ends before the top ruler or formula bar. The cursor will turn to an icon with two arrows pointing toward each other.
5. Click your mouse button while the cursor is in this mode. This hides your header and top margin while keeping you in 'Page Layout' view.
6. Scroll to the bottom of the page and place your cursor along the bottom edge until the double arrow icon appears again.
7. Click to close the footer.
Read more ►

How to Scatter Plot Data on Excel


Scatter Plot Data in Excel 2003
1. Open Microsoft Excel 2003.
2. Place one set of data in one column and another set of data in an adjacent column. For example, place one set of data in column A, which will be your X values, and then place another set of data in column B, which will be your Y values.
3. Select the range of values to be included in the scatter plot chart. To select the range, click the first cell to be included and then drag your mouse towards the last cell to be included.
4. Click the 'Insert' menu and click 'Chart.'
5. Click 'XY (Scatter)' under the 'Chart Type' box. Select the chart sub-type you want to use under the 'Chart sub-type' box.
6. Click 'Next.' This will show you the data range and a view of your chart.
7. Click 'Next' to proceed to the 'Chart Options' window. Enter the information for 'Chart Title,' 'Value (X) axis,' and 'Value (Y) axis.' The 'Value (X) axis' is a descriptive title for the values of the X axis, while the 'Value (Y) axis' is a descriptive title for the Y axis. For example, if you're tracking weekly expenses, you can use 'Days' for the title of the X axis and 'Total Daily Costs' for the title of the Y axis.
8. Click 'Next' to proceed to the 'Chart Location' box. You have the option of placing the chart in the same worksheet as your data or in a separate worksheet.
9. Click 'Finish' to exit and display the scatter plot chart.
Scatter Plot Data in Excel 2007 or 2010
10. Open Microsoft Excel.
11. Place one set of data in one column and another set of data in an adjacent column. For example, place one set of data in column A, which will be your X values, and then place another set of data in column B, which will be your Y values.
12. Select the range of values to be included in the scatter plot chart. To select the range, click the first cell to be included then drag your mouse towards the last cell to be included.
13. Go to the 'Insert' tab. Click 'Scatter' under the 'Charts' group menu.
14. Click 'Chart Area' for the XY chart. This displays the 'Chart Tools,' 'Design,' 'Layout,' and 'Format' tabs specific to the XY chart.
15. Go to the 'Design' tab and click the chart style you want to use.
16. Type the title for your chart under 'Chart Title.'
17. Go to the 'Layout' tab and click 'Axis Titles.' Click the 'Primary Horizontal Axis Title' to place a title for the horizontal axis. Click the 'Primary Vertical Axis Title' to place a title for the vertical axis.
18. Press 'Enter' to exit and show the chart.
Read more ►

How to Lock Column Width in Excel 2007


1. Open Excel 2007 and click the 'Office' button. Select 'Open.' Browse the files and locate the workbook. Click the workbook and select 'Open.'
2. Select the worksheet in the workbook where the locked column width will occur. Right click the column header and select the 'Protection' tab. Make sure 'Locked' is selected. Click 'Ok.'
3. Click the 'Review' tab and select 'Protect Sheet.' Make sure the 'Protect worksheet and contents of locked cells' is selected. De-select the option to 'Select Locked Cell.' Click 'Ok.'
4. Right click the column and notice the option to adjust the column width is grayed out and not available.
Read more ►

How to Remove Filters in Excel 2003


1. Open Excel 2003 and select a workbook with data. Click 'File' on the menu bar. Click 'Open.' Search your files and locate the workbook. Double-click the workbook. The workbook opens.
2. Click the column header in the first column of your data. Select 'Data' on the menu bar and select 'Filter.' Click 'AutoFilter.' Drop-down lists appear in the column headers of your data. Click one of the drop-down lists and select a value. Notice your data is immediately filtered by this value.
3. Remove the filter by clicking 'Data' on the menu bar and selecting 'Filter.' Select 'AutoFilter.' The filter is removed from your data.
Read more ►

How to Change an Excel Chart to Ascending Order


1. Click on the column heading. This is Column A, B, C, etc, in a basic worksheet or the Table in a PivotTable report. Again, you must change the chart data in order to change the chart. Chart data can originate from a basic or PivotTable form.
2. Click the arrow Filter drop-down on Column heading or Column Labels. Again, a column label is the Column heading in a PivotTable.
3. Click the arrow Filter drop-down. In PivotTables, the Axis Fields are categories and the Legend Fields are Series.
4. Change the Excel Chart data to Ascending order by clicking 'Sort A to Z' for text, 'Sort Smallest to Largest' for numbers and 'Sort Oldest to Newest' for dates and times.
Read more ►

Sunday, September 25, 2011

How to Use Negative Numbers to Calculate in Excel


Calculating Negative Numbers
1. Enter a negative number into an Excel spreadsheet cell by using the minus sign (-) before the number.'-12' is an example of a negative number.
2. Use the arithmetic operator, the minus sign (-), to subtract a number using a formula.An example is the formula, '=25-10' that will give a result of '15.'
3. Use a function to calculate negative and positive numbers.An example of this is '=sum(a1:a3)', where cell a1=10, cell a2=12 and cell a3=-25 will give a result of '-3.'
4. Use a function to transform a positive result into a negative result by applying the negative sign (-) to the function. An example of this is '=-sum(a1:a3)' using the established values for those cells will give a result of '3.'
Change Negative Number Formats
5. Select a number or range of numbers to change the way Excel displays these numbers.
6. Click the 'Home' tab on the top menu and select the arrow in the 'Number' section.
7. Choose either the 'Number' or the 'Currency' category in the 'Category:' list in the 'Format Cells' window.
8. Select the number of decimal places by either typing it in or selecting the up or down arrow in the 'Format Cells' window.
9. Choose the 'Use 1000 Separator (,)' check box. Skip this if you are formatting currency.
10. Choose the currency symbol by clicking the down arrow in the box next to 'Symbol.' Skip this if you are not formatting currency.
11. Select the number format in the 'Negative numbers:' box in the 'Format Cells' window.
12. Click the 'OK' button.
Read more ►

Blogger news