Tuesday 24 September 2013

Creating a custom tab

By , Director, Naturally Sales Ltd.

Many people are familiar with the quick-access toolbar for organising a few frequently-used buttons on your Excel application. Did you know that in Excel 2010, you can create a fully-functional custom ribbon tab to organise your most frequently-used functions into logical groupings for easy access?

To do this, click File then Options then Customize Ribbon followed by the item you'd like to create. First choose New Tab and to create custom groupings within the new tab, choose New Group. There is no limit to the number of groupings you can create.

Thursday 19 September 2013

Using data validation to control text length

By , Director, Naturally Sales Ltd.

In the past e-tip, we’ve looked at how to indicate when data is too long by using an IF statement, then how to cut data down to the required size using the LEFT function. In this week’s tip, we’ll see how we can prevent over-long data being entered in the first place. Prevention is better than a cure, after all!

First, select the cell (or cells) which you'd like to restrict. Then click the Data Validation button. If you're using Excel 2007 or above, you'll find the Data Validation button on the Data tab, in the middle of the Data Tools group. In Excel 2003, it's on the Data menu then Validation. Click this button and you'll be presented with the Data Validation dialog.

Data validation allows us to restrict the values a user can enter into our spreadsheets. The default is "Any value". Change this to "Text length", and choose the minimum and maximum length you'll allow. There are other operators to choose in the "Data" drop-down.

On the "Input Message" tab, you can type a brief message to tell your user what you're expecting, and on the "Error Alert" tab, you can type a message in case your user insists on trying to do things their own way!


Wednesday 11 September 2013

Trimming cells to a specified text length

By , Director, Naturally Sales Ltd.

Today we’ll look at how to automatically trim cells and discard anything over a certain length.
 

We do this with the LEFT function. It's constructed like this:

    =LEFT(cell_ref,no_of_characters)


...and will preserve only the specified number of characters from the cell. In "real life", we can preserve only the left 10 characters from cell A1 by entering :

    =LEFT(A1,10)

Why not play with the RIGHT function as well?

Next time, we'll look at how we can prevent our users from entering too many characters to start with, therefore saving ourselves some time cleaning up data.

Tuesday 3 September 2013

Dates

By , Director, Naturally Sales Ltd.


We'll take a little look at date formats today. There are occasions for all of us when Excel's dates don't perform as we'd like them to. By understanding a little more about how Excel processes dates, we're better-equipped to solve any problems we might find.

When you see a date in Excel, what you're actually seeing is just a number, formatted to look like a date. The number represents the number of days since 31st December 1899 - it seems that spreadsheets were invented by the Victorians! If you re-format a date cell as "General" or "Number", you'll see the date changes to a 5-digit number, probably beginning with a 4. Today is 41520 - that's the number of days which have elapsed since the turn of the last century!

That dates are stored as numbers is both a help and a hindrance to us as users - it means we can add or subtract dates as we would any other number, which allows us to measure results over time. It also means that although we see, for example, a year in the date, we can't just change the formatting to get at this information accurately - even if we format as year only, Excel is seeing the exact date.

Fortunately, there are functions we can use to extract information from a date. They are:

=DAY(cell ref)
- gives the day within a month
=MONTH(cell ref)
- gives the month number within a year
=YEAR(cell ref)
- gives the year


Using these functions, we can extract anything we want from a date.

Exercise: have a play with the WEEKDAY function and see what it does - any questions, just email, from our website.