Making a line break within a cell

By Neil Shorney, Director, Naturally Sales Ltd.


Have you ever wondered how to add a line break within a cell? That is, how to make a paragraph, rather than just one long line of text? You’ve tried pressing <Enter> but it always moves you down to the cell below? Well, today is your lucky day!

To create a line break within a cell, use <CTRL> and <ENTER>, and you can create paragraphs to your heart’s content!

Using the SUMPRODUCT function to sum multiple criteria / advanced cell references

By Neil Shorney, Director, Naturally Sales Ltd.

We guide you through creating a two-dimensional table to display information from large data sets.

In the first part, we’ll familiarize ourselves with a useful advanced lookup formula: SUMPRODUCT, then we’ll learn how we can save time populating tables by using a combination of absolute and relative references.

These are best looked at in an example, so you’ll find details in a file which you can download just here.  

www.nsales.co.uk/excel-examples/Populating-a-table-with-absolute-and-relative-references-using-SUMPRODUCT.xlsx

Sharing workbooks

By Neil Shorney, Director, Naturally Sales Ltd.

Tired of having to exit an Excel workbook every time a colleague wants to make a change?

You can use the “Share workbook” tool, which you’ll find on the “Review” tab. If you click this button, you’ll find a check-box to “allow changes by more than one user at the same time”. This will re-save the workbook, and you’ll see the word “Shared” at the top by the workbook name. Now anyone can be in the workbook and modify it together. If there are any conflicts (i.e. 2 people trying to change the same cell), then the second person to make the change has the option to accept the previous change or insert their own.

For added security and tracking, this can be combined with various passwords.

Future-proofing your VLOOKUPs

By Neil Shorney, Director, Naturally Sales Ltd.


Someone called me the other day with a VLOOKUP problem. We’ll look at that problem today, as it’s common when you first start working with this powerful function.

The lady in question, let’s call her Sarah, had created a VLOOKUP formula to search for a value within her data list which stretched from C1:D1000. She was looking up corresponding values for a list of alpha-numeric codes. Her formula was constructed something like this:

    (in cell B2) =VLOOKUP(A2,C1:D1000,2,FALSE)

She found that the formula worked at the top of her data list but in other places returned an error, even though she knew the data was present. Also, when she sorted her list, other values worked instead.

The problem was that she’d neglected to put in her absolute references, and as she dragged the formula down the worksheet, the data list reference increased as well, causing it to look at empty cells. The correct formula should have been:

    =VLOOKUP(A2,C$1:D$1000,2,FALSE)

In our Excel training courses, we talk a lot about “future-proofing” our spreadsheets, and this is a classic example. If Sarah’s data list had grown past D1000, she’d have needed to re-work the formula. A best-practice way of writing the same formula would be:

    =VLOOKUP(A2,C:D,2,FALSE)

In this case, if more data were added to the list, it would be picked up as the formula is now selecting the full columns.

If you’d like to take a look at this problem, you can download an example here: http://www.nsales.co.uk/excel-examples/Future-proofing-VLOOKUPs.xlsx

Another keyboard shortcut

By Neil Shorney, Director, Naturally Sales Ltd.

Regular readers of our e-tips will know that we regularly share keyboard shortcuts, so here's another one for this week's tip.

You may know that pressing the RETURN key moves the selection down and worksheet, and pressing the TAB key moves it to the right. Did you know, however, that using SHIFT+ENTER moves the selection up, and SHIFT+TAB moves it to the left? 

Try this on one of your worksheets to speed up your navigation skills.

Creating a custom tab

By Neil Shorney, 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.

Using data validation to control text length

By Neil Shorney, 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!

Trimming cells to a specified text length

By Neil Shorney, 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.

Dates

By Neil Shorney, 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.

Tidying data for mailings

By Neil Shorney, Director, Naturally Sales Ltd.



When we have an untidy data last for postal mailings, it can really affect the customer's 
perception of us as a company: missing capitals, too many capitals or poorly laid-out addresses can all make mailing labels look untidy. At the beginning of September, we looked at the TRIM function to remove erroneous spaces from words. Today we will look at three functions to correct the case of letters:

    UPPER(ref) - this will change all characters to upper case
    LOWER(ref) - this will change all characters to lower case
    PROPER(ref) - capitalises the first character of each word

Using a combination of these functions, we can tidy our data for professional-looking mailings.

Identifying cells by text length

By Neil Shorney, Director, Naturally Sales Ltd.



I received this email from a recent student on our live e-learning recently, and thought it would make a useful topic for an e-tip:

“I work a lot with mailing lists, and problems arise when the company and address fields contain more than 40 characters. Is there a way to indicate these fields when they contain too many characters in such a way that I can filter on this information? Right now I am simply eyeballing the list and making changes but I may receive lists with 10,000 entries!”

I suspect this might not be a unique problem, and there are two ways we can look at this problem, depending on where the data comes from. If the data already exists in the spreadsheet, then we can use a formula to indicate those cells which match certain criteria in order to sort by these cells; in this case those which contain more than 40 characters.

I'd recommend typing the formula into a new column, by which you can then filter to highlight those records you're identifying.  To highlight cells which contain more than a certain number of characters, you can use the LEN function (short for ""length""). It works like this:

      =LEN(cell_ref)

You can combine this with an IF statement to highlight certain conditions. For example, if you want to know whether C14 has more than 40 characters, you could type the following formula into, for example, D14:

      =IF(LEN(C14)>40,""Too long"","""")

...which would give a blank cell if less than 40, or say ""Too long"" if greater than 40.

Why not try this with other criteria?

The next step is to remove those characters after the fortieth in a cell. That's a topic for next time, then in the following e-tip we'll look at how to prevent users being able to enter more than 40 characters in the first place!

Rounding Numbers

By Neil Shorney, Director, Naturally Sales Ltd.


Excel has a number of ways to round numbers. Today we'll look at three of these methods, and we'll re-visit the topic in a future edition.

The first we'll look at is the ROUND function. This rounds a decimal to the number of digits specified, and is constructed as follows: =ROUND(number, no. of decimals). For example, =ROUND(3.141592654,3) would give 3.142. Remember, of course, that these numbers don't have to be built into the formulas - they can be cell references containing numbers instead.

Maybe, though, you specifically want to round up or down. The formulas for these are constructed in the same way. The ROUNDUP function will round up to the number of decimals specified and ROUNDDOWN will round down in the same way.

As always, if you'd like further help with these, send an email to excel-help@nsales.co.uk and we'll get back to you.

Filling data down multiple sheets

By Neil Shorney, Director, Naturally Sales Ltd.

There are three ways to copy data and formulae down a spreadsheet.  

For small amounts of data you can use a copy and paste commands.  

Filling down a number of cells within one worksheet the easiest way is to drag the content.  

• Select the cell you want to copy.  
• At the bottom right corner of the cell there will be a small black square.  Hover the most over the square until the cursor becomes a black cross.  
• Click the left mouse button and drag the content as far as you wish.  

If you wish to fill down over multiple sheets you can double click on the little black square. 

In Excel 2003 and Excel 2007, this will automatically fill the data as far as the lowest data in any column to the left. In Excel 2010 this will fill as far as the lowest data anywhere in the spreadsheet. This slight difference in behaviour can be tricky when upgrading to 2010.

We look at more Excel shortcuts in Introduction to Microsoft Excel.

Finding text within a cell part 2: the FIND function

By Neil Shorney, Director, Naturally Sales Ltd.

Continuing our theme of finding text within a cell, we’ll look now at the FIND function. The FIND function allows us to see where in a cell’s text a certain string is found. For example, if we look for the letter “t” within the word “Date”, it would return a “3”, because “t” is the third letter of “date”. It’s constructed like this: =FIND(text,within_cell), or =FIND(“t”,A3) where A3 contains the word “Date” If Excel is unable to find the text you’re looking for, it will return an error message. In a future e-tip, we’ll look at how we can combine the FIND and IFERROR functions to see whether or not certain cells contain certain words.

Finding text within a cell: the IFERROR function

By Neil Shorney, Director, Naturally Sales Ltd.


Many people are familiar with using VLOOKUP, INDEX/MATCH or similar lookup formulas to find cell values from a list. However, when we start trying to find a text string within a cell, or to see whether a cell contains certain text or not, things become more difficult.
Unfortunately, Excel doesn't give us a formula to ask it to find cells which contain certain text, along with other text, in the cell body. However, there are techniques we can use. This will be a 3-part e-tip as we address this useful topic. The first part is to learn the IFERROR function.

The IFERROR functions allows us to substitute an error value in a cell for something else of our choosing, and it works by wrapping around the formula you're creating which may (or may not) return an error value. If works as follows:

    =IFERROR(formula,value_if_error)


for example:

    =IFERROR(SUM(A1:A10),0)


...which will return the sum of A1:A10 unless that sum is an error value, in which case it will display 0 instead of the error.

Struggling to see how this will help you find certain text within a cell? Look out for part 2 coming in a fortnight's time as we take this a stage further.

Fixing errors in data sets

By Neil Shorney, Director, Naturally Sales Ltd.


In large data sets,  particularly those downloaded from an external database, it's common to find errors which need tidying. Often these errors aren't errors at all, but simply the way Excel displays numbers within the spreadsheet layout. Today, we'll look at 2 such "errors" which have come up in real examples from recent students on our classes.


Error 1: the hash-tag error: ########
This is a common sight in downloaded spreadsheet data, and one which is easily dealt with. The problem here is not an error, but simply a number (including dates, currencies, percentages, etc.) which is too big for the cell. To view the cell content correctly, resize the column by double-clicking the mouse in the column headers between the one you're in and the one after (i.e. if your error is in column C, double-click between C and D) and the column will expand to fit the longest data.

Error 2: the E error: 5.35E+08
Another common sight, this "error" particularly occurs where you have a long number in a cell with more than 6 characters. It does not occur on dates or currencies, and the cause is the same as the previous example - the number is too big for the cell, yet Excel is attempting to give you a little more information about the number, showing how long the number is (after the +) and the first few digits (535). Much of the time, the method described in section 1 (above) will also work here. Sometimes, however, an extra step may be required. To display properly, the cells should be formatted as "numbers", whereas the default is "general". Before resizing the column, highlight the cells, right-click and format as a number (you may need to adjust the number of decimal places).

Spreadsheet art

I discovered this fabulous chap recently, who uses Microsoft Excel to create works of art. I struggle to find a business use for this, but it's certainly an original application of spreadsheet skills. Take a look at this web page.

Saving your workspace

By Neil Shorney, Director, Naturally Sales Ltd.

You're working with a number of different spreadsheets, you've taken the time to arrange them within the Excel window, everything's where you need it, and it's time to go home. There's no need to spend time re-arranging everything again tomorrow morning – Excel's handy “save workspace” functionality allows you to save Excel exactly as you see it now, and open it up again whenever you need to.

The “save workspace” button is on the View tab. This will create a new file which is effectively a record of how you have Excel set up at the time. It doesn't contain your actual spreadsheets – It contains their size and location information, for re-assembly when it's convenient for you.

Presenting Data

By Neil Shorney, Director, Naturally Sales Ltd.

Pivot tables are a great way to present complex data in a flexible way. Select your data, then click Insert-Pivot table. You can then build your pivot table quickly and easily. For more permanent and nicely-presented reports, such as a dynamic dashboard, it's better to use formulas to interpret your data which will update as new data is added.

We look at this in more detail in Advanced Microsoft Excel. Alternatively, we can customise training to your needs and include your own spreadsheets as exercises.

Formatting as a table

By Neil Shorney, Director, Naturally Sales Ltd.

In Excel level 1, we learn about using the Format as table option to improve the look of data. But did you know that this can also be a time-saver?

Tables provide a quick-and-easy way to total lists, add rows, filter and affect the look-and-feel of your data. To create a table from existing data, highlight your data and choose "Format as table" from the Home tab, or "Insert table" from the Insert tab. As you create the table, tick the "My table has headers" box to use the top line as column headings. You'll see that the look of your data will change and a contextual menu Design tab will appear on the ribbon. In here you have a number of options, for example, adding a quick "Total" row at the bottom, changing the format of the first or last column to bold, removing duplicates, and other useful tools. In the "Table styles" box, you can quickly change the colours of the table. You'll also notice that Autofilter arrows will appear at the top of each column.

One of the most useful aspects is that if you have a column of formulae in your table, then adding a row in the middle will automatically copy those formulae to the new row, saving time copying and pasting.

Should you at some point decide that a table is not the best format for your data, the "Convert to range" button in the "Tools" box will turn it back to a normal data range.

Preserving formatting when filling down

By Neil Shorney, Director, Naturally Sales Ltd.

Today we're going to take a look at Excel's fill down function, and what that means for formatting.
The fill down function is one of the greatest time-savers in Excel. Create a formula with relative references, drag the formula down and watch your data complete itself automatically! It's a great tool, until formatting gets in the way and spoils the fun.

For those unfamiliar with the tool, the idea is that you select the cell with the formula to copy, click on the little black square in the bottom-right corner, and drag it down to create further formulas automatically. You can try it in the attached example.

A frustrating problem is that doing this also copies formatting, so a nicely-formatted table as in the attached loses its colours and you need to spend time re-formatting.

The solution is to use the right mouse button instead of the left. As you let go after dragging, you'll be presented with a number of options. The one you'll need is "fill without formatting" - the formula or data will be copied as before, but formatting will remain in place.

Paste values

By Neil Shorney, Director, Naturally Sales Ltd.

A useful data manipulation technique in Excel is "Paste Values". Copying formula results and pasting only the values elsewhere allows for two things:

  1. to move the results around without them changing
  2. to preserve the results at a moment in time before then changing data

The method differs between versions of Excel. In 2003 and 2007, you need to right-click, choose "Paste Special", select "Values" and click "OK". In Excel 2010 the process is much smoother – simply right-click, then click the button with "123" written on it.

For more about data manipulation, see the Intermediate Microsoft Excel course.

Wrapping text for flexible cell layout

By Neil Shorney

In Excel level 1, we look at techniques to re-size columns in large database downloads to make the data easier to read. One of these techniques is wrapping text. Perhaps you have a large amount of data in a cell, for example a postal address. In your data set, you want to be able to see the full content of the cell, but you don't want a column so wide that it becomes unmanageable. The solution to this problem is the wrap text function.

To wrap text in a column, highlight the full column then right-click in the selected cells and choose Format cells. On the second tab (Alignment), tick the Wrap text box then press OK. Instead of the column re-sizing to accommodate the data, the rows will increase their height and the text will be split over several lines within the column width you need.

We look at more cell formatting in Excel level 1.

Which version of Excel to use for a Naturally Sales training course

Neil Shorney

I've been asked a number of recently which version of Excel people should have before attending one of our courses, so for this week's Excel tip, we'll do a quick comparison of the versions. Over the past 12 months, I've come across people using Excel 2003, 2007 and 2010. Now we have 2013 being released so there's another version in the mix. Each version is an improvement on the previous version and contains various enhancements and bug fixes, but in real terms, what's important? Excel 2007 represented a big change over previous versions. Not only did the interface chance, but there was a huge increase in spreadsheet size and format. Some people are still on 2003 and it's not a problem, but there are some pretty major changes. The number of allowable rows increased from 65536 to 1048576 in 2007 and 2010. This was a useful change when working with large data sets. Other key changes were the introduction of the menu ribbon, a new pivot table interface, and a change in file format from .xls to .xlsx. These were big changes. Since then, changes have been confined to scientific functions which we don't use in day-to-day life, and bug fixes. 2010 gave a fresher look to the ribbon and layout of 2007, and some bugs were fixed. Two useful additions were the right-click Paste values option (a great time-saver) as well as the ability to select ranges from other sheets to create data validation drop-down lists. And here is an important consideration when some employees use 2007 and 2010. You may know that in Excel 2007 data validation, it was possible to define a list from a different sheet by typing the range reference manually even though it wasn't possible to select it with the mouse. In 2010, you can select this range with the mouse. However, if a 2010 workbook is opened with 2007, any data validation references to other sheets are forgotten. Even if the original list was defined manually in 2007. I've had a quick look at the trial version of Excel 2013 and the overall structure appears to be similar, but with another face-lifted view and a cleaner look. It's also more tablet-friendly. There have been improvements in auto-fill with the new Flash fill function which predicts your patterns. Charts and pivot tables have been improved. So back to the original question... if you're attending an Excel training course, it really doesn't matter whether you're on 2007, 2010 or 2013. Even using 2003, a good instructor should be able to deal with the differences. It comes more down to preference. I much prefer 2010 over 2007 for the reasons stated above, and I expect to upgrade to 2013 fairly quickly. But that's personal choice rather than business necessity. The key is not to get hung up on the version, and think about how you communicate with Excel to get the business results you need. For more info on Excel training from Naturally Sales, visit www.nsales.co.uk/microsoft_excel

Some useful data presentation functions

By Neil Shorney

Here are two useful functions plus a light-hearted one...

The first is great for anyone dealing with manually-entered data which might be a little untidy. The TRIM function will remove any preceding or training spaces. You could create a new column after a column of data (for example, names or addresses) and enter the following formula: =TRIM(cell ref). For example if A1 contains " Hello ", and you enter =TRIM(A1), the result will be "Hello". This is a little step towards cleaner data.

You're probably familiar with using MAX and MIN to display the largest and smallest values in a list. But what if you want the third smallest? We can use SMALL to do this. Imagine D1:D5 contains 8, 5, 6, 1, 3 and you want to know the third smallest number. Use =SMALL(D1:D5,3) to show this.

A fun one to close: try entering =ROMAN(2012) into Excel and see what you get...

SUMIF and COUNTIF

By Neil Shorney

If required, Excel is able to add or count only records which agree with a statement. These formulas are quick-and-easy and structured as follows:

=SUMIF(range,criteria,sumrange)
=SUMIF(B2:B9,"Printer",D2:D10)

SUMIF will sum a column if the corresponding cell in another column is true. COUNTIF will count the number of records which are true.

=COUNTIF(range,criteria)
=COUNTIF(B2:B9,”Laptop”)

Creating a drop-down list

By Neil Shorney

Creating a drop-down list: Quality of data in Excel is vital if you are to be able to pull useful results from data sets, and text can be a particular problem if you have repeated values that people choose to spell differently each time they're entered. On the Data tab on the Data Validation button, you can choose what data you will and will not allow.

To create a drop-down list for data entry, first create your list of allowed values in a range of cells, then click Data Validation, choose “List” from the drop-down box and define your source data as the range you've just created. We look at this further in Intermediate Microsoft Excel in the classroom, and in Working with data in Microsoft Excel webinar.

Keyboard shortcuts

By Neil Shorney

By Neil Shorney, Director, Naturally Sales Ltd.

To achieve things more quickly, keyboard shortcuts are an alternative to clicking the mouse. You might know that you can use Enter to move down a cell, and Tab to move to the right. Did you also know that Shift+Enter will move your selection up and Shift+Tab will move it left.

We look at this in more detail in Introduction to Microsoft Excel.

Pivot table tip

By Neil Shorney, Director, Naturally Sales Ltd.

When creating a Pivot Table in Excel 2007 or 2010, they can be opened in Excel 2003, but will be read-only. To create a pivot table which is editable in 2003, 2007 and 2010, create it with Excel 2003. You can then edit it with all versions.

We look at pivot tables in more detail in Intermediate Microsoft Excel.

Formula creation tip

By Neil Shorney, Director, Naturally Sales Ltd. When creating a formula, try to use the mouse as little as possible. Why? Because sometimes, clicking the mouse can confuse the formula. For example, when referencing another sheet using VLOOKUP or when using lists in Data Validation, clicking the mouse back to the other sheet at the wrong time will cause the formula to return an error value. As best practice, use the Return key (move down) or the Tab key (move right), and only click on another worksheet when it's absolutely necessary. We look more at how to create formulas in Intermediate Microsoft Excel.

Excel's crystal ball

By Neil Shorney, Director, Naturally Sales Ltd. Did you know that you can use Excel to gaze into the future...? If you only have partial data for a time period (say, for example, sales data part-way through the year), Excel can predict the rest of the period based on historical results. To do this, select data for the full period, including blank cells for that data which doesn't yet exist. Then create a 2D chart, and on the Layout contextual tab, select Trendlines to predict the future. Note this only works with 2D column or line graphs. We look at predicting future results more accurately in Advanced Microsoft Excel.

Performing backwards lookups in Microsoft Excel

By Neil Shorney, Director, Naturally Sales Ltd.

The most common ways of looking up data in Excel are the VLOOKUP and HLOOKUP functions. One drawback of using this method of finding data, is that they can only look up data from left-to-right (VLOOKUP) or top-to-bottom (HLOOKUP). In Advanced Microsoft Excel, we learn about backwards lookups, but as a work-around, you can copy and paste the column from which you'd like to return a value, to a blank column at the end of the data set, then do a normal forward lookup to return the data you need.

Click here to download an Excel file with step-by-step instructions.