Monday, August 3rd, 2015

Copy Excel Formulas Without Updating References

Screen Shot 2015 08 01 at 7.12.54 AM Copy Excel Formulas Without Updating References

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.

Screen Shot 2015 08 01 at 7.12.25 AM Copy Excel Formulas Without Updating References

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

Left Arrow to Get to the Bottom of a Word Document – Mac Only Stupidity

Screen Shot 2015 07 31 at 9.31.15 AM Left Arrow to Get to the Bottom of a Word Document   Mac Only Stupidity

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

Print a List of Files in a Windows Folder

print a file list from windows explorer opener Print a List of Files in a Windows Folder

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.

print a file list from windows explorer 1 Print a List of Files in a Windows Folder

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.

print a file list from windows explorer 2 Print a List of Files in a Windows Folder

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.

print a file list from windows explorer 3 Print a List of Files in a Windows Folder

 

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.

print a file list from windows explorer 4 Print a List of Files in a Windows Folder
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.

 

print a file list from windows explorer 5 Print a List of Files in a Windows Folder

Wednesday, April 15th, 2015

Add Mac Keyboard Icons to Word documents (and other things)

type mac alt and apple key symbol on a pc 3 Add Mac Keyboard Icons to Word documents (and other things)

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.
type mac command key symbol on a pc 1 Add Mac Keyboard Icons to Word documents (and other things)

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.

type mac alt and apple key symbol on a pc 2 Add Mac Keyboard Icons to Word documents (and other things)

Thursday, April 2nd, 2015

How to Open Winmail.dat files

open winmaildat files easily 2 How to Open Winmail.dat files

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.

open winmaildat files easily 1 How to Open Winmail.dat files
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

Must-Have Apps for Improving Your Personal Productivity

productivity on smartphone travelling personal productivity apps Must Have Apps for Improving Your Personal Productivity

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.

Wunderlist

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 productivity apps Must Have Apps for Improving Your Personal Productivity

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.

 

Shoeboxed

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.

shoeboxedt productivity apps Must Have Apps for Improving Your Personal Productivity

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.

 

Google Docs

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.

GoogleDocst productivity apps Must Have Apps for Improving Your Personal Productivity

Via Google Play Store

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

Help! Excel Shows Columns as Numbers not Letters

 

change column numbers to letters in exce examplel Help! Excel Shows Columns as Numbers not Letters

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.

change column numbers to letters in excel1 Help! Excel Shows Columns as Numbers not Letters

On the Mac click Excel > Preferences > General and deselect the Use R1C1 Reference Style checkbox.

Screen Shot 2015 03 25 at 7.03.00 AM Help! Excel Shows Columns as Numbers not Letters

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

Open Recent Files Missing from Word (Mac)

Screen Shot 2015 01 17 at 5.02.14 PM Open Recent Files Missing from Word (Mac)

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

Calendar Wizard in Word 2013 – Yes!

Install Microsoft calendar wizard for Word 2013 opener Calendar Wizard in Word 2013   Yes!

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.

Install Microsoft calendar wizard for Word 2013 1 Calendar Wizard in Word 2013   Yes!

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.

Install Microsoft calendar wizard for Word 2013 2 Calendar Wizard in Word 2013   Yes!

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.

Install Microsoft calendar wizard for Word 2013 3 Calendar Wizard in Word 2013   Yes!

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.

Install Microsoft calendar wizard for Word 2013 4 Calendar Wizard in Word 2013   Yes!

 

Tuesday, September 9th, 2014

Find Your templates in Microsoft Office – Mac

microsoft office template location on a mac Find Your templates in Microsoft Office   Mac

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

A Great Microsoft Office Tutorial Resource

asgags A Great Microsoft Office Tutorial Resource

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

Force Excel to Recalculate a Worksheet

excel force recalculation of formulas worksheet ctrl alt f9 Force Excel to Recalculate a Worksheet

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.

excel force recalculation of formulas worksheet Force Excel to Recalculate a Worksheet

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

Insert Text from a File into Word

pic1 Insert Text from a File into Word

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.

pic2 Insert Text from a File into Word

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

Resize and Position Photos in SmartArt Placeholders

powerpoint size and position photo in smartart placeholder1 Resize and Position Photos in SmartArt Placeholders

 

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.

powerpoint size and position photo in smartart placeholder2 Resize and Position Photos in SmartArt Placeholders

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.

Monday, June 9th, 2014

Find the Recycle bin in Windows

recyclebin Find the Recycle bin in Windows

Help! My Recycle Bin just disappeared!

This is a major annoyance – your Recycle bin just ups and disappears from your Desktop. Well it’s easy to get back – when you know how.

Click the Start button and type Desktop Icons – wait as the search results appear. Click Show or hide common icons on the desktop to open the Desktop Icon Settings dialog.

Here you will find icons for common tools including the Recycle Bin. Click its checkbox to select it and click Ok to return it to the desktop. Notice too that you can add icons for My Computer, Your User Account files, Network and the Control Panel to your desktop  by simply clicking the appropriate checkboxes.

 

Page 1 of 3712345102030Last »