Resizing Columns In Excel

Excel will do that for you ! Position the mouse at the right-hand edge of the column header. You’ll know  the right spot when the cursor changes to a left-right arrow. Now double-click in that exact spot.  The column is just big enough for its widest text.

You can do multiple columns at once, too. Select a contiguous group of columns by clicking one column header and dragging sideways until they’re all selected. Or select a mix-and-match group by clicking one column header and then Ctrl-clicking the rest. Now double-click the divider at the right-hand edge of any of the selected columns and they’ll all automatically adjust to the perfect width. Of course, if your lines are really long, you may want to use the Text Wrap option instead or as well.

Eight Tools in Excel

1. Subtotals. When you’ve got reams of data organized by date, it may be hard to see the big picture. Excel’s built-in subtotal feature can help. Click Subtotal on the Data ribbon in Excel 2007 or choose Subtotals from the Data menu in 2003. By default it offers a sum-type subtotal at each change in the leftmost[ column, but you can pick the column and operation you prefer. You can even create multiple subtotals, perhaps for month, quarter, and year. An outline strip at left lets you suppress detail data and just see the different levels of subtotals.2. Automatic conditional formatting. Conditional formatting in Excel 2003 is a drag. Excel 2007’s automatic conditional formatting really helps point out patterns in data, and it’s simple to use. Highlight a group of cells and click Conditional Formatting on the Home ribbon. As you mouse over the choices, you see an immediate preview. You can give each cell a color that reflects its rank in the whole range of values, add a transparent data bar whose length reflects the cell’s value, and more. It’s way easier than tangling with Excel 2003’s daunting Conditional Formatting dialog.

3. PivotTables. Excel’s PivotTable feature offers quick and flexible data analysis. Want to see how many times each value in a long column occurs? Highlight the column, click PivotTable on the Insert tab (in Excel 2003, select PivotTable from the Data menu), and click Finish. Drag the column-head field name into the Row Labels box and into the Values box (in Excel 2003, the Drop Row Fields Here area and the Drop Data Items Here area). Instantly you get a sorted list of all unique values in the column, along with the number of times each value occurs. That’s just one PivotTable trick—for more, see “PivotTable Magic“.

4. Document Inspector. Your Excel 2007 documents contain a lot more information than just rows and columns of data. They may include tracked changes, comments, private properties, and more. To examine (and clean out) the hidden data in an Excel 2007 document, click the Office button at top left, point to Prepare, and click Inspect Document. It’s very similar to the Document Inspector in Word, but instead of seeking hidden text it looks for hidden rows and columns, hidden worksheets, and objects formatted as invisible. Excel 2003 has no precise equivalent. The most it can do (which isn’t much) is to remove personal information from file properties when you save files.

5. Page Break Preview. Excel will print your spreadsheet using as many pages as necessary to display all the data both vertically and horizontally. If the last column doesn’t quite fit, printing the spreadsheet may take twice as many pages. It’s even more wasteful if you have to toss those pages and try again. To avoid this annoyance, click Page Break Preview on the View ribbon (in Excel 2003 select Page Break Preview from the View menu). Now when you resize columns, change font sizes, or make other layout changes, you’ll immediately see the effect on page breaks. It’s also a quick way to find out just how many pages you’ll be printing.

6. AutoSum. After you’ve entered a column of figures, nine times in ten you’ll end the column with a total. Excel makes it easy to total up a row or column. Click a cell just after the row or column and click the AutoSum button from the Home ribbon in Excel 2007 or the toolbar in Excel 2003 (or simply press Alt-=). Excel sums the row or column automatically. Don’t want a sum? Pull down the button’s menu to choose among the average, minimum, maximum and other functions.

7. Excel lists/tables. Excel 2003 calls them lists, while 2007 changed the name to tables. Whatever you call it, this feature lets you define a range of cells and easily sort, filter, and total the columns and (hooray!) insert or append rows without screwing up your formulas. Excel 2007 also offers dozens of predefined table styles for easy formatting. In Excel 2003 highlight the cells and choose Data | Lists | Create List from the menu. In Excel 2007 click Table on the Insert ribbon. Done!

8. Get data from the Web. You’ve found a fantastic table of data on a Web page; go ahead and pull it into Excel! Click From Web in the Data ribbon (Excel 2003 users select Data | Import External Data | New Web Query from the menu). Copy/paste the Web page address from your browser to the New Web Query dialog. An arrow icon appears next to each table that Excel can import; click one or more of these and click the Import button. Presto, the data is in your worksheet. And it’s semi-live—just click the Refresh Data button in the floating toolbar (2003) or the Refresh All button on the Data ribbon (2007) to update your worksheet with the latest version of the table.

The Best Shortcut Keys in Microsoft Excel

Ctrl+Z	Undo
Ctrl+C Enter, Ctrl+V Ctrl+X	Copy, Paste, Multiple Paste, Cut
Ctrl+F, Ctrl+H	Find, Find&Replace
Ctrl+P, Ctrl+S, Ctrl+F4, Alt+F4	Print, Save, Close, Close Excel
Ctrl+Arrow	Move to edge of region
Ctrl+*	Select current region
Ctrl+A	Select all cells
Ctrl+Home Ctrl+End	Select A1, Select last cell in used range
Ctrl+Shift+End	Select from active cell to last cell in used range.
Ctrl+Shift+Home	Select from active cell to A1
Ctrl+Page Down Ctrl+Page Up	Move to the next sheet, Move to the previous sheet
Ctrl+Tab	Move to next open workbook
Ctrl+N	Open new workbook
Shift+F11	Insert new worksheet
Shift+F3	Paste function window
=+FunctionName+Ctrl+A	Insert new function
Alt+F11	Open VBE
Ctrl+Shift+Enter	Array formula
Ctrl+F3, F3	Define name, Paste name
Ctrl+Spacebar Shift+Spacebar	Select columns, Select rows
Ctrl+1, Ctrl+B, Ctrl+U	Format cells, Bold, Underline
Ctrl+; , Ctrl+shift+:	Current date, Current time

Excel: Change the font color based on the value in the cell in Excel 2003/XP/2000/97

To do this, select the cell that you wish to apply the formatting to. In this example, we’ve selected cell B8.

Under the Format menu, select Conditional Formatting.

When the Conditional Formatting window appears, enter the first condition. In our example, we’ve selected when the cell value is greater than 10.

Next, we need to select what formatting to apply when this condition is met. To do this, click on the Format button.

When the Format Cells window appears, select the formatting conditions that you wish to apply. We’ve changed the Color to Red. Then click on the OK button.

If you wish to apply more than one condition, you will need to click on the Add button.

We’ve added three different conditions. When you’re done, click on the OK button.

Now when you return to the spreadsheet, the conditional formatting will be applied. As you can see, the value in cell B8 appears in red.

Turn Excel Data and Charts into JPEGs

Read this from PC MAG. Nice little trick

http://www.pcmag.com/article2/0,2817,2269778,00.asp

Shrinking Excel Text

I use Microsoft Excel 2003 with text in the cells, and the General format applied.  when I change the format of a portion of the text, that part of the text’s font size is reduced. I can manually fix the font size, but I use Excel alot and this is quite troublesome.

press Ctrl-A to select all. Right-click anywhere in the selection and choose Format Cells. Click the Alignment tab. See the box labeled Shrink to fit? That’s the culprit. Uncheck that box and click OK. If all the text is supposed to be the same size, click the Font tab and set the desired size before clicking OK. Done!

Excel Color-Coding for the Color-Blind

There are a couple of macros that you change that can help. They need to reside in the Personal Macro Workbook. Excel doesn’t create one until it’s needed. You could create it manually, but it’s easier just to trick Excel into doing the work for you. Select Tools | Macro | Record New Macro from the menu. In the Record Macro dialog there’s a pull-down list titled Store macro in—select Personal Macro Workbook from this list. Click OK and immediately choose Tools | Macro | Stop Recording.

Now select Tools | Macros | Visual Basic Editor from the menu. In the tree display that will appear to the left, click the boxed “+” sign next to VBAProject (PERSONAL.XLS) to open it. Open the Modules branch below it, and double-click Module1. The do-nothing macro you just recorded should be visible; highlight and delete it. Now copy or paste in the VBA code below:

Function GetColor(idx As Integer) As String
Select Case idx
Case xlColorIndexAutomatic
GetColor = “Automatic color”
Case xlColorIndexNone
GetColor = “No color”
Case 6
GetColor = “Yellow”
Case 5
GetColor = “Blue”
Case 13
GetColor = “Purple”
Case 45
GetColor = “Orange”
Case Else
GetColor = “Color index” & idx
End Select
End Function

Sub WhatColor()
Application.StatusBar = _
GetColor(ActiveCell.Interior
.ColorIndex)
End SubSelect Close and Return to Microsoft Excel from the File menu. Back in Excel, choose Tools | Macro | Macros, highlight the WhatColor macro in the list, and click the Options button. Assign a shortcut key to this macro, say, Ctrl-Shift-C.

Now it’s time to test the macro! Color-code one cell each with yellow, blue, purple, and orange. Select each cell in turn and press the shortcut key. If the status bar shows “Color index #” instead of the color name, it means that you’re using a different shade than I guessed—in which case simply edit the WhatColor() function to use whatever number was reported.

Sometimes the information provided by color-coding involves seeing patterns—regions that are all the same color. Here’s a handy macro to select all cells in the current region (bordered by an empty row and empty column) that are the same color as the active cell:

Sub SameColor()
Dim idx As Integer
Dim R As Range
Dim ColorR As Range
Application.StatusBar = “”
idx = ActiveCell.Interior.ColorIndex
If idx = xlColorIndexAutomatic Then Exit Sub
If idx = xlColorIndexNone Then Exit Sub
Set ColorR = ActiveCell
For Each R In ActiveCell.CurrentRegion
If R.Interior.ColorIndex = idx Then
Set ColorR = Application.Union(ColorR, R)
End If
Next R
ColorR.Select
Application.StatusBar = ColorR.Count _
& ” cells are ” & GetColor(idx)
End SubJust as you did with the first macro, add this to PERSONAL.XLS and assign it a keystroke. With these two macros, your colleague can quickly check the color of the current cell or select all cells with the same color. Note, however, that these macros relate strictly to the static background color of a cell—they won’t reflect a color that’s present solely because of Conditional Formatting.

http://www.pcmag.com/article2/0,1895,2072272,00.asp

Make Excel Stop Shooting Blanks

It’s always a good idea to choose File | Print Preview before launching an Excel print job. This gives you a chance to head off a flurry of blank sheets from the printer. And if it shows that your data is just a fraction too wide to fit on one page, you can go back and tweak the formatting before printing.

When Print Preview shows that the current print job includes useless blank pages, try resetting the print area. Select File | Print Area | Clear Print Area from the menu and then check Print Preview again. If that doesn’t help, it probably means that there is a stray cell somewhere with data in it. Even a space is enough to make Excel print those extra pages. One solution is to highlight the actual data and choose File | Print Area | Set Print Area from the menu. That will force Excel to print the specified area and nothing more. However, rows and columns added in the future may fall outside of that print area, yielding an incomplete printout. A better solution is to transfer the data to a fresh new worksheet. Select Worksheet from the Insert menu in order to create a new sheet. Copy only the desired data from the old sheet to the new. Then delete the old sheet. That should put an end to your print area problems.

Validating Text Entries

1. Select the range of cells.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:
=IsText (A1)
where A1 is the first cell in the range.
6. Click OK.

SCREENSHOT:

1. Select the range of cells.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:
=IsText (A1)
where A1 is the first cell in the range.
6. Click OK.

SCREEN SHOT

The Best Shortcut Keys in Microsoft Excel

Ctrl+Z	Undo
Ctrl+C Enter, Ctrl+V Ctrl+X	Copy, Paste, Multiple Paste, Cut
Ctrl+P, Ctrl+S, Ctrl+F4, Alt+F4	Print, Save, Close, Close Excel
Ctrl+*	Select current region
Ctrl+Home Ctrl+End	Select A1, Select last cell in used range
Ctrl+Shift+Home	Select from active cell to A1
Ctrl+Tab	Move to next open workbook
Shift+F11	Insert new worksheet
=+FunctionName+Ctrl+A	Insert new function
Ctrl+Shift+Enter	Array formula
Ctrl+Spacebar Shift+Spacebar	Select columns, Select rows
Ctrl+; , Ctrl+shift+:	Current date, Current time