Tuesday, January 10th, 2012
Pattern fills for your Excel 2007 charts
In Excel 2003 and now in Excel 2010 , there are pattern fills which you can use to fill chart bars so your charts print just great in black and white.
Unfortunately the same feature was removed from Excel 2007 – wtf? I have no clue why but it was but it has to be a very silly thing to have done.
If you are using Excel 2007 and you need to use pattern fills with a chart you are out of luck – well not really – you just need to read the rest of this tip because I can tell you how to put the fills back into Excel 2007.
To begin, download this handy add-in: http://officeblogs.net/excel/PatternUI.zip
The zip file contains a single file patternUI.xlam which you need to extract and place somewhere you will find it easily and where it won’t get deleted by accident. You could make an Excel add-ins folder for it, for example.
Once you’ve done this, open Excel 2007 and choose the Office button > Excel Options > Add-ins and from the Manage dropdown list, select Excel Add-ins and click Go. This opens the old Add-ins dialog from earlier versions of Excel. Click Browse and locate the .xlam file that you just unzipped and placed somewhere safe. Select it and click Ok. Ensure that the PatternUI option appears in the Add-ins available list and that it is checked and click Ok.
Now create an Excel chart. Once you have you chart, click on the data series to fill with a pattern – if you have a single series plotted then select just one of the columns at a time. Select the Chart Tools > Format tab and notice that you now have an option called Patterns available. Click the Patterns option and select a pattern to apply to the currently selected chart series or column. Click on each series or column in turn and apply a pattern to it. When you are done, you can print your chart as usual.
Installed add-ins are managed automatically by Excel so you will find that the add-in will still be there and accessible next time you use Excel.
If you are using Excel 2010 you don’t need this add-in as the pattern fills are back where they should have been all the time.
Labels: add-in, black and white, charts, download, Excel 2007, Excel 2010, free, pattern fills
Wednesday, December 28th, 2011
Quickly access Excel 2010 formula requirements
Ok.. so you want to use an Excel formula and you know it is, say, SUMIF that you want to use, but what data does it need and where?
Before you go searching through help or cranking up your browser, let Excel do the work for you. Just type =SUMIF in a cell and press Control + Shift + A and Excel will give you the list of data required. It’s dead simple and it saves heaps of time.
Labels: arguments, Excel 2010, formula, function, quick find
Thursday, November 17th, 2011
Save space – Compress your images in Word
A reader just asked if they added 50 images to a Word file would that blow out the file size when they send it to their colleagues. Answer: Yep! big time.
Most specially if you use your your beaut iPhone to capture the images, or a good compact camera or if you just insert big pictures. You see, unless you do something those big pictures will be inserted in the file and included in it when you save it – you can end up with a monster Word file (think 5Mb x 50!)
The solution to the problem is to compress the images. To compress all the images in your Word 2010/2007 file so that they take less room, click on any image and choose Picture Tools > Format tab. Locate the Compress Pictures button and deselect Apply only to this Picture so all images will be compressed.
If there is an Options button click it to see the sizing options (this appears in Word 2007 but not in Word 2010). Then choose the Target output – typically Screen or Print are good options as they will view well and print just fine. Check the Delete Cropped Areas of Pictures checkbox so cropped data won’t be saved. Click Ok. Then save the document.
This compression feature will reduce your file to a better size for sharing without compromising the quality of the document. And the changes affect only the images in the document not the originals on your disk.
Labels: compress images, Picture, reduce file size, reduce size of files, shrink, squash, Word 2003, Word 2007, Word 2010
Tuesday, October 18th, 2011
How to set up your PC and your iPad for the free iWork and iCloud
If you have an iPad and if you have upgraded to the new iOS 5 then you can take advantage of the free iCloud and iWork tools for synching files and storing them in the Cloud. iCloud allows you to synch not only photos but also data such as files across your devices. So you can use this new technology to share Microsoft Office documents with Numbers, Keynote and Pages on your iPad. And you do this on your PC using a browser and by passing iTunes – something I for one am really glad about.
So, if you’re using a Windows PC, you can automatically access documents that have been sent from the iPad to the iCloud without needing to plug your iPad into your computer or use iTunes. It is a long over due technology and it provides a more professional approach to the task of file exchange – not to mention it will solve the problem that some users experience where their network administrators block iTunes.
Getting this all configured is simple but it is far from intuitive. Nowhere could I find step by step instructions so I had to piece this stuff together bit by bit. Hence this post – it will explain all you need to do. From start to finish, I’d allow about 1.5 hours – if you finish early that’s a bonus but none of this stuff is totally trivial so allow time to get it all done.
Start by updating iTunes to 10.5 on your PC. If you haven’t done this – you do this from inside iTunes. Then, connect your iPad to your PC and update the iPad to iOS 5 – this might take some time depending on how much data you have on your iPad as everything will be backed up – then reinstalled once the operating system is updated.
While iTunes is updating and iOS 5 is being configured, you can – on your PC – visit the Apple site at http://support.apple.com/kb/DL1455 and download and install the iCloud control panel for Windows.
Back on your iPad when everything is running again, go into Settings and create your iCloud account.
On the iPad, in Settings > iCloud you can set up syncing for email and other items such as your Photo Stream and Documents & Data. For Documents & Data you have a choice of using your cellular data plan to upload or Wi-Fi only – for your Photo Stream you can only synch this using Wi-Fi.
If you have Numbers, Pages and/or Keynote installed, in Settings select these each in turn and enable the Use iCloud option – you need to turn this On before any of these apps will access iCloud.
iCloud can be accessed on your PC by opening the system tray and clicking on its icon – if it isn’t there, choose the Start menu and type iCloud. Use the settings to configure how and what you want your iCloud and iPad to share. For example, click Photo Stream and configure a folder for images to upload to the iPad and one to use to download into. On the PC you can select whether email, contacts and Calendars & Tasks should be shared with Outlook. I suggest you backup your Outlook pst file before you even consider enabling this!
So far you have everything working except your documents. For this, confusingly enough, you head back to your PC and crank up your browser and visit www.icloud.com. Sign in using your Apple ID. If prompted you should download and install the browser plug-in.
Here you will find links for Keynote, Numbers and Pages. Click on one of them to view the documents synched from your iPad – first time you do this it might take a while as the synching takes place.
In future, every time you create a document on your iPad or edit one it will be synched automatically and will appear in this list.
Although it is not obvious that you can do this, you can also drag and drop a document, spreadsheet or PowerPoint document into the appropriate panel and it will be synched with your iPad. You can double click a document in the list to download it to the appropriate application on your computer – here I’ve chosen a Pages document which will open in Word.
You can also now share documents with others who have Apple IDs using iWork. So, on your iPad, open a document, spreadsheet or presentation and click Share and Print (it looks like a spanner) and choose Share via iWork.com. Type the email address of the person you want to share it with and they will receive an invitation to view the shared document:
Labels: cloud sharing files, excel cloud, file sharing, free apps, free iwork, icloud, ios 5, ios5, itunes 10.5, iwork, keynote, MSWord cloud, numbers, pages, powerpoint cloud
Monday, October 17th, 2011
Shape images and Clip Art in Word 2010
There is a healthy range of free clip art available from Microsoft and it includes some photos including content from iStockPhoto.com. However, the process of getting them into your document any other way than by choosing Insert > Clip Art is not always obvious.
Here’s how to add a clip art image to a circle shape:
Choose Insert > Shape and select the Oval. Hold Shift as you draw to create a circle on the screen. If you choose Drawing Tools > Format > Shape Fill you get the option of applying a picture to the shape but not clip art.
Instead, right click the shape and choose Format Shape to get access to the new to Word 2010 – Format Shape dialog. Choose Fill > Picture or Texture Fill and click the Clip Art button.
Browse or search for an image. You could also have placed a Clip Art image into your document using Insert > Clip Art and then selected it and cut it to the Clipboard. Here in this dialog you can choose Clipboard to add the image from the clipboard – in short you have more options here for using image than you have using the Shape Fill list.
If the image is skewed out of shape – and it will be if it is a portrait or landscape image inside a circle which is pretty much a square with the corners cut off – you can adjust it.
Select Crop and, for a landscape orientation image inside a circle, increase the Picture Position Width value. For a portrait orientation image inside a circle, increase the Picture Position: Height value.
Then adjust the Offset X or Offset Y values, if desired, to control which portion of the image shows up inside the circle.
Labels: clip art, images, Microsoft Word 2010, photos, pictures in shapes, shapes, Word 2010
Friday, October 14th, 2011
Reformatting pasted text in Word
I recently grabbed some text from an email that I’d sent to someone. The text was in numbered point form and I wanted to add some more items inside the list of points. This is where everything started to go hair-raisingly wrong.
It seemed as if my list was no longer reformatting correctly and the numbers were everywhere. Worse still, that old standby of copying and pasting formats using the FormatPainter didn’t fix the problem.
By selecting the Show/Hide¶ icon on the Home tab in Word I immediately saw the problem. Instead of paragraph marks at the end of each paragraph there were bent arrows indicating a manual line break. I didn’t put them there but the email software had.
To get my list back to something that I could work with, I needed to quickly replace the manual line breaks with paragraph breaks.
If you open the Find and Replace dialog and click in the Find What: box you immediately see the problem – how do I tell it what a manual line break is and what a paragraph break is? The solution is to click the More button to show more options. Then click Special to get access to the Manual Line Break character. It’s actually a carat and what looks like a pipe symbol but it’s easiest to get this by clicking this by clicking Manual Line Break because the pipe symbol on the keyboard isn’t the right one – go figure!
Then click in the Replace With box and select the Paragraph Mark - this one you can type manually as it is carat + p (^p) but since you’re there why not click to insert it instead. Now click Less to return the dialog to what it looked like before and click Replace All to turn all the manual line breaks back to regular paragraph marks. Now my numbering worked just fine.
Labels: find and replace, manual line break, paragraph marker, special characters, Word
Wednesday, October 12th, 2011
Excel – get the day or month name from a date
Often you will want to extract a month or day of the week from an Excel date. This is extraordinarily easy to do using the text function.
To get the name of the day of the week from a date in, for example, cell A1 type this into another cell:
= TEXT(A1,”dddd”)
This will give you the full day name spelled out such as Monday or Tuesday.
If you want a three character name use:
= TEXT(A1,”ddd”)
The same basic formula can be used to get the month of the year from a date. Use this to get the month name spelled out in full:
= TEXT(A1,”mmmm”)
Use this to get the month of the year spelled out in three characters:
= TEXT(A1,”mmm”)
and this for a single letter month:
= TEXT(A1,”mmmmm”)
This formula can be easily constructed and copied down a column of dates to extract just the information you want very quickly and easily.
The Excel help file has some information about the different formats you can use to extract data using the TEXT function.
Labels: day of the week from a date, Excel 2007, Excel 2010, functions, month name from a date
Thursday, October 6th, 2011
Switch between open documents in Excel and Word
Hmmm … I am fussy, I want my cake and I want to eat it too!
I want to have a clean task bar so I don’t want to see lots of files lined up there so I love Windows 7 and its clean task bar. But I find the new panel that opens when I right click an icon on the task bar to be just a little bit too free with information. I really want it to show me a list of currently open files – not everything that I have open or have recently opened. Actually I could live with the information it gives me if I didn’t have to actually use it to switch windows.
So, problem is… how can I switch between open documents in Excel or Word, for example, without having to use the Windows task bar? Solution is to use the Switch Windows button. I add it to the QAT (Quick Access Toolbar) and it totally makes sense to me.
In Excel or Word, click the Customize Quick Access Toolbar button and choose More Commands. From the list which currently shows Popular Commands choose All Commands and scroll to find the Switch Windows button and click Add.
Now it is on the QAT and it will show you all your open files and you can use it to switch between them by just clicking on the one to go to. Repeat the process for both Excel and Word and you’ll be happy – at least until something else bugs you!
Labels: Excel 2007, Excel 2010, filenames on task bar, Switch between open documents, switch between open worksheets, Windows 7, Word 2007, Word 2010, workbooks
Monday, October 3rd, 2011
Go To a cell in a formula in Excel
Consider this scenario – you have a cell which contains a link to data in another cell on another sheet. The link might be the only thing in the cell or it might be a link in a formula which contains references to data in lots of other cells too.
If you want to go to a particular referenced cell you could read off the cell details – its sheet name and its cell reference and navigate there yourself or you could get smart and have Excel do the work.
To do this, click in the cell containing the reference and choose Formulas > Trace Precedents. When you do this you will see a small sheet icon and an arrow with a black arrow head pointing at the cell. Hold your mouse cursor over the arrow until the mouse cursor turns into a hollow white arrow. Double click and the Go To dialog will open. In it will be references to all the cells in the formula. Click the reference you are interested in going to and the cell reference will be highlighted – click Ok and Excel will take you direct to that cell.
If you have both workbooks open the same process will work to take you to a cell in another workbook if it is referred to in a formula in the current workbook.
Labels: Excel 2007, Excel 2010, go to a cell, go to a cell on another sheet, go to a cell on another workbook, trace precedents
Thursday, September 29th, 2011
iPad 2 – Delete multiple emails at a time
There are a few ways to delete multiple emails at a time on the iPad, none of them particularly intuitive and there is no big Select All option so it has to be done individually:
Start by turning the iPad into landscape mode and click the inbox. If you click the Edit button (top left) then you can select multiple emails to delete very quickly. Then click the Delete button which appears bottom left.
To make sure I don’t have too many emails to deal with at a time, this is how I have my email account configured:
First of all, because I deal with email on my desktop, I don’t want the iPad to delete emails from the server so that option, in Settings > Email is set to Never. To find it, click your account name and click Advanced.
Then I set the Remove option in this same dialog to After one day.
All these settings and actions make checking emails on the iPad a fairly simple process – but I, like you, wish there were smarter Select All and Delete All options.
Labels: all emails, delete all, delete multiple emails, delete multiple emails at once, ipad 2, select multiple emails
Tuesday, September 27th, 2011
Timeline in Microsoft Word 2010
The new SmartArt feature in Word 2007 and 2010 helps you create timelines very easily:
Step 1
Choose Insert -> SmartArt -> Process and select one of the process options such as Basic Timeline and click Ok. Type the text into the textboxes in the SmartArt object. Alternately, click the arrows at the far left of the object and add text via the dialog.
Step 2
To format the timeline SmartArt, select the object and choose Format -> Design on the Ribbon and then select one of the SmartArt Styles. Click Change Colors to alter the colours used in your SmartArt object.
Step 3
From the Shape Effects list you can customise an effect such as reflection or shadow for the art. In addition, as the look of a SmartArt object is controlled by the document theme you can choose Page Layout -> Themes and select an alternate theme for your document.
Labels: how to, Microsoft Word, smart art, SmartArt, timeline, Tutorial, Word 2007, Word 2010
Saturday, September 24th, 2011
iPad 2 -Blog to a self hosted WordPress blog
Ok, the challenge is how do you blog from your iPad to a self-hosted WordPress blog and how do you do it with images from your iPad?
Well, the actual blogging part is simplicity itself. Just crank up Safari and log in to your WordPress dashboard. From there you can do almost anything you want – except the one thing you really want to do – add images from your iPad to your post!
Ok. So, Safari is a great place to work in but we have to solve the image problem and for that we use the WordPress app for the iPad – good news is that it is free so download it from the store and get it started.
Then in the WordPress app you can add your blog – you just type its URL including the /wordpress/wp-admin bit and type your ID and password. You only do this once.
Here you will likely encounter a problem that your blog isn’t configured to allow this type of editing. You will get this message that XML-RPC services are disabled.
What you need to do is to go to your computer and log in to your blog using an admin account – if it is your blog, then chances are you are an admin anyway. Then go to the Settings group on the left and click Writing. Then, locate the Remote Publishing area and enable the “WordPress, Moveable type, MetaWeblog and Blogger XML-RPC publishing protocols”. Once you have done this you’re ok to go and your blog will be added to the WordPress app. If you have multiple blogs you can add more than one, which is smart.
Now, the big reason why we’re using the WordPress iPad app is that it lets us get images up to our WordPress blog so you don’t have to write your content in the app but you do have to upload your images using it so you can get them from the Gallery later on.
So, first of all crop your images and rotate them – I find it easier to do this on the iPad and you can use any program you like – I use Photoshop Express but you can use anything.
Now click on your blog name to get access to your WordPress site on your server. You will see some links at the foot of the screen which take you to the various parts of your site – click the Posts button to view your posts and click the little Add button at the top of the panel on the left to add a new post. Here you type the post content.
To add images, click Done and then find the little icon in the bottom right that looks like a landscape image – click it and add images from your Camera Roll.
Regardless of where you are when you do this all the images go one after the other into the post… this is the sucky part but it really is a small issue – at least they are there!
Before you leave the WordPress iPad app, click Done and click the Settings button (bottom Left) and set a Schedule for the post – I make this a day or more but at least an hour so ahead of time so I have enough time to fix the image issue back in WordPress in Safari before everything goes live.
Now, still in the WordPress app, go ahead and Upload and Update everything. Then go back to Safari and log in to your blog.
By the way, I haven’t mentioned it yet, but it really helps to have one of those bluetooth keyboards so you have arrow keys and you can use shortcuts to copy and paste and select stuff. It beats working with your fingers on the screen – if you’re serious about blogging on the iPad you really need one. I use the Zagg one which I really like.
Back in WordPress on Safari I just delete all the code which has been added for the images as they are seldom where I want them to be. Now I move into position in the post and add the images using the regular WordPress Add Image button – the pictures are all in the Gallery – thanks to the WordPress app.
In WordPress in Safari I can do things like add Captions and descriptions and scale any image that need resizing.
On a scale of 1 to 10 it would be easier to be able to do everything from inside Safari or inside the WordPress app but I wouldn’t call this difficult or unnecessarily cumbersome. I can live with the slight workaround for the sake of being able to blog with images from the iPad in WordPress to a self hosted blog.
I’d rate it around a 7/10 ease of use and functionality and I love it. In fact this is the last big issue I’ve had with the iPad and not being able to blog to my WordPress blogs would have been a deal breaker for me. I need to be able to blog on the road and I want to do this with images – in particular as I am doing an apple a day blog over at my design site and looking at heaps of cool iPad apps so not being able to include images would be horrible!
Labels: apps, blogging with the ipad, how to blog with the ipad, ipad 2, self hosted, upload images to Wordpress blog, Wordpress app, Wordpress self-hosted blog
Thursday, September 22nd, 2011
Warn of tracked changes in Word 2010
Microsoft Word 2010 has some great security settings but I am continually disappointed that Microsoft hides them away so they are so difficult to find.
One of these settings is crucial to enable so you don’t accidentally send a document out to a client with, for example, a comment in it saying what a idiot your client is. The revelation that you think they are an idiot might be news to them – and you’re not going to look too smart yourself.
The culprit is tracked changes. It is all to easy to have Tracked Changes enabled but to have your document show only the final version of the text. Behind the scenes every insertion and deletion is being stored in the document even if you aren’t seeing it. However the information will be accessible to anyone viewing that document – not a smart idea!
If you don’t think this is a big deal you might be interested to know that lots of smart people have been caught out by it, including Microsoft itself. This blog post showcases stories of businesses and government caught out by tracked changes.
To stop this happening to you, you can do a few things but I like the method that you set once and, from there on, Word will do the work for you. This involves having Word tell you if a document you are about to print or save has tracked changes in it. Ignore the warning at your own risk!
To configure this, choose File > Options > Trust Center and click Trust Center Settings then Privacy Options. Enable the “Warn before printing, saving or sending a file that contains tracked changes or comments” checkbox.
Now, in future, Word will show a warning if a document contains tracked changes even if they are hidden from sight.
It should be on by default and it should be easier to find… but there it is… and don’t say I didn’t warn you about it.
Labels: document security, Microsoft Word 2010, tracked changes
Tuesday, September 20th, 2011
Edit Excel files on the go
If you have access to a PC and an internet connection then your options for working with an Excel spreadsheet away from your desk are good – thanks to the Microsoft Office WebApps. Even though many of the advanced Excel tools you are used to using offline won’t be accessible online they won’t be destroyed by opening a file in the Excel WebApp either. You can view charts and filtered table data and features such as the new Slicers in an Excel 2010 PivotTable can be used to work with the data.
Of all the cloud based apps, including Google Docs, the Microsoft WebApps are your best option for working with Excel spreadsheets in the cloud when you are away from your desk and the apps are free.
You can sign up for a free SkyDrive account at skydrive.live.com and that’s where you get access to the WebApps which include Excel, Word, PowerPoint and OneNote. I selected to upload this file then view it in the Excel viewer. To work on it beyond selecting options in the Slicers I can click Edit in Browser to open the file in the free cut down version of Excel online.
One benefit to using SkyDrive is that you can upload files from your local computer to SkyDrive where they are stored for you. You can work on the files online and later download them to your computer when you want to work on them there.
Labels: cloud Excel, Editing Excel files online, Excel 2010, Excel viewer, Excel WebApps, free Excel
Wednesday, July 6th, 2011
Dreamweaver – Properties inspector missing buttons
If you’re using Dreamweaver on a large monitor, you may encounter a difficulty with the Properties panel (Inspector) not showing all the icons you expect to see.
For example, in the image below the Quick Tag Editor, Help button and the Expand button are all missing from the Properties panel.
Only when the Dreamweaver window is expanded to a much larger size do these buttons appear.
So, unless you know what is happening you will find that key tools for working in Dreamweaver are missing. If you are creating an image map, for example, not being able to see to click the Expand button means all your Map tools are hidden.
So, if you find the Expand button is missing on your Dreamweaver Properties panel you can do one of three things. Either make the Dreamweaver window very large so that you can see the Expand/Collapse icon or drag the Properties panel free of the Dreamweaver screen so it is no longer docked. As soon as it’s undocked the buttons appear again. Or… and this is a cool trick, double click anywhere in an empty space in the Properties panel and the hidden buttons appear.
Hiding buttons is REALLY ANNOYING behavior particularly when you know there’s an icon or feature that’s supposed to be on the Properties panel and it’s simply not appearing when it should.
Labels: Dreamweaver, missing buttons, properties inspector, properties panel