Microsoft® Excel® has tremendous power to sort data that goes beyond alphabetically or numerically. This short video tutorial shows how to take advantage of Excel’s built-in sorting capability or go beyond that capability by creating your own custom sorting list. Learn how to sort your data the way you want to view or present it. Watch the tutorial by clicking on the video below or use this link to view the tutorial in YouTube.
OK, this might seem like an article way out of date, but you’d be wrong. Sure, I’m going to discuss Microsoft Office formats from more than five years ago, but you’d be surprised at how often we get asked about the new (!) Office file formats. So here’s the scoop – the one that uses a trowel, not a snow shovel scoop.
When Microsoft® released Office 2007, they introduced a new file format in their various programs that was based on Open XML. (Extensible Markup Language – do you really want more detail on that? We didn’t think so.) Microsoft’s new file format applies to Office products 2007 and beyond. The new file format was incompatible with previous versions until Microsoft released “converters” for older versions of the software, so no need to fret now – you can download the converters and all will be well with your world. (OK, maybe just a little piece your world will be well, but a little piece is better than none.)
To distinguish files using the new file format from the previous format, Microsoft added an x or an m to the extension –
|
Type |
2003 and |
2007 and |
|
| Word |
doc |
docx |
docm |
| Excel |
xls |
xlsx |
xlsm |
| Powerpoint |
ppt |
pptx |
pptm |
What’s the difference between the x and the m?
- The x signifies an XML file with no macros
- The m signifies an XML file that does contain macros
What’s it mean to you?
- If your file has macros, you will need to save using the m extension.
- Yes, files are backwards compatible, but you will need to download file converters for earlier versions of Office.
Why’d they do it? It’s a security thing largely. They say it makes for smaller files, too but I haven’t found that to be true.
Aren’t you glad you asked?
We are strong advocates of keyboard commands. Studies consistently show that using keyboard commands is more efficient than selecting menus and options with a mouse. Learning keyboard commands can take some practice. Welcome, TIPCards. We’re creating a series of TIPCards that will help you find the keyboard command you need for basic functions. The following TIPCards are currently available:
- Special Characters – providing keyboard commands for things like registered trademarks, copyright symbol, the diameter symbol, typographical quotes and other commonly used special characters. These keyboard commands can be used in most Windows programs.
- InDesign Navigation – keyboard commands that help you move from one open document to the next, from page to page, and around the current page. On its flip side, the TIPCard identifies common keyboard commands related to viewing the document – changing magnification and toggling guides and special characters on and off.
- Excel Tips for Beginners – provides basic navigation keyboard shortcuts and commonly used cell editing commands.
Why TIPCards?
Because they make life so easy. Even though we use special characters in InDesign and Excel every day, there are some common commands that we don’t use often. Then we find ourselves in the middle of a project that would benefit from repeated use of some of the commands. It’s
so much easier to pull the TIPCard out of a top drawer and check a command than hunt through program menus or help screens to find what we’re looking for.
TIPCards are available for free download from our DDP Resources/TIPCards page.
Is there a program you’d like a TIPCard for? Or perhaps a series of related commands that it’d be helpful to have at your fingertips. Let us know. Add a comment below or email us at Tips-Tricks@DataDesignsPublishing.com. We’ll add your request to our production list for future TIPCards.
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!
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 tips-tricks@datadesignspublishing.com.
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.
-
Hit Enter
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!
On a hunch, Maggie Timmons sent me an Excel tip. Maggie used to be our office manager but now manages Train-O-Rama, their family owned tourist attraction in Marblehead. She knew this tip would come in handy for me someday.
At the time, I popped the tip into my Memonic filing cabinet. (Read about Memonic here. I love this service so much that I recently upgraded to a paid account.)
When we reached year end and it was time to recreate some of the annual spreadsheets we use I remembered the tip and went back to my Memonic folders to find the details.
We have one very long complicated spreadsheet that helps us manages a part of our business. It does what we want it to do, but remembering what the formula in each cell is actually accomplishing can be quite a challenge. We added a column that provided explanations for the cells in that row, but that created its own problems. Maggie’s tip came to the rescue.
This tip allows you to put information – notes, explanations, etc. – right in the formula line of your Excel spreadsheet. Here’s a simple example:
As you can see, after the Excel formula, simply add the note using the following convention (where the gold text indicates your unique text):
+N(“add your note here“)
It’s incredibly easy and I’ve found it to be incredibly useful. Try it yourself on your next complicated (or not so complicated) spreadsheet. When you go back to the spreadsheet next week or next month, you’ll be glad you took the time to add the notes. Oh, and thanks again, Maggie!