Search Not Just Numbers

Tuesday 26 November 2013

Excel Tip: Making the same change to multiple worksheets

Sometimes we build an Excel spreadsheet that has a number of worksheets (or tabs) that are the same layout.

No matter how well we have designed our spreadsheet, there are times when we need to make updates. This can be further improvements or changes to reflect changing requirements.

This can be a time-taking task if the changes are to be made to multiple worksheets. Fortunately, Excel has a way to do this.

First of all, I think it is worth mentioning that some of the reasons we have multiple worksheets are due to bad spreadsheet design. For example, having a sheet for each month is not usually the best way to handle most applications. In most situations it is far better to have a sheet that contains all of the data and a report sheet that allows you to report that data for any month. For my advice on laying out data, take a look at this earlier post.

Assuming that you have decided that you do need multiple sheets (or that you already have them and are not going to spend your time changing a spreadsheet that works!), then you can make Excel apply the changes to each of the sheets that you want it to.

First of all, click on the tab name of the first of the sheets that you want to edit (as you normally would to make that the active sheet). Then, hold down the Ctrl key while you select the other sheets you want to edit too.

You should now see that the backgrounds of the tab names of the selected sheets are all white.

While these multiple sheets are selected in this way, any change you make to one of the sheets will be applied to them all.

The really important thing to remember though, is what you need to do when you've made the changes:

Right-click on the tab name of one of the selected sheets and click "Ungroup Sheets"

If you don't do this, then you can imagine the chaos you can cause when you type anything into one of the sheets and it changes them all!

This can be really useful, as long as you remember that last step!

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 19 November 2013

Twenty Principles for Excel Good Practice (from the ICAEW)

You may remember that back in May, I told you about some work I was doing as part of the ICAEW IT Faculty's Excel Community Advisory Committee. The other committee members and I have been working towards a set of Excel Principles to be published by the ICAEW (the Chartered Accountancy body for England and Wales),

In my post back in May, I posted my initial contribution. This was then merged with the contributions of the rest of the committee. Over the past few months these have been argued about, compromised, honed and, in no small part due to the diplomatic skills of Paul Booth of the IT Faculty, improved, to arrive at a set of Twenty Principles that the whole committee can get behind.

This process has now reached the stage where the principles can be released into the wild, to get the views of the wider Excel world.

I have reproduced the headline principles, as they currently stand, below, but I would strongly recommend that you visit the ICAEW's IT Counts website, where you can download the full document, including explanations and examples illustrating each principle, as well as join in the debate.

THE TWENTY PRINCIPLES IN BRIEF 

The spreadsheet’s business environment 

1. Determine what role spreadsheets play in your business, and plan your
spreadsheet standards and processes accordingly.

2. Adopt a standard for your organisation and stick to it.

3. Ensure that everyone involved in the creation or use of spreadsheets has an
appropriate level of knowledge and competence.

4. Work collaboratively, share ownership, peer review.

Designing and building your spreadsheet

5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for
the job.

6. Identify the audience. If a spreadsheet is intended to be understood and used
by others, the design should facilitate this.

7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.

8. Design for longevity.

9. Focus on the required outputs.

10. Separate and clearly identify inputs, workings and outputs.

11. Be consistent in structure.

12. Be consistent in the use of formulae.

13. Keep formulae short and simple.

14. Never embed in a formula anything that might change or need to be changed.

15. Perform a calculation once and then refer back to that calculation.

16. Avoid using advanced features where simpler features could achieve the
same result.

Spreadsheet risks and controls 

17. Have a system of backup and version control, which should be applied
consistently within an organisation.

18. Rigorously test the workbook.

19. Build in checks, controls and alerts from the outset and during the course of
spreadsheet design.

20. Protect parts of the workbook that are not supposed to be changed by users.



Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 12 November 2013

Excel Tip: Switch columns and rows

We often use Excel to analyse data from elsewhere. This might be from another Excel spreadsheet, or a text file exported from another software tool - maybe your accounts package.

We all know that Excel is brilliant at this, but the job can be made significantly more difficult when the data we want to analyse is not in the best format for us to work with.

Earlier this year, I wrote a post on tidying up text in Excel. This can help sort out the format of individual fields to get them the way you want them, but what if the layout of the data itself not what you require.

If you are not sure how you want your data laid out to make it easy to analyse, this post might help.

One frustrating layout issue is when you are presented with data that is in rows where columns would be more useful, or vice versa - fortunately Excel has a simple solution.

Say we have some data that we want to paste into our spreadsheet as follows:


This data would be easier to analyse if we had one column per field, i.e. two columns of data rather than two rows.

All we have to do is copy the data as normal but, when we paste it into our spreadsheet, click Paste Special, or Ctrl-Alt-V and we are presented with the following dialog box which allows us to make various changes to the data as we paste it (I will look at some of the others in future posts).



In this case we want to tick the Transpose box. This flips the data round, switching rows for columns.

When we then click OK, our pasted data looks like this:



Exactly as we wanted it.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 5 November 2013

Excel Tip: Removing all spaces from text

Before we get into today's tip, I'm aware that we have a lot of new visitors and subscribers and I thought it might be a good time to remind you of my needaspreadsheet.com service.

If you are reading this blog, then you probably use Excel in your job, and my posts are intended to help you to get the most out of this excellent tool. However, sometimes you might not have the time or the skills to get it to do what you want.

When that happens, you just need to enter what you need your spreadsheet to do in the form on the site, and I will send you a fixed price quote to build (or fix) your spreadsheet for you. The whole service is handled by email and as a result, your location really doesn't matter - we have regular clients all over the globe.

Anyway, enough about me - on with today's tip...

In an earlier post on tidying up text, I introduced the TRIM function, that removes all leading and trailing spaces from text, as well as ensuring that any spaces in the text are reduced to single spaces.

Whereas this is useful for many applications to tidy up data, sometimes we need to remove all spaces from text. This happened to me the other day with some client data from their bank account. The only way to ensure consistency of the data was to remove all spaces.

Thankfully, Excel has a function that can help with this, as well as a number of other issues we might face when manipulating text data.

The function we can use is SUBSTITUTE. This replaces all references to one string of text in a cell with another string of text.

Its syntax is:

=SUBSTITUTE(Text to apply the formula to,Old text,New text,[Instance number])

Text to apply the formula to - this can be text in inverted commas, but is usually a cell reference containing the text

Old text - the text that you wish to see replaced, this can be text (in inverted commas) or a reference to text

New text - the text that you wish to see Old text replaced with, this can be text (in inverted commas) or a reference to text

Instance number - this argument is optional (as indicated by the square brackets) and specifies which instance of Old text you wish to replace (1 for the first instance, 2 for the second, etc.). If this argument is not entered, the function replaces all instances of Old text.

We can use this function to remove all spaces from the text in cell A1, by entering the following in cell B1:

=SUBSTITUTE(A1," ","")

Note that the second argument is a space in inverted commas, whereas the third argument has no space.

B1 will now be the same as A1, but we will have replaced all instances of a space with nothing - i.e. removing all of the spaces.

It's as simple as that.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".