Remove the last octet of an IP address in Excel

Maybe I’m the only one who ever has to do this, but here’s how to remove the last octet of an IP address in Excel using one (relatively) simple formula, not a series of calculations.

Read more

Why is Publisher hyphenating my words?

Why is Publisher hyphenating my words? That’s probably one of the most common questions I hear about Microsoft Publisher. There’s a good reason for it, but I understand if you want to disable it. So I’ll answer both questions.

Read more

Find words in all caps in Word

Find words in all caps in Word

Sometimes it’s helpful to be able to find words in all caps in Word. Microsoft Word, that is. This helps you find all the acronyms so you can make sure you spelled them out or explained them properly.

Double-checking acronyms is especially important when writing government proposals, which require you to spell out acronyms on first usage. If you’ve never written a proposal, be glad, smile, and nod. Many acronyms make good index material, so this trick helps when you’re writing an index. Also, acronyms are frequently jargon, so making sure you explain them adequately is just part of good writing. Or, if you’re a fan of high quality typography, you might want to find them all so you can set them in small caps for improved aesthetics and readability.

Here’s how you do it.

Read more

Spot phishing e-mails with Outlook

Spot phishing e-mails with Outlook

I got e-mail the other day from Turbotax saying someone had filed my taxes for me. Obviously a cause for concern, right? Here’s how I determined the message was fake in about three minutes. You can spot phishing e-mails with Outlook the same way.

Some people will tell you not to even open a message like this, but if you’re a computer professional, at some point someone is going to want you to prove the message was fake. I think this is something every e-mail administrator, desktop support professional, security professional, and frankly, every helpdesk professional ought to be able to do.

So here’s how you can get the proof. And generally speaking, Outlook’s default configuration plays it safe enough that this procedure won’t get you into any trouble, at least on modern versions of Windows.

Read more

Get rid of trailing zeroes in Excel charts

I was making a chart in an Excel spreadsheet the other week and it insisted on adding trailing zeroes in the charts after the decimal point, even though all of my stats were whole numbers. Here’s the solution I finally found to get rid of trailing zeroes in Excel charts.

Read more

How to justify text in Publisher 2013

I couldn’t figure out how to justify text in Publisher 2013, but I finally found the way. Here’s how.

I did some layout in Publisher 2013 after having not done page layout in a decade or more, and Publisher 2013’s interface confused me a bit. I finally found two ways to justify text.

The fast, easy way: Highlight the paragraph you want and press CTRL-J. Done. I love keyboard shortcuts. Justify starts with “j,” so that makes the keyboard shortcut pretty easy to remember.

The harder, slower way: In the paragraph tab, click the down arrow in the lower right corner. In the “Indents and Spacing” tab, there’s a dropdown box called “Alignment.” Select “Justified,” then click “OK.” Scout’s honor, I looked past that option at least 17 times.

Oddly enough, once I used full justification, then I got a little icon in the paragraph section of the ribbon for that, but I’m 100% certain that option wasn’t there before.

Pro tip: If you’re going to justify text, make sure you enable hyphenation. Click inside the text box, then click “Format” under “Text Box Tools” under the ribbon. Justified text looks much better when hyphenation is on. Hyphens reduce the number of spaces the computer has to insert. Fewer spaces mean fewer “rivers” in the text, and that makes for a better-looking page. Here’s more on hyphenation if you’re curious.

Taming Office 2013’s appearance

A couple of months ago I upgraded to Office 2013 at work. I liked it, but around the same time, my eyes started burning. I never made the connection, but then last week, when a coworker upgraded, he mentioned his eyes were burning, and he made the connection.

He found this guide for toning down Office. We both recommend the dark gray scheme, which is much easier on the eyes than the default harsh white scheme.

Read more

How to keep Excel from dropping zeroes after the decimal point

At work part of my job is reporting security metrics along with my colleague, and sometimes we report things like the number of machines running a specific operating system. The problem we run into is that when it comes to operating system versions, OS X versions 10.1 and 10.10 are really not the same. We run into similar issues with versioning for other operating systems too, such as AIX.

To keep Excel from dropping those significant zeroes on your charts, highlight the column containing your version data and switch it from a numeric format to text format. Then switch to the tab that contains your chart, refresh the data, and your charts will show the zeroes properly.

Merge CSV files from a command prompt

I had a bunch of CSV files I needed to merge. I don’t spend half an hour loading all of them into Excel and doing a bunch of copying and pasting. Here’s how I merge CSV files from a command prompt.

Read more

Excel won’t scroll down or otherwise? Try this

Excel won’t scroll down or otherwise? Try this

I regularly work with Excel spreadsheets with tens of thousands of rows, correlated. Or hundreds of thousands of rows of raw data. Working with gigabytes of data taught me a lot. Including things it wasn’t supposed to, like what to do when Excel won’t scroll down or otherwise with the keyboard, or Excel mouse scroll isn’t working.

Large, complex Excel sheets are pretty fragile. Among other things, the largest of the sheets will stop scrolling. The scrollbar on the right scrolls, but the display doesn’t move. The mouse wheel scrolls, but again, the screen doesn’t move. And the arrow keys don’t work either. I can’t scroll down, I can’t scroll right, or do anything useful with the data because I can’t see the whole worksheet. In this blog post, I cover two ways to solve the problem when Excel won’t scroll.

Read more