Have you ever opened an Excel workbook and found that the column headings show numbers instead of letters?
The formula look strange too, showing references like RC[-1] instead of D2.
Continue reading “Excel Column Headings Show Numbers-How to Fix”
Excel tips and tutorials
Have you ever opened an Excel workbook and found that the column headings show numbers instead of letters?
The formula look strange too, showing references like RC[-1] instead of D2.
Continue reading “Excel Column Headings Show Numbers-How to Fix”
It seems simple enough, but sorting data in Excel can go horribly wrong, if you aren’t careful.
For example, you could sort a list of names, but miss the phone numbers in a nearby column. Then, when you’re finished sorting, all the names are associated with the wrong phone number.
Here are some tips for trouble free sorting in Excel:
In some lists, you might want to sort by one column. For example, in a list of sales orders, you could sort by Order Number.

Sometimes you need to sort by multiple columns.
For example, in a list of sales orders, you could sort by Customer Name and then by Order Date.
The customers would be sorted alphabetically, and if there are multiple orders for a customer, those orders would be sorted by date.


_______________________
With a bit of programming, you can make it easy for users to enter data in an Excel workbook, and keep them away from the stored data.
Dave Peterson created a sample workbook with a worksheet data entry form.

In the worksheet Data Entry form, there are data validation drop down lists in cells D5 and D7.
The quantity number is typed in cell D9.
After you fill those 3 cells, click the Add to Database button.
A macro runs, and your new record is added at the end of the database, which is a list stored on a different sheet.
In Dave’s workbook, you could click the View Database button to go to the database sheet, and review or edit the existing order records.

In some cases you might prefer to hide the database sheet, to protect the records, but still allow users to view the existing data.
I’ve added a few buttons to Dave’s workbook, to allow users to scroll through the existing records.

The navigation buttons take you to the first, previous, next or last record.
Or, you can type a record number in the yellow cell, to go to a specific record.
In the updated workbook, I left the “Go To Database” button on the data entry worksheet.
You could remove that button, and hide the PartsData sheet, if you prefer, so users are less likely to change the data.
The zipped sample workbook can be downloaded from the Data Entry Worksheet Form page on my website.
In the Download section, look for Version 2 – Navigation Arrows.
New Improved Excel Data Entry Form
How to Customize the Excel Data Entry Form
Delete Orders with Excel Data Entry Form
Check Excel Database Before Adding New Item
___________________________
You can use Excel’s Data Validation to limit the total amount that users enter in a group of cells.
For example, if budget is $3500, you can prevent entries that will go over that total amount.

Here’s what is on the Budget data entry sheet:
On the Budget Data Entry Sheet, data validation is applied to cells C3:C7, because this is where the data entry occurs.


After setting up the data validation, you can test it, to make sure that it works as expected.

_______________________
Last week, the UK Excel User Group Conference was held at the Microsoft offices in London.
You can see a few conference photos by Bob Phillips, including shots of speakers Nick Hodge, Simon Murphy and Andy Pope, and a few pictures of the attendees.

The UK Excel User Group Conference was a free event, hosted by Microsoft, and it filled to capacity quickly, after registration opened.
There may be another conference in the fall, and I’ll post the details here if one is announced.
There’s also a list of upcoming Excel Events on my website.
________________________
Today is the publication date for Pro Excel Financial Modeling: Building Models for Technology Startups, by Tom Y. Sawyer.
I was the technical editor for the book, and was impressed by Tom’s knowledge, and his ability to clearly explain the complex financial modeling topics that his book covers.
Drawing from his extensive experience with technology startup companies, Tom explains the business thinking behind financial modeling.
Then, using a step–by–step approach, he shows how to develop financial models in Excel.
The book includes extensive case studies and you can download the Excel templates from the Apress website.
The templates and screen shots are from Excel 2007, but you could adapt the techniques for any version of Excel.
What you’ll learn:

A couple of weeks ago I described how you could select a cell in a table, and automatically filter the list based on that cell’s value.
The same feature is available in Excel 2007, using a different technique.
Using the same example as in the previous post, the East region is selected in the table below.
With a couple of clicks, and no programming, you can add an AutoFilter and filter the table to show only the East region orders.

In previous versions of Excel, you had to add a toolbar button to use the filter by selection feature.
In Excel 2007, the feature is available in a shortcut menu.
The table doesn’t need to have an AutoFilter currently applied.

An AutoFilter is added to the table, if there wasn’t already one in place. The table is filtered, and shows only the East region records.
To remove the filter, and show all the records again:

The filter is removed from the Region column, but the AutoFilter feature is still turned on.
For more information on Excel AutoFilters, visit the pages on Excel AutoFilter Basics and Excel AutoFilter Tips on my website.
In related news, I recently discovered that the mouse shortcut to copy and paste as values doesn’t work anywhere on a filtered sheet, unless all the records are showing.
Here you can see that it’s not available on the shortcut menu.

You can use other methods to copy and paste as values, such as the Ribbon command, but not the shortcut. I wonder why.

______________________
I saw this question in Twitter this week:
Anyone know a way to get an Excel cell to take over 255 characters w/o converting to pound signs ###? Wreaking havoc on something I’m trying
First, what do you call those symbols – ###?
Anyway, whatever you call them, in the screenshot below, you can see an example of this 255 characters problem, in cell B2.

Here’s what’s on the worksheet, shown above:
Why do the cell strings behave differently though?
Aside from long text strings, there is another reason that causes pound signs to appear in a cell.
Those pound signs appear if a number is too long to display in a cell, and widening the column would fix that problem.
However, in this case the cells contain text, not numbers. Widening the column won’t help.
In example shown above, cell B2 is formatted as Text, and that’s what is causing the long text string problem.
There is a limit to what can show in a cell formatted as Text.
To fix this problem, you can change the cell’s format to General.
To change the format, follow these steps:
After you change the format, the long text string will overflow into the adjoining cell on the right.

____________________
It’s almost April, and the snow tires are coming off my car today, so spring must be just around the corner. That means it’s time to start planning the garden.
We don’t actually plant anything in southern Ontario until the May 24th weekend, but a bit of planning will make it easier to survive the stampede at the garden centre on planting weekend.
I’ve found a few websites that have Excel garden planning workbooks, that you can download and adjust for your climate zone.
At the Compostings blog, there’s a workbook that lists crops, and when you should plant them indoors, then move them outdoors. Uh-oh, I should have started the basil a couple of days ago.

At the Everchanging Garden blog, there are instructions for sketching a garden plan in Excel, using the drawing tools.

At High Prairie Landscape Design, you can download an Excel Garden Budget, garden charts and a seed start chart.

There are lots more examples out there, or maybe you’ve already created your own garden workbooks in Excel.
Now let’s hope for some good growing weather this summer.
_____________________
Well, you might not get a degree when you complete your courses, but there’s lots of educational material available through YouTube.
It’s organized in a special section of the website: www.youtube.com/edu
which features videos from YouTube’s college and university partners.

You can search for a specific topic, or view a list of participating universities, and browse through their course offerings.
University of California, Berkeley, is by far the most popular, but many other colleges offer videos, including a few from Canada, and other countries.
Topics range from chocolate at Harvard University, to a Dental Anatomy Introduction from University of Michigan School of Dentistry.
_______________________