Have you ever imported data or received a spreadsheet from someone in which you have a column of numbers that is formatted as text? It’s pretty irrelevant until you try to apply a mathematical formula to it. Nothing happens. First, you have to convert the data from being formatted as text to being formatted as a number.
No problem, right? The logical thing to do would be select the column, right click, select “Format Cells…” and then apply a number style. Yeah, that’s the logical thing to do. It doesn’t work. Your data will still be formatted as text. You see, you’ve told the spreadsheet to format the cell as a number, but the spreadsheet still thinks that the data in the cell is text! (Can you hear the scarecrow singing “If I only had a brain?”)
Well, there are several ways to fix the problem, but both start with the above procedure. Let me repeat it here and then give you the secret to making it work:
Step One – Reformat the Cells
Select the column
Right click and select “Format Cells…”
Click on the “Number” tab if it’s not selected
Select “Number” from the list at the left
Select the style you want and the number of decimals on the right side of the dialog box
Step Two – Reformat the Data (For Short Columns of Data)
Select the first cell with data in it
Hit the F2 key – this places the cursor at the end of the data in the cell
Hit the enter key – this enters the (non)changes you made and advances the cursor to the next cell
Voila! Excel now recognizes the data in the cell as a number. If you have a short column of numbers, you can get in a pretty fast rhythm – F2 with the left hand, Enter with the right hand, F2 with the left hand, Enter with the right hand, etc.
But if your column of data is long, that’s pretty tedious. Try a different Step Two!
Step Two – Reformat the Data (For Long Columns of Data)
Type the number 1 in any cell formatted as a number
Select the cell and copy (^c) it
Select the column of data or specific cells you want to reformat
Right click and select “Paste Special”
Select “Multiply” from the “Operation” area of the dialog box (it’s in the center)
Voila! Excel now recognizes the data in the cells as numbers. Oh, you can delete that number 1 you typed in any cell
Trust me, the process is much simpler than explaining it is! You’ll have your poorly formatted numbers reformatted as numbers in no time. You’re welcome!
Our blog provides primarily tips & tricks that help you use various software packages more efficiently and effectively…but you’ll also find the occasional bit of industry news or anything else we find interesting. To see all the tips & tricks on a particular topic (such as InDesign), select that category from the column at the left.
Here are some of our favorite keyboard shortcuts. We think you’ll find them pretty handy. The symbol ^ means hold down the Ctrl key while hitting the other keys.
^ Page Down – moves to the next worksheet in the file
^ Page Up – moves to the previous worksheet in the file
Alt Enter – start a new line in the same cell
^ ; – enter the current date
^Shift ; – enter the current time
^Shift ! – applies the number format with 2 decimals, commas to separate thousands and minus signs to indicate negative numbers
^Shift $ – applies the currency format with 2 decimals
^ 1 – opens the Format Cells dialog box
^ D — fills down from cell above
Give them a try. If you’ve got a favorite keyboard shortcut let us know. E-mail us at firstname.lastname@example.org.
Create a Single PDF from Multiple Files.
There’s more than one way to skin a cat, as my mom used to say. (Which in retrospect is a really morbid thing to say. I really don’t think she ever skinned any cats…at least I hope not!)
You can creat a single PDF from multiple files a couple of different ways in Acrobat. It’s just that some ways are easier than others and some offer more options than others.
The “Old Fashioned” Approach — Insert Documents
With your first document open, go to the last page before the new document you want to insert, then
>Document>Insert (or Ctrl-Shift-I)
Go to the document you want to insert, select it, and then use the dialog box that appears to identify what pages should be inserted.
The “New Fashioned” Approach — Combine Documents
Combining documents is a bit easier and offers more functionality. Depending on the version of Acrobat you have, you’ll want to think “Create PDF” (Acrobat 7) or “Combine Files” (Acrobat 8). You’ll probably find these buttons on your toolbar, but if not, look in the File menu.
Select “From Multiple Files” and you’re on your way. A dialog box will open that allows you to browse your system to find the various files you want to combine. Don’t worry if you select them in the wrong order because the dialog box allows you to rearrange them. The beauty of this approach is that you can combine files that are not yet PDF files! You can select Word or Excel files (for example) and combine them with existing PDFs. Acrobat will open a PDF writer and create PDFs from your source documents, combining them together into a single PDF file.
Pretty nifty! Of course if you’re accustomed to creating your own PDFs, you might feel that this approach doesn’t give you the control you’d like. We’d fall into that category. But the command is still helpful in that it will combine multiple PDFs after you’ve created each one just how you want it.
Formatting Cells to Create a Numbered List
Have you ever wanted to create a numbered list in Excel and have the first column show the number with a period after it,but no decimal places? This seems to be something I try to do frequently, but there’s no pre-defined format for a number with a period but no decimals after it. (Yes, I use Excel for lots of things it probably wasn’t intended to be used for.)
Fortunately, creating the new format is easy. Here’s how to do it:
Select the column (or just the cells you want to apply the format to)
Right click and select “Format Cells…”
Click on the “Number” tab if it’s not selected
Select “Custom” from the list at the left
In the box below the word “Type:” enter a zero and a period like this: 0.
All the cells you have selected now have the new format applied. If you enter a “1”, it will display “1.”
You may now begin creating your list. Enjoy!
Versions: CS3 thru 5.5
There are many ways to align objects on your page, but I find that “snapping” them to guide is the easiest and fastest.
So many people don’t turn this feature on (or accidentally turn it off) and I just don’t understand that. When “Snap to Guides” is turned on, you will be able to feel your object “snap” into place when you get near the guide. Of course if you want the object NOT to align with the guide, you can easily toggle the function off or you can snap it to the guide, then nudge it off the guide.
To turn “Snap to Guides” on:
Follow the menus: View > Grids & Guides > Snap to Guides
OR use the following keyboard command to toggle the function on and off: Shift-Ctrl-; (semi-colon)
You can also snap to your document grid:
Follow the menus: View > Grids & Guides > Snap to Document Grid
OR use the following keyboard command to toggle the function on and off: Shift-Ctrl-‘ (apostrophe)
Think semi-colon for guides, apostrophe for grid!
Make a PowerPoint-style Presentation Directly from Photoshop
Want to create a quick presentation from a bunch of images on your computer? Piece of cake!
Open Photoshop’s File > Automate>/ PDF Presentation dialog box.
Browse your computer to select all of the images that you want for your presentation, then click on Presentation as your Output Option.You can even choose at what speed the pages will flip and select from a variety of page transition effects.
Give your presentation a name, designate a destination folder, and Photoshop will automatically create a PDF presentation of your images.
Want to see an area of your screen up close? Or perhaps you’re already zoomed in and want to see the whole page. Either way, going up to the view menu to find the zoom percentage you want or grabbing the magnifying glass – well, I’ve never been satisfied with the results of that tool!
Here are some quick keyboard solutions to your zooming needs.
^ means hold down the Ctrl key while hitting the other keys identified.
^1 Zooms to 100%
^2 Zooms to 200%
^4 Zooms to 400%
Easy to remember, right? The zoom action will center the page wherever your cursor is. But how about when that 200% view is just a little too much or too little? Another easy solution
^- Decreases the zoom level. (Think “minus decreases.”)
^= Increases the zoom level. (The plus symbol is above the equals symbol, so think “plus increases”.)