Monday, August 3rd, 2015
Copy formulas without the cells updating to their new location
I have had an ongoing problem with a worksheet I use to track data by date. Each month I need to copy a range of cells to start the data comparison for the next month. Problem is that you can’t copy cells in Excel without the cell references updating to their new location. And, guess what? That’s exactly what I need to do – copy cells, paste them into their new location without the formulas changing – at all.
One way you can do this is to copy each formula manually by double clicking the cell, copy the text in the Formula Bar, press Esc and then go to the cell to copy it to and paste it in. It works, but you can only do it one cell at a time. I have ten or more cells so that’s just a plain waste of time.
I could write a macro – and in future, I will – but there is a super neat solution that I am using for now.
First of all, press Control + ` (grave) to show formulas in cells (or choose to Show Formulas from the ribbon).
Next select and copy the cells containing the formulas.
Paste these as text into a document. I use Paste Special > Unformatted Text in Word but you can paste into any text editor.
Return to Excel and hide the formulas using Control + ` again.
Now select the text of the formulas from your document and copy it, then paste it into the appropriate cells in the worksheet. The Data Parse feature in Excel will automatically split the text containing the formulas and paste one formula into each of the cells.
It might sound complex, but you can do this really quickly, and it’s accurate and effective.
Friday, July 31st, 2015
Move to the top or bottom of a Word Document on a Mac
From a file labelled stupid commands comes this one for getting to the top or bottom of a Word document on the Mac. You see simple commands like Page Up and Page Down which work on a PC fail spectacularly on a Mac so most hapless users just use the arrow key (a lot) or scroll bars to get around a Word document.
Smart users know there is a key combination that will do it, but it uses the Left Arrow and Right Arrow keys – I kid you not! How stupid is that? However, since it is useful (albeit stupid), I’ll tell you how. To get to the top of the document use Fn + Command + Left Arrow and to get to the bottom of the document use Fn + Command + Right Arrow.
So, now you know and you can move faster around your documents. Just try explaining that key combination to a friend and hold your tongue as they look at you like you’re nuts.
Friday, May 29th, 2015
Learn how to quickly make a printable list of files in a folder (and how to import it into Excel)
Sometimes I need to get a list of files or folders inside another folder in Windows. I like to either print the results or take them to Excel to work on them. However, it isn’t self evident how to do this – there’s no print command in Windows Explorer that can do it for you. However, once you know how to do it, it’s easy to do.
Start by navigating to the folder whose contents you want to print. You want to have this folder visible in the right hand pane in Windows Explorer. Hold the Shift key as you right click this folder. A shortcut menu will appear, from it choose the Open Command Window here option.
This opens a MSDOS window inside the folder.
Now type this DOS command at the prompt and press Enter:
dir > filelist.txt
The name filelist.txt can really be any file name of your choice, I just like to use the .txt extension since its contents are plain text.
When the prompt reappears close the window.
If you now look inside this folder you will see a text file called filelist.txt. You can now print it by right clicking it and choose Print or double click to open it in Notepad and print it from there.
You can also open it in Excel and convert it to columns of text using the Text Import Wizard. The file is a fixed width file so it converts pretty easily into columns of text – you simply need to drag the lines in the preview to mark out how the text will convert to columns.
Then, when you are done, click Finish and you have the data in Excel – save it as an .xlsx file and you’re good to go.
Wednesday, April 15th, 2015
Find the Mac keyboard icons for the Control & Command keys
I had a problem today in that I was writing about the Mac but using a PC. I needed to type the character for the Command key on the Mac keyboard which, as you might know, is this cute twirly shape. But since I was working on a PC I didn’t have fonts which contained the character. After an hour or so of research, I found the character.
As luck would have it, it was where I should have looked to begin with, in my Wingdings font. To get it, in any document or any application that supports fonts, click to use the Wingdings font and type the lowercase z – voila – the Command key character.
So far, so good. But what about the other keys, like the Apple key and the Alt key on older keyboards? In the interests of helping you get everything you need in one post, if not in one font, here’s how to get hold of the characters for the Apple key and the Alt key:
Turns out the Alt key symbol isn’t as easy to find as it might be. Probably the quickest way to locate it is to download the free font Virtue from the ScooterGraphics site.
Once installed the font contains a range of characters including the Mac Alt key symbol and the Apple icon. To get the Alt key select the Virtue font and type Alt + 0221 on the keyboard. The Apple symbol is a little more difficult as it is a hex value so, switch to Virtue font, type F000 into a Word document and immediately (don’t press space) press Alt + X. This little bit of keyboard magic creates the little Apple logo for you. If you need it elsewhere, make it in Word and copy and paste it to the application you need to use it in.
Thursday, April 2nd, 2015
Got a Winmail.dat file? Open it the easy way!
Winmail.dat is confusion in a very small package. It is a file attachment, usually from someone who uses Microsoft Outlook. They send you an attachment to an email and Outlook bundles it up in this little file that no other program can open. Handy isn’t it?
I am the kind of person who hates having to go back to someone and ask them to fix a problem however stupid it is. So, instead of sending the winmail2.dat file back where it came from, I sorted out how to open it.
Here’s the simple solution – head over to the site http://www.winmaildat.com and click Choose File to upload your winmail.dat file. Click Send File to send your file to the server which will then unlock the magic for you.
You will see a set of links for the things that were in the winmail.dat file so you can click to download those that you want.
Once you are done, you can click Delete to delete your files from the server. If you don’t – they are removed automatically after 2 hours.
The site is simple, it works and you don’t have to wait for someone to fix their Outlook so it stops doing this.
Wednesday, April 1st, 2015
Image credit © Kaspars Grinvalds, Shutterstock Inc
It used to be that, aside from making calls, we could do little more with our phones other than using the built-in calculator function. Needless to say, things have changed quite a bit. Ever since the advent of smartphones, we’ve seen apps for just about any function that you can imagine. Scan through the iTunes App or Google Play Store on any given day and you’ll see dozens of brand new apps and more are developed and released every single day. With millions of apps to choose from, it makes finding one that’s useful like finding the proverbial needle in a haystack.
At the same time, productivity continues to be a daily struggle. We attribute some level of our unproductivity to procrastination and distractions, which — perhaps ironically — seem inevitable as our smartphones keep us connected and informed of what’s happening on social media, on the news, and at places where we cannot physically be. It seems natural, then, that we would turn to our smartphones for help, hoping that there were some way these devices that so often prevent us from attending to important tasks would be able to help us complete them.
Fortunately, there are a variety of tools that are meant to make us more productive, responsible members of society, and we can access them right on our smartphones. Here are some apps that will help improve your personal productivity.
It’s likely that you’ve at least heard of Wunderlist already, but if you haven’t, prepare to meet your new best friend. As the name implies, it’s one of the multitude of to-do list-making apps available for smartphones, but Wunderlist stands out for several reasons. First, Wunderlist isn’t just an app; while it’s available for free on Android and iOS devices, it’s also accessible on the web. This means that once you create and sign into your account, you can create and edit your to-do lists on one device, then access and continue them later on another. You can also create multiple lists, sorting tasks by their subject or some other label of your choosing.
Wunderlist via Google Play Store
Wunderlist also supports adding multiple users to a list, which offers great functionality both at work and at home. In the office, this means you could have a to-do list of tasks for a department that anyone in that department can access and work on, then have separate lists for individual people for projects and tasks assigned on an individual basis. At home, this translates as being able to create a master chore list, for example, then creating a grocery list for your spouse and assigning individual tasks to your teenager. Users need only to have an Android or iOS device, the app, and a Wunderlist account to be added to lists or create their own. Users added to a list can even exchange messages with each other right there in the app. As such, Wunderlist is an incredibly powerful app with great potential for not only your personal productivity, but also helping you to keep your employees or family more organized as well.
Do you find it difficult to keep track of your expenses anytime you’re traveling in the name of business? Do you not only lose receipts, but also hours of your time creating incomplete expense reports? Shoeboxed is the app you need because it can do all of the dirty work for you. Available for free on both Android and iOS devices, and offering web access like Wunderlist, Shoeboxed lets you scan receipts using your smartphone’s cameras, then collates and organizes all those business expenses for you. You can also do the same thing with all those business cards that associates were handing you; simply scan them into the app, then let Shoeboxed create a contact list by pulling the info from those business cards.
Shoeboxed via iTunes App Store
When you return from your business trip, Shoeboxed will have a detailed expense report ready for you to send, which you can also do right there in the app. Shoeboxed saves you time, the hassle of trying to hang onto all those receipts, and perhaps most importantly, is a free cloud-based service that you can access on all your connected smart devices. And even though it’s marketed for business trips, Shoeboxed can also be used by the thorough record-keepers among us, by entrepreneurs, and by those who freelance and need to keep track of expenses accrued in the line of duty. Having a detailed record of all business-related expenses will be an incredible relief come tax season.
If you’re not already using Google Docs, you probably will be eventually. Google Docs almost single-handedly brought word processing to the smartphone when probably just five years ago we all would have agree that we’ve never want or need to create and edit documents on a smartphone or tablet. However, Google Docs is a well-made, easy-to-use mobile word processor that can easily import and export files in the most essential document file types, such as Microsoft Word files and PDF files.
Perhaps the most useful feature of Google Docs is its seamless integration with Google’s free cloud storage service, Google Drive. When you open or create a document in Google Docs, it’s automatically saved to your Google Drive while you have the option to save it locally on the device you’re using. Google Docs also auto-saves your work after every change you make, even if that change is as trivial and small as a single letter or punctuation mark. This makes it essentially impossible to lose any unsaved changes, which is a godsend when it comes to word processing — anyone who has ever lost hours of progress after Microsoft Word crashed will agree — and also means the document you’re seeing will always be the most up-to-date version. Since you can share documents with other users to work on a project remotely and simultaneously, it’s good to know that you’re working on the most current version and not spending time making changes that were already made by someone else.
It’s also useful to be able to type up important documents right from your smartphone. You probably won’t want to use your on-screen keyboard to write a term paper, but it’s good to know that if you ever needed to make small changes while on the go, Google Docs certainly gives you the option. On the other hand, you could connect a Bluetooth keyboard while you sit in a coffee shop or library and type longer documents more comfortably. Google Docs is an incredibly capable mobile word processor that’s free for Android and iOS devices, accessible on the web, and proves that it’s not unrealistic to create and edit documents on the go.
For more information, head to Modernize.com.
Post by Guest Blogger Dane O’Leary
Friday, March 27th, 2015
What to do when Excel shows Column 1 not Column A
My Excel has been behaving stupidly lately. Instead of Column letters – A, B & C etc, the columns are numbered 1, 2, 3 and so on.
While I haven’t solved the fundamental problem I do have a short term solution. It all has to do with the Excel options. To change the column numbers back to letters chose File (the Office Button in Excel 2007) and choose Options > Formulas and disable the checkbox for R1C1 Reference Style.
On the Mac click Excel > Preferences > General and deselect the Use R1C1 Reference Style checkbox.
This setting kicks Excel back into the correct mode – much more to my taste!
Of course, if you prefer seeing numbers and not letters all you need to do is to click the checkbox and you are good to go!
Saturday, January 17th, 2015
If your Recent files list disappears from your Mac, here’s how to put it back
Word on my Mac doesn’t have a recently opened files list but Excel does. Turns out the feature was disabled (or perhaps it was never enabled). Whatever the reason, it wasn’t there and I wanted it to be accessible.
Lucky for me it is just a preference setting. Click the Word button, click Preferences and then General. Look for the Track Recently Opened Documents option and enable it. Set the number of documents to track and click Ok. Yeah! Now you can open a recently opened file like you expect to be able to do.
Thursday, October 23rd, 2014
Missing the Calendar Wizard in Word 2013? – no problem, here’s how to get it
Many years ago, Word had a wonderful Calendar Wizard that you could use to make calendar pages in Word. One really neat aspect of this Wizard was (and still is) that you can make calendar pages for any period of time – so you don’t have to wait till someone makes 2015 calendars for you to download – instead you can make your own and you can do it now! Hell you can make calendars for 2020 now if you want to really plan ahead!
In Word 2013 it might at first appear that the Calendar Wizard isn’t available – and it isn’t – that is, until you know how to make it accessible.
To start, download the cab file for the wizard from here: Microsoft Word Calendar Wizard Download for Word 2013.
Alternately, if you have an older version of Word on your computer, search for Calendar Wizard.wiz as you may have it on your computer somewhere already.
If you download the .cab file you’re getting a zipped archive format file which has to be opened. If double clicking it doesn’t launch an unzip program that can handle it, then download Express Zip File Compression as it can handle .cab files easily.
Extract the .wiz file (or copy it) to a folder of your choice or, better still, put it into your custom templates folder C:\Users\‹Username›\Documents\Custom Templates.
If you are doing this, then change its name when you do so – calendar wizard.wiz is (and was) a good name for it. If you found the wiz file on your own drive, make a copy to this folder.
T’hen double click the .wiz file to launch it in Word. If you put it in your Custom Office Templates folder then you can launch it from inside Word 2013 by choosing File > New, click Personal to view your personal templates and click to run it from there.
Tuesday, September 9th, 2014
Need to manage your Microsoft Office templates but can’t find them?
It’s not easy to find the templates folder on your Mac. You might know it is My Templates but a typical search in Finder won’t turn it up.
Luckily help is at hand. Launch Finder and choose Go > Go to Folder. Now paste this folder name into the search box to go to your My Templates folder where you can now see and manage your templates!
~/Library/Application Support/Microsoft/Office/User Templates/My Templates
Wednesday, August 20th, 2014
Great find! A list of good quality Microsoft Office tutorial resources
I’ve recently discovered an enormous list of Microsoft Office tutorials that may be worth checking out. Each piece of Office software has several listed tutorials ranging from beginner to advanced difficulty and general to specific usage.
Best of all, each tutorial has a brief summary of its contents so you can quickly decide if its new and interesting information. Hopefully every Office user will find something of use. You can visit the page here.
Thursday, July 31st, 2014
Help! My Excel formulas aren’t updating – how can I make Excel recalculate everything?
I have been working on a very large and very complex worksheet today. It uses a lot of custom functions to manage the calculations and as I was working on the functions I was plagued with a problem. You see Excel refused to update the cells that contained formulas based on my functions. It meant I was continually thinking that the problem was with my code. Sometimes the code was a problem but when formulas don’t update you just don’t get any visual feedback as to what is going on. I checked the Options to make sure that Calculation was set to be done automatically and it was.
Turns out the problem is a known one and can happen in Excel. How horrible is that? Excel won’t recalculate? Wow! That’s like Word not spell checking or not letting you type the letter e!
Anyway the solution is to press Control + Alt + F9 to force every formula in the worksheet to recalculate. When you do this, it might take a while for it all to recalculate but at least the data will now be accurate!
Thursday, July 24th, 2014
You can quickly insert text from a file into your document using Word’s Text from File command. This used to be as simple as choosing Insert > File but the command got hidden behind an additional layer of the UI in Word 2007, making it almost entirely invisible to most users.
To insert text from a file in all recent versions of Word, open the Insert tab in the ribbon and, in the Text settings find the Object button. Click the small arrow to the right of the Object button, and choose Text from File…. Choose the file you want and click Insert. The file type selection here defaults to Word documents only, so if you wish to insert a .txt file or other non-Word document, you’ll have to change the file type to your desired type or All Files.
If you insert a Word document it will include all elements of the document such as images and special formatting. If you wish to include the file’s header or footer (if it has one), you should insert the file into a new section of your document. Be careful when inserting multiple files with different formats, since text from one file may take on another file’s format if the inserts aren’t separated properly.
Monday, June 16th, 2014
Yikes! Just how do you resize a photo inside a PowerPoint or Word SmartArt placeholder
I got an email from a reader this morning. He has a PowerPoint slide (but it could as easily be a Word document or an Excel worksheet) and he wants to size a photo inside a placeholder. You see he was making an organization chart and he was dealing with lots of different head shots – all photographed differently. He wanted to make the faces the same relative size inside the placeholders – but to do this he had to get access to the photos inside the placeholders.
You see that’s the problem, every time you right click the placeholder and choose Size and Position you’re affecting the placeholder not the thing inside it! The solution is to use the Crop tool – so click on the placeholder and choose Picture Tools > Format tab and click the Crop tool.
Now you get handles around your photo and you can drag the handles to resize the image and you can move it to change its position inside the placeholder. When you’re done, click Crop again to finish. Easy when you know how.