You probably already know that you can fill a series of Excel cells by entering the first two numbers in a series and then select the two cells and drag on the marker in the bottom right corner of the selection. Excel fills the selected cells with the next numbers in the series. to find more fill options, including the ability to copy the series rather than filling it, select the cells but use the right mouse button to do the dragging. If you’re filling dates you’ll get options like Fill Weekdays and Fill Months – that let you control the fill series that Excel creates for you.
Wednesday, July 18th, 2007
Align data in Excel
When you make an Excel row much deeper by dragging on the marker below the cell number you’ll often find the cell entry hugs the bottom of the cell. If you’d prefer to have it centered in the cell you won’t find the Center button works to do anything more than center the cell entry horizontally.
To center it vertically use the Format, Cell dialog and choose the Alignment tab. From the Vertical list choose Center and the entry will be aligned evenly between the top and bottom cell margins.
Labels: Center text, Excel 2003
Categories:Uncategorized
Wednesday, July 4th, 2007
Excel – highlight cells containing formulas
There’s no shortcut way to color code cells in Excel which contain formulas but this workaround is simple and fast.
Choose Edit, GoTo, Special button and click Formulas and then Ok. Now fill the cells with a color using the Fill Color tool.
It’s now clear which cells contain formulas and those that do not.
Labels: Excel 2003, highlight cells containing formulas
Categories:Uncategorized
Monday, July 2nd, 2007
No new file when you launch Excel
When you open Excel you will, by default see a new blank workbook. If you’d prefer to see nothing at all you can do so by altering the Excel startup icon.
Right click it and choose Properties and then in the Command area add the text /e at the end after the double quotes. Now Excel will launch without the splash screen and with no new workbook.
Labels: command line switch, Excel 2003, startup
Categories:Uncategorized
Wednesday, June 27th, 2007
Gridlines – Yes or No?
In Excel there are gridlines and gridlines. You can display them on the screen as you work or on the printouts or both or neither.
Confusing?
To display or hide gridlines as you work, choose Tools, Options, View tab and enable or disable Gridlines.
For printing, choose File, Page Setup, Sheet tab and enable or disable Gridlines for printing…
Now you know.
Labels: Excel 2003, gridlines
Categories:Uncategorized
Wednesday, June 20th, 2007
Millions in Excel
Excel has some cool formatting tricks up its sleeve and one of these is its ability to shrink really big numbers down to size.
So, if you have values in the millions – like your salary – Ha!, you can size them down to size using a custom format. Select the cells, choose Format, Cells, Number tab and click the Custom group and type #,,”M” and Excel will format 200,000,000 to read 200M! The numbers aren’t altered it’s just a simpler way of displaying them.
Since the Y axis of a chart inherits its formatting from the top left cell in the chart data range this lets you format a chart’s Y axis to show the smaller values too.
Labels: Excel 2003, Excel 2007, number format
Categories:Uncategorized
Saturday, June 16th, 2007
Self Portrait in Excel
Excel can take photos of itself, it’s a fun technique for applying a portion of a worksheet back into the worksheet as an image or into some other application.
To do this, make a selection around the area you’re interested in and hold Shift as you click to open the Edit menu. There’s a new option called Copy Picture which, if you click it, you can then select how to copy the picture – as shown on the screen or when printed etc.. Make your choice and then you can paste the image into any application.
To paste it back into an Excel workbook, Shift + click on the Edit menu and you can paste it back in by choosing Paste Picture.
You can do some funky things with this. Take a copy of a portion of a worksheet using this technique and then select a bar in a bar chart. Choose Edit, Paste and you’ll paste the image in as your new bar chart fill.
Labels: chart image, Excel 2003, take a picture of a worksheet
Categories:Uncategorized
Tuesday, May 22nd, 2007
Quick moves in Excel
My friend Theda and I were talking the other day about a project she’s working on where she has to reorder a lot of items into alphabetical order and, in some cases, an order which is not alphabetical.
While she is doing the project in Microsoft Excel there are, of course, difficulties in Excel in moving items around as Excel does not, by default, open up a row when you choose to move an item from one part of the worksheet to another – instead, Excel thinks you want to overwrite the target cells – as if!
There is, however, a way to do this which opens up the space for the new row and which closes up the space that you have just made available. To do this select an entire row or range of rows in Excel by clicking on the row number or numbers. Hold your mouse over the dark outline around the rows and hold the Shift key as you drag the rows to their new position. The empty space that you have created will be closed up and a new space will be created between the other two rows for your item.
Problem solved!
Labels: Excel 2003, Excel 2007, Moving rows
Categories:Uncategorized
Monday, May 21st, 2007
Put a button on an Excel worksheet
I love the form tools in Excel they let you do so many cool things. One thing I love to do is to make buttons run my macros. To do this, use the Forms toolbar, click the Button tool and drag to create a button on the worksheet. When you do, a dialog appears with all your macros listed so you can select the one to use. You can also edit the text on the button and when you’re done simply click it to run the macro – sweet huh?
If you’re using Excel 2007 you have to add the button tool to your Quick Access toolbar (it’s in the Commands Not In The Ribbon group and it’s called Button (Form Control).
Labels: button, Excel 2003, Excel 2007, Form control
Categories:Uncategorized
Tuesday, May 8th, 2007
Text on a chart, Excel
It’s easy enought to place a title or Y and X axis titles on a chart but what about a note or comment?
The secret is in the Drawing tools. Display the Drawing toolbar and click on the Text Box button. Now you can drag a text box on your chart and add text inside it. Size it, format it and you’re off.
Labels: chart, Excel 2003, text on a chart
Categories:Uncategorized