Wednesday, October 31, 2012

Excel 2013 Update!

Excel 2013 is part of the new Microsoft Office suite, of course, and many of us are eager to see the final version released. I have been using the beta version of the suite for several months, and you can count me in as one of the early adopters who is excited (and maybe a bit impatient) to get my hands on the Final Release.

Two Big Questions are Out There:

1. When will it be released?
2. How much will it cost?

As concerns the First Question, we do not as yet know with any absolute precision, but industry insiders now say it looks like it will be sometime in January. Although this may seem like a long time to wait for the update on our favorite spreadsheet software, kudos to Microsoft may be in order for waiting until they are more sure the bugs are out.

As concerns the Second Question, that information is here! We now know there will be Two Basic Approaches to acquiring the 2013 version of MS Office (and, of course Excel):

1. The Traditional Method of purchasing software: Locally-installed and with the license valid for just a single PC or Mac. The pricing will be similar to what is has been in the past: Office 2013 Home & Student will cost $139.99, Office 2013 Home & Business is $219.99, and Office 2013 Professional will retail for $399.99.

2. A newer Subscription Method: Alternatively, consumers can opt for the online subscription-based offerings. Office 365 Home Premium is $99.99 per year (or $8.33 per month), and Office 365 Small Business Premium is $149.99 per year per user (or $12.50 per month per user).

If you are like me, you may be adverse to paying for a Subscription, rather than having your own copy on your personal hard drive, but consider this: With Home Premium Subscription, the you also get Outlook, Access, and Publisher (not included with Office 2013 Home and Student edition), an additional 20GB of online file storage with SkyDrive, and 60 minutes per month of Skype calls. Plus, it can be Shared across Five different users and/or devices!

Whichever way you choose to acquire your Excel 2013 product, I am confident that you will find things to like in this latest rendition. If you haven’t yet experienced the beta, you can go to to download a Free Trial version today.

Happy Halloween and Excelling!

Wednesday, October 24, 2012

Self-Expanding Charts

This Trick is a real Halloween Treat!

If you have ever created a Chart that needed Updating on a regular basis, you know what a bore it can be to do the frequent revisions. That sort of work may keep some people employed, but there are Better Ways to handle this task.

Building a Self-Expanding Chart is remarkably simple, and can Save You A Good Deal Of Time and boredom (and, after all, who needs more boredom!)

To create your Self-Expanding Chart in Excel 2007 or versions thereafter, here is what you do:

1. Create your Chart as usual ( you can use the sample data below)
2. Select any cell that contains the data that is used by the chart
3. Choose Insert / Tables group / Table to convert the range to a table

Amazingly that’s all there is to it! Excel will recognize the data as a Table and any data that is added to it is Automatically Updated in the Chart.

Give this Charting Trick a try, and find out why it is such a Treat!

Wednesday, October 17, 2012

Custom Date Formats

Although they are a major part of the grist which drives the Excel, handling Dates can be a bit tricky and are often misunderstood. The most common Date Formats are as follows:

  m - Month as a number without leading zeros (1-10)
•   mm - Month as a number with leading zeros (01-10)
•   mmm - Month as an abbreviation (Jan - Dec)
•   mmmm - Unabbreviated Month (January - December)
  d - Day without leading zeros (1-10)
•   dd - Day with leading zeros (01-10)
•   ddd - Week day as an abbreviation (Sun - Sat)
•   dddd - Unabbreviated week day (Sunday - Saturday)
•    yy - Year as a two-digit number (for example, 12)
•   yyyy - Year as a four-digit number (for example, 2012)

But suppose you want to create a Date Format that is Not included in the built-in list in Excel? No worries, here is what you do. Let’s assume that you wish to have a date format that has the elements shown as follows:

1.   4-Digit Year
2.   2-Digit Day
3.   Unabbreviated Month

The Steps are as Follows:

1.  Right-click on the cell containing the date
2.  Select Format Cells
3.   In the Category list on the Number tab, select Date
4.   In the Type list to the right select the format closest to what you want
5.   In the Category list select Custom, and the format you selected will show in the Edit Box above
6.   Modify the format in the Edit Box and see the preview in the Sample
7.  When you are happy with your results click OK
8.  The new format will be applied to the cell and will also be Added to the List of custom formats

For our Example, the results will be a date format in the syntax: yyyy/dd/mmmm.

Controlling your Date Formats, even when they are Custom, is really quite easy in Excel. The payoff is Improved Communication. And that is, of course, The Bottom Line…

Wednesday, October 10, 2012

Find and Delete Rows

Who says you can’t teach an old dog new tricks? I just ran across this Fabulous way to select rows (or columns, cells, etc) in a database that contain a Key Word, and Delete Them All at Once.

Let’s say that you have a database or table that contains records you wish to delete. Furthermore, let’s say the records you wish to get rid of all have the word Excelite included in one of the cells. It may be possible to sort the database and delete the records, but this may not be convenient (or even impossible if the Key Word does not lead the string in a cell).

Here is What You Do:

1.   Choose any cell on the worksheet, and press Ctrl+F on your keyboard
2.   Type the Key Word (“Excelite” in our example) in textbox and click the Find All button
3.  Now Select an item in the list, and press Ctrl+A to select the entire list
4.   Go to the Home tab, click the down-arrow on Delete (in the Cells group on the ribbon), and then click Delete Sheet Rows
5.   Voile! Your unwanted records are history!

You will note when you click the down-arrow on Delete that you are also presented with options to:

• Delete Cells
• Delete Sheet Columns and
• Delete Sheet

These can also be useful, but you will probably find the Delete Sheet Rows to be the most handy. With the high cost of Energy, it’s always good to have an additional way to Save Some in Excel!

Happy Excelling!

Wednesday, October 3, 2012

Take Control with Your Ctrl Key

Some of us don’t take advantage of the humble Ctrl Key. It sits there on our keyboards in two different positions making it convenient for any of us to use it to Best Advantage.

So just what can you do with the Ctrl Key?   Many Cool Things; Here a Few…

1) Speedy Navigation: When you press Ctrl and any Arrow Key (up, down, left, or right), you Jump to the last populated cell in that direction. Zip! Just like that!

2) Fill the Contents and Format: Using the topmost cell of a selected range as reference, click Ctrl+D to Fill the Selected cells below. (An easy Crowd Pleaser…).

3) Simultaneous Data Entry: When you want to put the Same Information into multiple cells, all you need to do is Copy it, Select the cells you wish to paste it into, and use Ctrl+Enter to fill them simultaneously. This really Rocks!

4) Make Noncontiguous Selections: Select any cells you want, hold down the Ctrl key while you click on a cell or drag through a range of cells. As long as you hold down the Ctrl key, you can click and select Anything You Want on the spreadsheet.

5) Print Without the Mouse: Sometimes we still need to Print a worksheet. Rather than using the mouse, another simple Crowd Pleaser is to simply press Ctrl+P. Bamm! Ready to print!

There are many other Cool Ctrl Tricks that you can use to help you Control your worksheets, but I think you will find some of these discussed to be your Favorites.