Tuesday, February 24th, 2009
I work with monthly worksheets and they develop over the month in such a way that I want the starting point for the next month to be where this month stops. So, I need to copy this month’s worksheet to make next month’s.
In Excel this is simple. Right click the tab at the foot of the worksheet and choose Move or Copy sheet. Make sure you click the Create a Copy checkbox so you make a copy or you’re toast – you’ll lose your original.
Now choose the position in the current workbook to make the copy or choose another or new workbook. Click OK and the copy is made.
I then strip out all of last month’s variable data and start over for this month… but I always make sure I made a copy before I do this, you never can be too sure.
Sunday, February 22nd, 2009
Instead, Excel 2007 offers sophisticated formatting options, but they can be applied only if you’re prepared to convert your range to a table. However, this isn’t always a desirable outcome but luckily there is a work around.
Start by selecting your data and click Home tab > Format as Table and select a table format. You will notice that Excel applies an AutoFilter to your data which is typically the most obvious part of the conversion that you don’t want. Other things come with this table format too – if you have data alongside the table – but not part of it – and you delete a row from a table or insert a row into a table, the data alongside remains in place.
So, if you want to retain the nice formatting but not the table behaviour, click in the table and choose Table Tools > Design tab and click the Convert to Range option and click Yes when prompted to convert the table to a normal range. The range will go back to behaving like a typical Excel range and the formatting will be retained.
Monday, February 16th, 2009
If you’re like me, you will open Excel in the morning and then open a series of workbooks that you work on each day. You can save time in finding and loading these files by creating an Excel Workspace.
To do this, open all the workbooks you want to have opened each time you launch Excel and then save them as a Workspace file by choosing File > Save Workspace and type a name for the file. Click Save and you can then open all the workbooks at one time by opening the Workspace file. Of course, if you just want to open a single file you can open it as normal.
In Excel 2007 – find the Workspace feature by choosing View > Window > Save Workspace.
Another alternative for opening files automatically when Excel opens is to save the file to the XLStart folder – when you do this, the file is opened every time Excel launches.
Wednesday, January 14th, 2009
Ok, so here’s the scenario, you want to enter a value like 20Kg (or 44 lb) into an Excel cell. All goes well until you try to multiply the value. Instant disaster. Your number isn’t a number it’s a piece of text and you can’t multiply or add text. You can’t divide or minus text or find the 4th power of it either – you get the idea.. it’s basically a useless entry – pretty but useless.
SO, how do you format a cell so it displays the Kg after the number but so it still treats the entry as a number? I’m so glad you asked!
The answer is to create a custom number format. Click the cell or a range of cells and choose Format > Cells > Number tab. Select the Custom entry and type something like 00.00 “kg” in the text box and click Ok. Now, enter the numbers again as 20 or 40 etc and the kg will appear automatically after the number courtesy of the format – and the number, well it’s still a number so you can add, multiply, divide and do all sorts of other wonderful things to it as you like.
You can also add the text before a number. So, for all those lovely Canada dollars I get paid by the folk at Hub Digital Living where I write a monthly Office column, I type a Custom format like “CAN” 00.00 in my sheet where I record my payments. It’s all so easly when you know how.. and now – you do too..
Saturday, January 10th, 2009
I encountered an interesting problem with Excel this morning when I tried to import data copied from a table in a PDF file. The table was bad news from my accountant about my retirement investments. I won’t be retiring any time soon 😉
The point of the issue however was that the data when copied and pasted into Excel came in neatly aligned in column but the numbers wouldn’t add up because they’d been added as text.
The solution to this is to use a feature previously called data parsing. What it does is to look at the data and convert it from one format to another. My only alternative would have been to select each cell, double-click on the cell to get the number on the screen, remove any characters that were causing issues such as any leading dollar signs or spaces and then press Enter to convert the text value into a number.
Luckily data parse does the work for you almost instantly. To do this, select the column of numbers that you’re working on. If you have a whole lot of columns to do, you still have to do each column one at a time. That’s the bad news; the rest of it is all good.
Select the column of numbers (if it includes some text entries that doesn’t matter), choose Data > Text to Columns and then select Delimited as the Original Data Type and click Next.
Click Next again and this is where you get to do the work. To convert text to numbers select the General option. If you have dates then select the Date option and select the date format that the data was created in. My values came from Australia so the date format used was dd-mmm-yy. Provided you select the date format that matches the dates you have, everything will convert just fine. Later if you want to show these in another format such as mm-dd-yy you do so using a date format. When you’re done, just click Finish.
The data will be instantly converted and you can move forward to do the same thing on the next column.
I estimate that this process took me about three minutes to do and on the data that I had it could have taken me half an hour or more to fix it all manually.
So next time you’ve imported data from an external source and when you need to convert text back into numbers check out the Data > Text in Columns option.
Friday, January 9th, 2009
Ok, so this post can happily be filed in the “Gee I really needed to know that – NOT!” category. It’s seriously fun but, unless you’re color blind, not something you absolutely HAVE to know.
It’s easy (if you’re a dab hand at changing registry entries) to change the color of the wavy underlines used for spelling and grammar errors in Word 2007. Choose Start > Run > Regedit and locate this key:
HKEY_CURRENT_USER\Software\Microsoft\Shared Tools\Proofing Tools
If the SpellingWavyUnderlineColor entry exists, double-click it and when the Edit DWORD value dialog appears, click Hexadecimal and type an eight digit hexadecimal number representing the RGB color value of the color to use (add two leading zeros to the value). So to use Blue, type 000000FF and click Ok. If the value isn’t in the list, choose Edit > New > DWORD value, type SpellingWavyUnderlineColor and then double-click it and change its DWORD value to the hexadecimal number you want to use.
To change the grammar underline color, change the GrammarWavyUnderlineColor value, and to change the contextual spelling error color change the ContextualSpellingWavyUnderlinecolor entry. If any entries don’t exist, just create them.
When you’re done, close the registry, restart the computer, and restart Word and the changes will be in place.
If you don’t know what hexadecimal color is what, visit http://www.patman.org/webdesign/hexcolors2.asp and find a color and its appropriate hexadecimal value here. Add two leading zeros and you’re done.
Of course, before you touch the Registry you should back it up in case everything goes pear shaped. Also – NEVER mess with anything you don’t understand!
Sunday, December 28th, 2008
Often when you’re working on a document including one which contains code, or foreign language words you will want the document spell checked but you’d like the code or foreign language words omitted – so you’re not distracted by the red underlining everywhere.
To do this, in Word 2007, select the text you don’t want to be checked and double click the Language entry on the status bar – typically this will show English (United States) or similar. When the dialog appears, choose the Do not check spelling or grammar checkbox. This disables spell checking for this particular word or selection. The rest of the document is spell checked as usual but words you don’t want to be checked, won’t be.
Saturday, December 20th, 2008
I needed something cute for a magazine sample the other day so I decided to create an organic shape. I needed three edges to be straight and one rounded.
Here’s how it’s done, it makes use of nodes and Bezier curves, fairly simple to do when you know how.
Start with a new Word document. Display the Drawing toolbar and choose AutoShapes > Lines > Freeform. Start in one corner and click once to begin. Click at each point around the shape so you’ll have a polygon shape. If you hold Shift as you click you’ll make a straight line and, if desired, it will be perpendicular to the previous one too. It’s important you get straight sides and square angles when you want your shape to butt up against a page edge.
Click the shape to select it, right click and choose Edit Points. Control + Click on a point to delete it if you don’t need it. Right click on a point that you want to be rounded and choose Smooth Point and then drag on the handles to shape it nicely.
When you’re done, click outside the shape to deselect it. click it again and right click, choose Format AutoShape. Choose Line Color > No Line and choose your Fill Color. Hold Control as you click and drag on the shape to duplicate it and set the Fill Color of this one a different color. Repeat if desired.
To arrange the shapes, click one to select it, right click and choose Order > Send to Back to send it below the others. Choose Order > Send Behind Text to send it below the text. The second command is used to move the shape to the bottom layer of the document below the text. Use the first command to change the layer order of the shapes so they are stacked as you want them to be.
When you’re done you should have a page that looks something like this, I sized the shape to fill the page and moved it to the edge of the page. I also added a gradient filled rectangle under everything just to finish it all off.
Wednesday, December 17th, 2008
Excel 2007 has some great tools for working with and formatting tables (previously called Lists in earlier versions). To create a table, select the range that contains the table data and from the Home tab select Format As Table. Select a table format style and, when prompted to, confirm that the selected area contains all the data for your table, whether or not your table has headers and click Ok. When you do this you will see that each heading cell displays a dropdown arrow to the right of its contents.
Unfortunately, if your headers are right aligned, the table headings will run into the arrows and be partially hidden. I don’t know why Microsoft doesn’t create a fix for this because it looks awful. To avoid this happening, select the heading cells, right click and choose Format Cells. Click the Custom setting and type @ and four spaces and click Ok. This should add sufficient spaces to the right of a heading to move the headings a little to the left so the headings can be seen clearly. Now it all looks much nicer as you see above.
Thursday, December 4th, 2008
I love it when folks call me or email to pose questions. It’s hard when you work with apps every day to remember there are folk out there for whom Word and Excel are a daily challenge and not necessarily in a good way.
Today’s came from my partner, a PowerPoint file with a serious case of bloat. Now, if it were a cow, you’d do something rather disgusting with a knife between the ribs – no it doesn’t kill them, it cures them – lets all the nasty gasses out. In PowerPoint, the solutions are different. One cause in this file was the lack of use of a Slide Master. You see the person (not my partner, she’s better at PP than that) put a wonderful but very large image on all the slides – each one had its own version of the image – instant bloat.
The solution, next time would be to build the file properly and put that image on the background layer of the Slide Master – when you do this it automatically gets added to all slides – if you have 100 slides you still have only one image – instant slimming for your file.
If you need a plain slide – no image, you create two masters – one with an image and one without – use the master you need for the slide you’re creating. Pretty easy stuff and makes a presentation much easier to email – that was the problem here – at 7Mb it was too big for most folk’s email inbox so it bounced right back and something that big doesn’t bounce so much as go splat!
The solution I used on this file was different. I didn’t want to go rebuild someone else’s file at no charge – so I grabbed a great app called PowerPoint Minimizer – it shrinks PP files really really small so folks like me can look good by solving a problem that everyone else has spent hours on in a matter of minutes.
Find PowerPoint Minimizer here for download. Best thing is you can trial it and see how it works.
Saturday, November 29th, 2008
Vista’s Windows Media Player will play audio and video files, only if it has the codecs for them. Codecs are like translation programs, without the codecs you get exactly zip, nada, zero on the screen. Sucks huh?
Unfortunately not all the codecs you need are included in Vista so you might try to play a video and find you have no visuals although the sound might play just fine, or vice versa. The solution is to download the codecs you need to play the range of media files you’re likely to encounter.
Download it and install it and you should be right to play just about any media file around.
This is the file which Microsoft should have provided. These codecs should be supplied with the player, after all, what use is a DVD or audio player if it doesn’t play disks? If you buy a physical DVD player you don’t expect to have to go out and buy something extra to make the player and your disks compatible. Just because the player is software doesn’t excuse the lack of compatibility.
So, yes, it sucks you have to go find and download this file, but kudos to the guy who did all the work so we don’t have to.
Friday, November 21st, 2008
Shield color and opacity
When you click the crop tool and drag the crop marquee over an image in Photoshop you will see a shield around the area of the image that will be removed when you commit the change. You can change this shield color from the Tool Options bar to make it any color you want. For example, click in the color selector and make it white, gray, black. You can also Adjust the opacity of this shield to make it, for example, 100% so it totally masks out the unwanted area of the image. This lets you see more clearly the portion of the image you have selected.
Rotate your crop
Rotate the crop marquee by dragging on one of its handles to change the angle of the rectangle and make an angled crop from your image. This way you can make a diagonal crop without having to first rotate the image.
This option is way too cool! Click the Perspective checkbox on the tool options bar and you can crop an image in perspective. So, drag the corner handles into any four sided shape you like – each corner operates independently of the others when this option is enabled. Then click the Commit button and the unwanted part of the image will be discarded and what remains will be reshaped and deliciously distorted to a rectangle.
You can use this feature to fix keystone perspective problems with images such as tall buildings (which tend to be wider at the base and narrower at the top) or you can use it for creative purposes.
So, next time you select the Crop tool, check out these features and put them to work on your images.
Friday, November 21st, 2008
The My Places feature in Microsoft Word and other Office applications makes it easy for you to find documents quickly by adding the folder that they’re stored inside as a link on the left hand bar of the File Open and File Save dialogs.
So, when you’re working on a project even a short-term one, navigate using the File Open or File Save dialog to the folder that the files for the project will be stored in. With the folder selected (don’t open the folder), in Office 2003 and earlier click the Tools button on the toolbar and choose Add to My Places. In Office 2007, right-click the left sidebar and click Add folder name.
This adds the folder at the bottom of the list in the left sidebar making it easy for you to navigate to that folder by simply clicking its name in the list.
If you’re using a combination of Office 2003 and Office 2007 you will notice that the left hand panel of each of the File Open and File Save dialogs are different. However, if you’re working in Word 2003, for example, the same sidebar appears in Excel 2003.
If you have lots of links in the sidebar – I have around 20 – right-click the panel and select Small Icons to make the links smaller so that everything fits better.
Tuesday, November 18th, 2008
Saving paper is easy with Microsoft Office. Here are some cool ways you can save paper when printing (other than the very obvious option to not print in the first place!)
1 Use Shrink to Fit in Word’s Print Preview to size your document down so it takes one less page.
2 Use the options in Excel’s File > Page Setup dialog to select the number of pages wide and tall to print onto – the document will be scaled down to fit.
3 Use Print Preview – every time – before you print. Check your document thoroughly to make sure there aren’t errors in layout or design before you waste paper finding out!
4 Print in duplex (double sided) where possible and if your printer allows.
5 For documents which you’re printing for archive or reference purposes – print 2 up so you get 2 “document” pages on each sheet of paper – if you duplex this you’ll get 4 pages per sheet.
6 Use Internet Explorer’s Send to OneNote 2007 feature to save web pages for reference to a OneNote notebook rather than printing them.
8 Instead of printing Slides in PowerPoint one per page, use the Handout option to print up to 9 per page.
9 Email documents to other users when working on documents as a team. Use the Track Changes feature in Word to mark up changes so they can be seen, shared and evaluated.
10 To print only part of a worksheet, select the area to print and choose File > Print > Selection.
Wednesday, October 22nd, 2008
Planning a trip? Researching a topic? almost anything you are doing, OneNote should have a place in your toolkit. With OneNote 2007 installed you have better than ever integration between OneNote and, for example, Internet Explorer or Word. In Internet Explorer, find a site you like and which you want to keep the information from and you can. With the page visible, choose Tools > Send to OneNote and Internet Explorer sends the page contents and the URL to a new page in a OneNote notebook. It’s all stored ready for you to use when the time comes you need the information.
It’s fast and smart.
In Word 2007 you have a OneNote printer. Open a document and choose to Print it but this time, select the Send to OneNote 2007 printer and the document gets sent automatically to OneNote.
It’s a great place to store all your stuff, check it out. It was, from its inception a fantastic piece of programming and a leader in its field. It rocks even more in the new 2007 version.