Loading...
Menu

Microsoft Excel Essential Hints and Tips

Microsoft Excel Essential Hints and Tips

Fundamental hints and tips to kick start your Excel skills

By Diane Griffiths

Published by Diane Griffiths at Shakespir

Copyright 2015 Diane Griffiths

Shakespir Edition, License Notes

This ebook is licensed for your personal enjoyment only. This ebook may not be re-sold or given away to other people. If you would like to share this book with another person, please purchase an additional copy for each recipient. If you’re reading this book and did not purchase it, or it was not purchased or your use only, then please return to Shakespir.com and purchase your own copy. Thank you for respecting the hard work of this author.

Disclaimer

The information contained in this book is for general information purposes only. While I endeavour to keep the information up to date and correct, I make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the book or the information, products, services, or related graphics contained within the book for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

Contents

The ‘Learn Excel Visually’ Journey

What’s in it for Me?

About Me

Introduction

Setting up your Spreadsheet

Getting data into Excel

Formatting your spreadsheet

Display Management

Printing and sharing

Bonus – Make it Idiot Proof

Bonus – A Summary of my Favourite Shortcuts

Conclusion

The ‘Learn Excel Visually’ Journey

Excel is relevant for all aspects of a business and it’s not hard to learn, but even apparently smart people can have trouble mastering Excel.

Why is this?

Well – Excel provides the tools, but doesn’t tell you how to use them. You can read about the functionality of Excel and try and figure out what to use, but how do you know what to apply and when? The Learn Excel Visually (LEV) journey is here to take you through the essentials of the Excel process; set up your spreadsheet, capture and structure data efficiently, cleanse it, analyse it meaningfully and present it with visual oomph. There are no complicated macros or convoluted functionality on the LEV journey, I teach what you need to know, not what you don’t.

What I tend to find with Excel textbooks is that they give you a lot of information, it’s all very good information but I don’t use most of it. Therefore the idea of these short handy bite-size books is to provide you with what I have found to be most useful elements of Excel within my day-to-day work and life. I don’t tell you about all the bells and whistles – just what you need on a daily basis. These ebooks are suitable for anyone who is looking to learn Excel and wants to increase their productivity and efficiency, both at work and home. Please bear in mind I don’t cover all functionality of all areas, the point is that I strip out anything that’s not useful and only highlight the functionality that I believe is useful on a daily basis. Don’t buy a huge textbook which you’ll never fully read, pick an ebook which is most relevant to your current learning; read it, apply it and then get on with your day.

What’s in it for Me?

I’m assuming that you will already know the very basics of using Excel; open, close, basic data entry. This book will give you new skills to super-charge your spreadsheet aptitude.

  • Introduction*

We start off with some basic Excel information and terminology to ensure we’re on the same page.

Setting up your spreadsheet

It’s important to have an excellent spreadsheet structure. You don’t want a fragile spreadsheet, a well formatted spreadsheet will be robust; allow the viewer to read the data, understand what the spreadsheet is trying to tell them, spot the key information and be able to action it.

Getting data into Excel.

We also look at a couple of great timesavers; how to quickly input data using shortcuts, and how to transpose and convert data that might be in the wrong format to use.

Formatting your spreadsheet

How to format things within Excel; how Excel understands and interprets what we type, dictates how it displays it, which isn’t always what we want – so this will give you the best practice tips of what to do to present your data.

Display management

Next we look at display management; this considers your workspace rather than your data. How can you set up Excel to optimise your visual working screen and use Excel efficiently.

Printing and sharing

Once you have an amazing spreadsheet may need to print or share this data. Different screens and printers have different settings, but I’ll give you some key things to watch-out for in order to share your data in the best way.

Bonus 1 – Make it Idiot Proof

A short section on how to protect your workbook from user error.

Bonus 2 – My favourite shortcuts

As it says in the title; my favourite Excel shortcuts.

About Me

Excel is such powerful tool; you really can do almost anything you want with it. However that’s not always a good thing! Throughout my career most of my roles have involved using Excel to a large extent, I’ve had to come up with creative solutions to complex problems which allow users to solve, track and improve their daily processes in a simple way. Many of my spreadsheets started simple, got complex and then I had to pull it back to the simple level. The trouble with complex spreadsheets is that they break, get forgotten about and almost always provide at least some incorrect data at some point.

I have various levels of experience which allow me to approach Excel from many different angles:

- Media/advertising production management and operational roles.

- Best practice, change management and process improvement projects.

- Operational and financial software implementation.

- Accounting and financial activities.

- Advanced Excel/VBA modelling and analysis.

Qualifications

- First Class BA Honours Degree in Media (TV Production) and Business Management and IT.

- Advanced Microsoft Excel, Access and VBA (Macros, pivots, database management with Best STL Training)

- AAT (Association of Accounting Technicians) Qualification – Advanced Certificate.

- ACT Change Management Training Bronze Practitioner (DHL training)

- DEPICT Project Management Training (DHL training)

- Prince2 Registered Practitioner Certificate (APMG-International Quality Management System)

- DAMIC Process Improvement Champion Training Bronze Master (DHL training) – (Six Sigma)

- Microsoft Project Level 1 & 2 and Advanced Powerpoint.

- Presenting with Impact

- Train with Confidence

- Coach with Confidence

Introduction

Excel first appeared in 1980s – however spreadsheets have been about since the 1960s, some of you may remember Lotus 123 and perhaps a few of you may have heard of VisiCalc. In the early 1990s Lotus 123 was very popular, I remember using it myself. However in the mid-1980s Microsoft launched Excel which became the popular spreadsheet that we all use today. Excel went through many versions and I remember quite a few of them, I remember that the 2007 version caused a bit of a fuss by introducing a ribbon rather than simple drop-down menus and toolbars. A lot of people didn’t really agree with the ribbon, but I loved it straight-away – it’s much more visual. Then 2010 and 2013 Excel came out, which are very similar but have some new features and improvements.

Which version do I have?

You can either go to ‘help’ in your menu bar or if you don’t have a ‘help’ go to ‘File’ > ‘help’ and you should be able to see the version.

Throughout this book when I take you through examples I will be talking through the 2010 version of Excel. However from 2007 onwards the location of most of the functionality will be very similar so hopefully you will still be able to follow the steps even if you don’t have the 2010 version of Excel.

Main File formats

There are quite a few different Excel file formats, the below are the ones I normally have to deal with:

.xls – the Excel 97-2003 workbook format

.xlsx - the Excel 2007+ workbook format

.xlsm – the Excel macro-enabled format – you can tell whether an Excel file has a macro (advanced programming) based on its file format.

Excel 97-2003 will not be able to open the .xlsx format. So if you are using an old version of Excel, check the format if you are having trouble opening it.

If you aren’t sure which format a certain file is in you can right-click that file within Windows Explorer and click ‘Properties’. Under the ‘General’ tab you will see ‘Type of file:’ this will tell you which file format you are working with, for example, ‘Microsoft Excel Worksheet (.xlsx)’.

Excel 2007+ can open any of the above formats and for the most part you’ll probably be working with .xlsx.

Quick Glossary

Most of you will already understand the basic elements of Excel but I want to use this section to clarify some key Excel terminology to ensure we are on the same page.

Menu = The list of items along the top of the screen; for example, file, insert, page layout etc…

Ribbon = Ribbon is like an expanded menu. It depicts all the features of Excel in an easy to understand pictorial form. Since Excel has 1000s of features, they are grouped in to several ribbons. For example, the ‘insert’ ribbon has functions grouped into; clipboard, font, alignment, number, styles etc…

Name box = Just underneath the Ribbon you have a white box on the left-hand side – it shows the cell reference (default A1) or if you have specified a name for a cell or range of cells, it will show that name.

Formula bar = Next to the Name box, also underneath the Ribbon. The formula bar shows you the contents of a selected cell, particularly useful if you want to see a calculation within a cell and not just the output of the calculation.

Spreadsheet = A method of spreading information across a sheet of paper – the screen represents a piece of paper with grid lines.

Workbook = The file you create in Excel.

Worksheet = A page within the Workbook. By default an Excel Workbook contains 3 worksheets; they are the viewed using tabs along the bottom.

Cells = The grid lines make rectangular boxes – known as cells. Referred to as a letter and a number.

Columns = Cells down a spreadsheet are columns – letters.

Rows = Cells across the spreadsheet are rows – numbers.

First TIP! In your ribbons under some of the groups or sections you have a little icon which has two lines and an arrow.

While you can see some of the popular functionality in the ribbon, if you click on these little icons they will take you to a full list of available functionality within that section.

Setting up your spreadsheet

There are no standard set of rules to ensure that you build the ‘perfect’ spreadsheet. However there are certain principles that you can follow in order to create a framework of best practice in order to achieve good spreadsheet design.

Purpose

The first thing you need to know is the purpose of your spreadsheet. This plays a key role in how to design and develop it.

Start off by knowing what your outputs need to be. What do you need to get out of your spreadsheet? Do you just need to capture and track data, or do you need to capture it so you are able to carry out certain calculations or provide reports?

I find it helps to work backwards; ensure you are clear about the purpose of the spreadsheet, consider the outputs and therefore what logical inputs you require in order to achieve those outputs.

Let’s step through the process.

1 – Define

You should be able to define the purpose of the spreadsheet in one sentence or less. This sounds rather obvious and at little basic but try it. If you can’t tell someone what the purpose of a spreadsheet is in one sentence or less, then I would question what you are doing with it. In particular watch out for trying to do too much with a spreadsheet – one spreadsheet should not and will not solve everything.

An example of a spreadsheet purpose could be:

- To capture weekly timesheet information for team A and produce a weekly summary report.

- To provide summarised management information on the sales for Site X in Jan.

You should also ensure that the spreadsheet is as self-explanatory as possible. Someone should be able to pick up this spreadsheet in a years’ time and be able to understand what the spreadsheet is for, what it is telling them and how to use it.

2 – Consider what inputs you want and what inputs you have.

As a rule of thumb it’s better to have more data than you need. You can exclude data quite easily but it’s hard to get data, particularly accurate data, added in at a later stage.

There might be a gap between what inputs you have and what inputs you need in order to achieve your required outputs. Be realistic here. Can you access that extra data you require or do you need to adjust the purpose of the spreadsheet.

Finally, always state what your data source is, ideally with a date stamp of when the data was captured. I’ll show you a couple of ways you can document this later.

3 – Consider what you want the spreadsheet to process.

What do you need to do with those inputs? Do you need to collect them in order to get a bigger picture? Do you need to perform calculations on them? Do you need to be able to manipulate the data in order to present it as a visual summary? Or are you collecting it in order to pass it onto someone else who will be working with it? This will affect how you work with the spreadsheet and whether there are certain requirements you need to meet.

4 – Consider the output – what and for whom

First, consider for whom the output is for; staff, peers, management or board level? This will dictate the type of output; visual summary, data table, pivot table or dashboard for example. We don’t go into detail in this book on the output format, but it’s something to keep in mind.

5 – Document assumptions.

Any assumptions that you have made about the data or any calculations that you perform on the data must be documented.

This might be rate of inflation, bonus assumptions, working hours a week, etc…

There is nothing wrong with adding notes or instructions within your spreadsheet. Don’t underestimate the value of this at a later stage for yourself, as well as someone else who might be taking it over in the future.

6 – File naming

Please take the time to consider how to name your spreadsheet; be as descriptive as you can.

Too many times I’ve quickly named a spreadsheet and forgotten what I called it and not been able to find it afterwards.

For example; Project_Obvious Descriptive Name_20151105 v1

I also use a specific date format.

yyyymmdd rather than dd.mm.yy

Firstly I don’t like using full stops in my filename. A full stop is classically the differentiator between the filename and file type so I don’t like using it within a file name for consistency purposes.

Secondly I use this format because when sorting in Windows Explorer by filename I can easily see the latest files.

It doesn’t matter too much what the naming convention is, just ensure there is a descriptive naming convention and that you use version control. I can’t stress enough how much you may thank your lucky stars you have version controlled a spreadsheet rather than saved over it. I have had to revert or refer back to previous versions many a time.

To keep your folder structure tidy you can set up a folder called ‘old’ to keep the old versions.

Structure

The second part of setting up your spreadsheet is to ensure that you have strong structural foundations in place.

We’re going to work with some basic sales data as below and set up the spreadsheet:

-

Sales for Freddy Toys

13/01/2013 Racing Track 18605053 £300 Kent store ID 6330

15/01/2013 Princess Doll 18605674 £49.99 Kent store ID 6330

15/01/2013 Teddy Bear 18604477 £9.99 Kent store ID 6330

16/01/2013 Lego Set 18605995 £249 Kent store ID 6330

-

Start at the top left-hand corner and work down.

1 – Start with your title.

2 – Column headers

All versions of Excel have many many more rows than you need, and many more rows than columns. What this means is that you need to have your defined list as column headers and your growing number of items, in this case ‘sales’, in the rows.

Then decide what your column headers should be and what they should be called, try to be as descriptive as you can. I’ve seen many an incorrect data analysis carried out because of vague column headers and people misinterpret what that data is telling them. For example, don’t just put ‘date’, put ‘date collected’, ‘date sold’, ‘date ordered’ – something which provides the user with a bit more information.

The next step is to decide what order the columns should be. Again there is no hard and fast rule for this. The layout should be logical – both for reading and entering data, ideally working from left to right and top to bottom, just as you would read a book.

As a rule of thumb for your first column you want something that will provide a unique reference. It’s good practice to have this unique identifier at the beginning of your data because it will allow for easier data analysis later down the line.

In the list above we have something which looks like an order number. This will be the unique reference so I’ll start with that.

I’ve also resized the columns so I can see the column headers in full. Do this by moving your mouse over the line between columns. The cursor will change to look like this:

Then you can either click and drag the column to the required size or double-click the mouse and the column will automatically resize in order to show the longest piece of text in that column.

The double-click method works well, but as you can see below the column has resized to fit the title, but we only need it to fit the ‘order number’.

You can manually move this back to the end of the ‘r’.

I’ve actually done the columns manually in this case. I like a bit of space between the headers so the data doesn’t run into each other.

Now you can enter your data.

You’ll notice that the date automatically formats when you type it in and that some cells align left or right depending on the data you’ve entered. We’ll look more into this later.

3 – Common data

You might have some data which is the same throughout the rows, its best practice not to have repeated data. It simply doesn’t add value but does increase the amount of data you have to work with and the file size of the workbook.

Here we have a combination of both; we’ve got some data which applies to the whole worksheet and some which has data that applies to each row.

Always look to reduce the number of columns without reducing the data. Move the ‘Location’ and ‘Store ID’ common data to the top of the worksheet.

TIP – If you have to use multiple worksheets to capture similar data, where possible use the same columns for the same things across worksheets. This will make swapping between worksheets more familiar and calculations easier at a later stage.

4 – Comments

If a descriptive label isn’t enough, then you can use a useful piece of functionality called ‘comments’ to add descriptions where relevant. You might want to expand on what data is required or expected in a column.

A comment is associated with a single cell.

To create a comment

1. Select the cell.

2. Right-click and select ‘Insert comment’.

A yellow box will appear where you can enter your comment. Excel automatically places the author name in the comment. I would always initial and date a comment, particularly if you have many people sharing the spreadsheet and they may also be adding comments.

3. Enter your comment.

Once you’ve added a comment you’ll see a tiny red triangle in the top right-hand corner of the cell.

You can choose to show or hide comments in one go using an option on the ‘Review’ ribbon. Otherwise comments will only appear when you hover over the cell.

5 – Instructions

Not every spreadsheet will require instructions, but I’ve seen too many spreadsheets without instructions which could really do with a few lines.

Instructions could range from a separate worksheet with step-by-step detailed instructions through to a couple of lines at the top or bottom of data. This will help any user to quickly grasp what the spreadsheet is for and how to use it.

Below I’ve simply added a line indicating what the user needs to do.

Now we have a solid structure in place. The spreadsheet has been set up; it now needs a bit of formatting which we’ll cover a bit later.

[* Future-proof*]

You need to ensure that you can easily amend or add to the data. This might be by leaving appropriate spaces or grouping data together.

Where possible, avoid using advanced features where simple features are usually more than adequate. In my series of books, I do my utmost to keep it simple. If I mention advanced functionality it will either because it’s worth learning, or to let you know it’s there but why I wouldn’t use it.

Consider how to structure your data so it can be used again in the future. The main way to do this is to keep it simple and have only one piece of data per cell.

Another aspect of future-proofing your spreadsheet will be with the way you structure your calculations; this is outside the scope of this book – but I start to cover this in my next book.

Getting data into Excel

Inputs should only be entered once and should be entered in a logical location within the workbook.

As just mentioned, never try to put data into one cell which should be split into two cells. You aren’t saving space; you are just potentially causing issues down the line with any data analysis. For example, if you have to enter full names into a spreadsheet, put the first name and last name in two cells, not one. It’s easier to join them together than split them apart at a later stage, (easier but not impossible).

When typing data into a cell get into the habit of pressing ‘Enter’ rather than just moving to the next cell. In most cases this isn’t a problem. However if you’re typing a formula you could risk amending that formula by moving to another cell without properly exiting the cell.

Press ‘Escape’ on the keyboard to cancel editing a cell or entering data into it.

If you’ve started to accidentally overtype the contents of a cell, simply press ‘Escape’ to come out of the cell and leave the original contents.

Autofill

Knowing about Excel’s autofill functionality you can really speed up data entry.

Say you had a list of numbers to generate. You can enter the first 3 numbers.

Select those 3 numbers and hover your mouse over the bottom right-hand corner.

The cursor will change to look like this:

Click and drag the mouse down to the number that you need. In this case we want a list of 10.

Release the mouse and Excel will autofill the numbers.

You can also do this with dates. Enter the first date in a cell – can you then use autofill to copy down and create a list of dates

You only need to do the first date in this case and Excel will recognise that you are trying to do a list of dates.

If you want to list the months of the year, enter Jan and Feb, so Excel understands that you want the list to be the first of every month.

Then drag the bottom right-hand corner down to December.

You can also do this with years.

Watch out how you autofill though. Above I’ve shown you how to grab the bottom right-hand corner of a cell and drag down the data.

However another popular method of copying data below other data is a shortcut known as Ctrl+D. I use this shortcut a lot to copy data, but this doesn’t autofill data – there is a big difference between the two:

With autofill you are asking Excel to logically fill in the remaining cells, with Ctrl+D you are asking Excel to copy the top data into the below cells.

Types of copy and paste

Copy and paste is a huge time-saver, however I still see too many people using the right-click functionality, which involves several clicks in order to do a copy and paste.

Here are another two handy shortcuts. Use ‘Ctrl+C’ to copy and ‘Ctrl+V’ to paste the results – give it a try and see how quickly you can copy and paste things within Excel.

There are a few types of copy and paste that you need to know about.

Ctrl+V is a normal paste.

If you are working on a spreadsheet which has calculations in this might be a little more complicated.

Column E in the below is a calculation field. We cover creating calculations in my second book ‘Excel Basic Formulae’, but for now you just need to know that the calculation is multiplying the price by the quantity to give the total price.

If you wanted to copy and just paste the results of the calculations in a different place you can’t just use a normal paste.

It copies the calculation formula not the calculation result.

If you want to only copy the calculation result you need to ‘copy and paste values’.

Start by copying the cells normally; select the cells and press ‘Ctrl+C’

Select the cell to paste to and right-click.

Then you can either click the ‘123’ button which represents ‘Paste values’.

Or you can select ‘Paste Special’ and select ‘Values’.

TIP – if you are copying data from the internet, you often end up copying hyperlinks. If you don’t want hyperlinks in your spreadsheet ensure you copy and paste values.

Transpose data

One final example of a copy and paste is the amazing time-saver ‘Transpose’.

Let’s say you have some data in your spreadsheet listed in rows, but you actually want them to be column headers.

You could retype the headers or do a ‘copy and transpose paste’.

1 – Select the cells.

2 – Select the cell to paste to and right-click.

3 – Select ‘Paste Special’

4 – Tick ‘Transpose’.

5 – Click ‘OK’.

The headers are now horizontal rather than vertical.

While I don’t use this a lot – for the times when I have needed this, it’s saved a lot of time!

Copying non-Excel data into Excel

You might need to select and copy data from another program into Excel, however sometimes the format it appears in when it’s pasted into Excel is not always easy to work with. It can often squash all your data into one column.

That’s where a niffy (yes I said niffy) tool called ‘Text to columns’ is great! If your data is squashed into one column you may be able to split it out quite easily.

The key is to have something consistent which separates each bit of data. For example does your data have what’s known as a ‘separator’ or ‘delimiter’ between each piece of data?

Fortunately we have a comma in-between each piece of data in the above which will make the process straightforward.

1 – Select the data you need to split up and click ‘Text to Columns’

2 – Walk through the Excel wizard steps.

Step 1: Select ‘Delimited’ we have a comma to separate each field.

Click ‘Next’.

Step 2: Tick ‘Comma’, this comma is a delimiter and will trigger Excel to put data after each comma into a new column. You can see that Excel gives you a data preview based on the comma splits in the ‘Data preview’ section. This looks good to me so far.

Click ‘Next’.

Step 3: Here you can specify the type data per column. I’m happy to leave the columns as ‘General’ format. You can change the format later anyway.

Click ‘Finish’.

Now you have all your data separated into columns. Much easier to use and analyse at a later point and it only took a few minutes.

Formatting your spreadsheet

Many people spend way too much time formatting their spreadsheet – often leading to over-formatting, inconsistent formatting or worse – distasteful formatting. The more formatting you have, the larger the workbook will be, Excel has to store the format information somehow and it manifests itself as a larger workbook.

Remember, beauty is in the eye of the beholder, keep it simple; black, white, grey and perhaps a couple of accent colours is usually more than sufficient.

Consistency

Always keep your font consistent and stick with the basic, clear, readable fonts; Arial or Calibri for example. You could perhaps put the title or headers in different font, but restrict yourself to two fonts. Changing fonts inconsistently throughout makes the spreadsheet look messy.

Let’s take the ‘Freddy Toys’ example and work through how to format it.

It has a single font ‘Calibri’ and a single font size ‘11’.

It doesn’t look great, but it does look consistent and is a good starting point for formatting.

Let’s highlight the title. I tend to make this bigger – either font size 12 or 14 and bold it.

I have some labels and headers. Ideally put these in bold. This is particularly useful for column headers as it will help Excel recognise them as headings when using certain functions such as ‘sort’.

Use a cell colour and borders to highlight your column headers. Try not to use really bright colours, they detract from the data.

I would always recommend having dark text on a light background – it’s easier to read and prints better.

At this point try to keep all colour and border lines to a minimum at this stage. You don’t want to overwhelm the viewer. For example the below is difficult to get your head around. I’m not exaggerating, I’ve seen spreadsheets similar to this!

I also sometimes use different coloured headers for different column sections. It breaks up the columns and again, helps to make the spreadsheet more readable. This table doesn’t really require it, but an example might look like:

The text data is in the grey section and the numbers and prices are in the blue section. Always try to go for the lighter pale colours; they are much easier on the eye.

If appropriate use a key in order to tell your viewers what the formatting is telling them. This won’t always be necessary and I don’t often use a key – but if you have status updates in red, yellow and green for example – tell the viewer what those colours mean. Never assume!

Format Painter

A massive formatting time-saver is ‘Format Painter’. I love this tool! It allows you to copy the format from one cell and apply it to another cell or range of cells.

1 – Select a cell containing the formatting that you want. Click once one the format painter button – a series of flashing lines will appear around the selected cell and the mouse pointer will also have a format painter brush attached to it.

The cursor will look like this:

2 – Select the cell or ranges of cells you want the format to apply to. Job done.

That’s great but it gets even more useful.

Do the same but double-click on the format painter button.

This will hold the format painter brush, so you can click on as many different non-adjacent cells as you want to update the formatting. When you’re done, press ‘Escape’ in order to drop the format painter brush.

This double-click trick has saved me hours of repetitive clicking!

Highlights

Is some of your data more significant than the rest of it? Use bold or italic font on areas you want to stand out.

Alternatively you could use borders to give the cells extra attention, but don’t try to use everything all at once – pick your highlight tool.

Cell formats

Number format

When we entered the price into column D we got a mix of formatting. Where we’ve entered decimals places they has remained, but where we haven’t or the decimals are ‘00’ we can’t see the decimal places.

To ensure this is consistent we can select the cells and click the below button within ‘Home’ and under the ‘Number’ group, to increase or decrease the number of decimal places.

Select the cells you want to amend. In this case cells D3:D6.

The press the ‘increase decimal place’ button twice to get two decimal places in each cell.

You could alternatively simply select the ‘number’ format and this automatically formats the selected cells to two decimal places.

That’s a good start.

Currency format

You can actually go one better here by using the currency format.

The currency format will default to the currency Excel has been set up in; in my case I have pounds or GBP as my default. It will also default to two decimal places and if the number is large enough it will also drop a comma in.

Select our cells again and select ‘Currency’ format.

Date format

The date format has many options. If you enter a valid date into a cell Excel will automatically recognise that it’s a date and format it as dd/mm/yyyy, which we saw when we entered our original data.

So when you enter 15/1/13 – Excel will recognise the date and format it as 15/01/2013

You can tweak this format if you want. I find the easiest way to do this is to right-click on the cell and select ‘format cells’.

You will see the full list of format options here.

TIP – I wouldn’t always stick to the ‘Date’ options; take a look at what’s in ‘custom’.

Popular examples of the format you can use are:

dd/mm/yyyy = 15/01/2013

dd/mm/yy = 15/01/13

mmm-yy = Jan-13

As you can see Excel also gives you a few other options you can use. Again, don’t try to get too clever, but it’s good to know that you can be pretty flexible when it comes to dates.

TIP – if you have dates for headings always use real dates and format them as dates. This will make it easier at a later stage if you need to analyse your data, you will be able to use the headings as part of your calculations. If you don’t want the headings to have a single date showing then format mmm-yy to keep the heading tidy.

Percentage format

A quick note on percentages – when you type in 10 and then click on the % button, it gets converted to 1000%.

You haven’t done anything wrong here, but Excel converts the number into the appropriate percentage.

Mathematically 10% is actually 0.1 in numerical terms. Therefore in order to get 10%, the value needs to be is 0.1

Some percentage examples:

1 = 100%

0.5 = 50%

2 = 200%

0.03 = 3%

However if you change the format of the cell before you type in the percentage, then you don’t need to worry about this, simply update the format, type in 10 and Excel does the work for you.

Formatting phone numbers

This can be a tricky one. What we have to do is trick Excel (yes it’s possible) into thinking that the telephone number is actually text.

You do this in order to ensure you don’t lose the zero at the beginning of the telephone number.

You can either set the cell format as text under the ‘Home’ ribbon and ‘Number’ section or you can add an apostrophe in front of the number in order to tell Excel that this will be a text formatted cell and therefore to keep the zero.

General Format:

7 = 7

07 = 7

Excel gets rid of the 0.

Text Format or add an apostrophe:

’07 = 07

Text format

Only use the ‘text’ format if really necessary – as in the above situation. All data entered into a text cell becomes text, even numbers, therefore if you try to build a formula which links to a text cell, the formula will not be able to calculate even if there is a number in that cell.

Number format = 7

Text format = ‘7

The apostrophe stops Excel from seeing this 7 as a number and therefore won’t understand what you’re asking it to do if you try to do a calculation with it.

I see this confusion a lot with VLOOKUP functions, people try to lookup a cell which has been formatted as text and can’t understand why the function isn’t working. We’ll cover this more in my next book ‘Excel Basic Formulae’.

Cell Alignment

Our brains like to make sense of things, so when things aren’t aligned this is uncomfortable for our brains.

By default, numbers are aligned at the right of their cell and text values at the left. You can change that by adjusting the alignment to left, right or centre within ‘Home’ and under the ‘Alignment’ section.

I quite like to centre my headers; it often makes them easier to read. As mentioned earlier I also like to create a bit more space by manipulating the width of the columns. This provides reading space and ensures the data doesn’t look cramped.

TIP – you can also horizontally align your text. Use the buttons above the vertical alignment. You can have text sitting at the top, middle or bottom of your cell. You only see visible difference if you have big row heights.

Column Alignment

If I have a set of columns I want to make the same width I can do this by highlighting all the columns at the same time and then changing the width of those columns in one go.

Alternatively I could check the width by highlighting the column, right-click over the column letter and select ‘Column Width’.

Make a note of the number and repeat the process and apply to the other columns.

TIP – you can select multiple columns at once to do this.

Don’t ignore row height – sometimes I like to increase the row height, again to give my data room to breathe. If you do this then definitely check out the horizontal alignment buttons mentioned above.

  • Descriptions*

Don’t underestimate the importance of including descriptions in order to assist data interpretation.

When typing text within a cell you might want to do a couple of things with it.

Word Wrap

Word wrap is useful if you want to tidy up how to display text within a cell. By default when you start typing in a cell once you pass the right cell boundary Excel just lets you keep on typing. However by using word wrap you can wrap the text within the cell.

You can find the button within the ‘Home’ ribbon under the ‘Paragraph’ section.

Ensure you have a sufficiently wide column; otherwise your text will wrap and the row height will jump down the screen.

Widen the column to make this look better.

Merge cells

Merge cells allows you to spread your text across many columns and/or rows.

You can find the button within the ‘Home’ ribbon under the ‘Paragraph’ section.

Highlight the cells you want the text to spread across and click the ‘merge cells’ button.

Warning – this may seem very useful but use sparingly. When you merge cells you limit what Excel can do. Excel thinks in rows and columns and when you start merging some of those things Excel starts getting confused. In particular it will affect things like filtering, sorting and creating pivot tables (which you might want to do in the future).

I’m not saying don’t do it, but try not to do it much and only with titles or notes, avoid merging column headers at all costs.

There is an alternative to this which doesn’t merge the cells but still spreads the text.

Highlight the cells you want the text to spread across, right-click and select ‘Format cells’.

In the ‘Alignment’ tab, under ‘Text alignment’, under ‘Horizontal’ select ‘Center Across Selection’ from the dropdown.

Click ‘OK’.

It looks like it’s merged but doesn’t confuse Excel – Excel thinks that the text is in cell A2, rather than spread across many cells.

Putting text on several lines

If you’re typing text or multiple lines of data into a cell and you want the text to appear on several lines, then instead of entering the text in another cell just simply press Alt + Enter.

Then you’ll start a new line.

When you do this Excel automatically assumes that you want to word wrap the cell, so the below happens.

Here you can’t tell where the new line starts.

However if I expand the column, you can see the sentence and the start of the new line.

Give it a try.

Dress to Impress

When you submit your spreadsheet – it might not look very exciting – it’s a spreadsheet not a painting – you want it to look tidy and easy to read.

However, if you really feel like you need to impress – we can do that – taking a similar example let’s give you a taste of what you could do.

Step 1 – Add logo

Try not to do this – but if you feel it’s necessary then it can help to impress.

Go to ‘Insert’ and ‘Picture’

Select the image you want to insert into the spreadsheet and click OK.

You may need to resize and move the image to an appropriate placement.

Step 2 – Column A or Row 1

You can keep column A or row 1 clear – this will look nicer on the screen.

Highlight column A and click ‘Insert’.

Excel will add in a blank column.

Do this for row 1 as well.

Then re-size the column and row so they look even.

This sets you up nicely for the next step and you will see the difference it makes.

Step 3 – Remove gridlines

In the ‘View’ ribbon under the ‘Show’ section you can choose whether to see gridlines or not. To hide the gridline untick the ‘Gridlines’ tick box.

This is down to personal preference. When drafting out a spreadsheet and working with raw data I prefer to see the gridlines. However when I’m prepping a report to share or print I prefer to turn them off, it’s easier to highlight the relevant information when the screen is tidied up.

You can see that this looks a lot cleaner.

Step 4 – Avoid fancy text

Avoid being fancy – Please please avoid drop shadow, glow, rotation, gradient or bevel at all costs, just to name a few! Don’t do it, don’t investigate it, it simple don’t work, just keep it simple.

I’ve also just left-aligned the ‘Year’ 2016 – so the labels are now aligned and look tidier.

Suddenly our boring but functional spreadsheet now looks more interesting and aesthetically pleasing.

It’s bound to impress the viewer, at least a little.

Display Management

Let’s start with a little bit of display management – it will make your use of Excel a lot easier.

The following tools will help you to sort out and arrange the visual parts of your spreadsheet and make it easier for you to do some data analysis.

Tabs

A good tip is to rename your tabs as you start using them. This will allow for easier navigation through the workbook.

To use appropriate names for the worksheet tabs double-click over ‘Sheet1’ and you can rename the worksheet. Press ‘Enter’ when you’re done.

You can also colour them by right-clicking over the tab name, select ‘Tab Color’ and pick the colour you want.

TIP – don’t overdo the colours here, I would group similar tabs with the same colour, but definitely don’t do a different colour for every tab, particularly if you have a lot of them.

A nice example is as below:

Finally you can also hide and show tabs. Useful if you want to hide tabs when showing the spreadsheet to people.

For example you can hide the ‘Validation’ tab by right-clicking over the tab name and select ‘hide’.

To show the tab again right-click over any tab and select ‘Unhide…’.

You then get a list of available tabs to unhide. We only have our ‘Validation’ tab.

Click ‘OK’.

We can see our ‘Validation’ tab again.

Zoom

This is a useful bit of functionality will which allows you zoom into parts of the spreadsheet when you need to look at the detail and then zoom out when you need to see the bigger picture and navigate the spreadsheet.

Freeze panes

You can make your workspace easier by freezing the headings of your columns or rows. This will allow you to move up and down or left and right throughout the spreadsheet but still see what your headers are.

We’ll look at a bigger dataset for the next few steps.

Simply put your cursor in the cell cross-point for the columns and/or rows you want to freeze.

Let’s say we want to freeze the row headers (row 2) and the ‘Order Number’ column (column B).

Put your cursor in the row and column after the ones you want to freeze, in this case cell C3.

Then go to ‘View’ > ‘Freeze Panes’ > ‘Freeze Panes’.

Notice the black lines which indicate where the spreadsheet has been frozen.

Hide rows/columns

You can also tidy up your workspace by hiding columns or rows that hold data you currently don’t need to work with. For example in this case let’s say we don’t need the ‘Platform’ column (column I)

Select column I, right-click and select ‘hide’.

You can see that the grey line between columns H and J now looks thicker.

TIP – When you receive data it is always good practice to check to see if there are any hidden columns. You can hide them again once you know what they are, but I would always check that important information hasn’t been hidden.

To do this look out for:

1) Non-consecutive column numbering (in the above it goes from H to J).

2) Thicker line between the columns (as highlighted above).

To unhide, simply highlight the columns either side of the thicker line, right-click and select ‘unhide’.

Sort

You can also sort your data into date or alphabetical order so it’s easier to look for things or search for patterns.

Highlight the area of data that you want to sort – include the column headers.

Go to ‘Home’ > ‘Sort and Filter’ > ‘Custom Sort’.

Select the column you want to sort. In this case we’re going to sort by ‘Client’.

- Select the Client column.

- Sort on Values.

- Select the sort order as A to Z.

A quick note here to point out that in the top right-hand corner of the sort box you can see a tick box ‘My data has headers’. If this wasn’t ticked you would just see Column B, Column C, Column D, etc.., in the column dropdown. By having this ticked (which Excel does automatically) allows the column dropdown to have meaningful values in.

Now the Clients have been sorted alphabetically.

We are also able to do a more comprehensive sort where you can select multiple columns.

Go back into ‘Home’ > ‘Sort and Filter’ > ‘Custom Sort’. To add a secondary sort click on ‘Add Level’.

- Select the GBP Value column.

- Sort on Values.

- Select the sort order as Smallest to Largest.

This will sort the data into:

1) Client name.

2) Then order value, starting with the smallest.

Filter

Finally you can also filter out row data that you don’t want to see. It’s quicker and more flexible than hiding rows, you can filter rows based on certain criteria to hide or show relevant data.

Again, highlight the area of data you want to filter then go to ‘Home’ > ‘Sort and Filter’ > ‘Filter’.

This will add little dropdown arrow boxes onto each of the headers.

You can now filter your data on any of those column headers.

Let’s say we want to filter by ‘Client’ and we only want to see orders relating to ‘Betfair’

Simply click on the arrow by ‘Client’ and select ‘Betfair’ in the list below.

Click ‘OK’.

You can see that the dropdown box in the filtered column now has a little funnel icon.

There are two key ways to see if you have a filter on your data:

1) Funnel icon on one of the dropdown boxes in the headers

2) Blue row numbers highlighting filtered areas (in the above you can see that rows 4 to 13 are blue – highlighting the fact that other rows have been hidden by the filter).

This is the easiest way to tell which column has been filtered, particularly if you weren’t the one to filter the data and need to check if everything is visible.

You can also filter more than one column at a time. Simply repeat the steps with columns you want to filter.

Watch out for auto-filtering without pre-selecting the data, I would always explicitly highlight what you want to filter.

Some people just highlight the headers and click ‘auto-filter’ – this might work, but if there are gaps in your data, then some of it might get missed off.

Some people highlight the entire columns and click ‘auto-filter’ – this may cause the spreadsheet to become unstable because it’s trying to filter on the whole column rather than just the relevant data. This also increases it’s file size.

Summary

To summarise we have covered 6 methods of display management:

1 – Tabs – rename, colour, hide and unhide.

2 – Zoom – in and out

3 – Freeze – to freeze headers (good for large amounts of data).

4 – Hide – columns you don’t need to see (good for exports of data which tend to contain too many columns).

5 – Sort – the data into a logical sequence (good if you want to organise the data a bit before analyzing it).

6 – Filter – use to analyse data and hide any rows you don’t need to see (this is good for dynamic searching of data – it’s easy to filter and unfilter the data).

Printing and sharing

On screen

When you want to show data on screen consider some of the things we’ve talked about.

- Tidy up the screen with blank column A and row 1, also remove gridlines.

- If you have a lot of data use your freeze panes to ensure they help you navigate the data.

- Check that you have the zoom to an appropriate level for viewers to read the information.

Hard copy

When you want to print out your spreadsheet you have a couple more things to think about.

1 – Orientation (landscape or portrait)

This depends on the data. Set the orientation under ‘Page Layout’, ‘Orientation’ and select either ‘Portrait’ or ‘Landscape’

2 – Margins and Alignment

You can use these settings to tidy up how the data will look on the page. Go to ‘Page Layout’ and ‘Margins’ – you could select a pre-defined margin, where possible I select either ‘Normal’ or ‘Narrow’ (If I have a lot of data and want to fit more on the page).

I also often go to ‘Custom Margins’.

Here I can tweak any margin numbers if I need to, sometimes I like to allow less room at the top and bottom, but again it’s down to personal preference. You have to play around and see what you like best. However don’t spend a lot of time on this. If you data fits within the ‘Normal’ setting, then just leave it.

TIP – Tick ‘Horizontally’ under ‘Center on page’.

You can see that Excel has now centred my data horizontally on the page – similar to a centre-align on a cell, but applied to the whole page.

I only tick the ‘Horizontally’ one not the ‘Vertically’ one. For the most part I want my data to appear centrally on the page, but still at the top. Sometimes when you select ‘Vertically’ it can look low on the page. In the below you can see that the data is centred vertically but it’s now too low on the page.

3 – Scale

Scale comes in very useful when you have quite a bit of data to print.

Go to ‘File’ and ‘Print’. Here you will see some printing options. You can see the Orientation and Margin options we talked about above.

At the bottom there is also a Scaling option.

Here I don’t have much data so ‘No Scaling’ is fine. However the next 3 options are really useful if you have more data.

Where possible I try to ‘Fit Sheet on One Page’ – everyone likes a one-page summary.

Otherwise if I know I need to go onto more than one page I select either ‘Fit All Columns on One page’ (for data with lots of rows) or ‘Fit All Rows on One Page’ (for data with lots of columns). This ensures that when the pages print we won’t have data falling across many different pages with a matrix of rows and columns cutting across pages.

If you do have a lot of headers, take a close look at them – do you really need to print them all or can you hide some of the columns?

[_ 4- Header/Footer _]

Another useful tool is to add a header or footer. This is something I don’t do unless I need to. If I’m just printing something to review with someone and it’s a one-off then I don’t bother. However if it’s for something a little more official and we need to record a bit more information about who printed it, when and what the print-out contains, then this is a perfect place for this information.

Go to ‘Insert’ and ‘Header & Footer’.

Don’t let yourself get carried away when adding this information. You could actually spend quite a bit of time messing around with the different options trying to decide what to include and how it should look. Keep it simple.

First you’ll see the Header area, there are three sections to it. Left, centre and right.

Click on the left-hand section and under the ‘Design’ ribbon select ‘File Name’.

You’ll see ‘&[File]’ appear – this means when you print out the spreadsheet the file name will appear at the top, so you know which file was printed.

You can also use the header to add a ‘Picture’ – this would typically be where I would put a logo, just check the image dimensions and the margins, a large logo can take up most of the paper if you’re not careful.

Now click ‘Go to Footer’

In the footer I add ‘Current Date’ perhaps a confidentially clause on the left.

Then the ‘Page Number’ and ‘Number of Pages’ on the right.

However this is just a guide; use what works best for you.

5 – Repeat rows on top when printing.

This is a really useful bit of functionality if you have a lot of data.

Go to ‘Page Layout’ and select ‘Print Titles’.

Under the ‘Sheet’ tab you will see a ‘Print titles’ section.

Here you can select a certain set of rows or columns to repeat on each page you print.

I don’t often use the columns option but the rows one is very useful. Say you wanted to repeat the top information and column headers on each page.

Click on the grid and arrow icon to the right of the ‘Rows to repeat at the top:’ title and select the rows.

Very useful for the below data with lots of rows.

It means that it doesn’t matter which page the viewer is looking at, they know what each of the columns are for because they are repeated at the top of every print-out.

PDF

If you are sending a report which is information only – I would always aim to save it as a PDF. It ensures that data doesn’t get changed and ensures that when it’s printed that your viewer is seeing it in exactly the way that you want them to see it.

Set up your spreadsheet as you would for hard copy printing – however quite simply – go to ‘File’ > ‘save as’ and under ‘Save as type:’ select the PDF option.

Simples.

Bonus – Make it Idiot Proof

Now you have a functional and beautiful spreadsheet. You may need to protect this amazing piece of work. I always do this if there will be multiple users and protect the parts of the workbook that aren’t supposed to be changed my users.

You can protect the worksheet as below.

Choose which cells can be amended or need to be locked (depending on the spreadsheet).

You do this by highlighting the cells, right-clicking and selecting ‘format cells’.

In this case I want people to be able to enter information in the table, but I don’t want them to change the top information or the column headers.

Go to the ‘Protection’ tab and select ‘Locked’.

Click OK.

Now you can protect your worksheet.

Go to the ‘Review’ ribbon and ‘Protect Sheet’

As default Excel ticks ‘Select locked cells’ and ‘Select unlocked cell’.

I like to have only ‘select unlocked cells’ ticked. This means users can only select unlocked cells.

You don’t even have to set a password – just the act of protecting the worksheet is often enough to deter people from messing around – you can leave the ‘Password to unprotect sheet’ field blank.

Click OK.

You’ll notice that you can only select the data under the table now and not the header or column labels.

I try not to set passwords for my Excel workbooks unless there is anything I genuinely have to protect, for example I may have a tab with salary information on. When I show that to a viewer I may want to hide that tab and protect it.

I do this by the ‘Protect Workbook’ option rather than ‘Protect Sheet’. This protects the workbook for ‘Structure’ which will stop people accessing hidden information such as tabs. They will also not be able to add, delete or rearrange the existing tabs.

I tend to ignore the ‘Windows’ option – this will fix any windows you have set up, e.g. the size and position of them. There aren’t many spreadsheets I know of which require that amount of precision in their protection.

Bonus – My Favourite Shortcuts

Ctrl + S – save the active workbook with its current file name and location

Ctrl + Z – undo the last action

Ctrl + Y – redo the last action (undo an undo!)

Ctrl + C – copy the selected cells

Ctrl + X – cut the selected cells

Ctrl + V – pasted the copied cells

Ctrl + B – add or remove bold formatting

Ctrl + I – add or remove italic formatting

Ctrl + D – copy the above cell into the below selected cells

Ctrl + R – copy the left cell into the right selected cells

Ctrl + F – open the find dialog box

Ctrl + H – open the find and replace dialog box

Conclusion

Congratulations! Today we have covered how to set up a spreadsheet; knowing the purpose of your spreadsheet will determine how you need to set it up. From there you can create a spreadsheet structure which will be logical and future-proofed. We also covered how to get that data into Excel from speedy data entry through to converting external data into an Excel readable format.

Next we moved onto spreadsheet formatting and some best practice tips with regards to consistency, types of cell formats, how to best display descriptions and some tips on how to present the data.

We looked how to set up your display in order to be able to quickly and easily work with the Excel data by manipulating the tabs, zoom, panes, rows, columns, sort and filter functionality.

Finally we looked at how to print and share your spreadsheet; on screen, printed out or via PDF, and the bonus sections included how to protect your data and some of my favourite shortcuts.

That’s a lot of information to take in! I’ve keep the data very simple for this book so you can easily re-create some of the examples yourself, but don’t forget to try it with some examples of your own. You won’t master these techniques until you practice them. So that’s what I recommend you do next. Take your next Excel project and analyse it going through each stage of this book; what’s its purpose, structure, data entry, formatting and printing presentation.

-

Thanks again for taking the time to read this ebook. I love writing and I love helping people to understand complex things in a simple way. I’m a very visual person so when I write, I start off with a vision of what I want to create, and then aspire to capture that in a way that translates in the readers’ mind. This is why the journey is about learning Excel visually!

The freedom and individuality you can flaunt both in pictures and words is empowering, getting abstract thought into a tangible format is a challenge I’ll never tire of. If I don’t enjoy writing it, why would you enjoy reading it? That’s very important to me; books aren’t just a learning experience, but an entertaining one.

More books coming soon!

Click here to get the latest updates on my ebook releases.

Leave a Review

Did you find this book helpful? If you liked the book then I would very much appreciate an honest review – I’m always trying to improve my books so your comments will help.

If you noticed any errors please let me know! Drop me an email [email protected] so I can fix them.


Microsoft Excel Essential Hints and Tips

Fundamental hints and tips to kick start your Excel skills. This is an Excel starter book, but I’m assuming that you will already know the very basics of using Excel; open, close, basic data entry. This book will give you new skills to super-charge your spreadsheet aptitude. We look at how to set up your spreadsheet, getting data into Excel, formatting your spreadsheet, a bit of display management and how to print and share your spreadsheets. I also throw in a couple of bonuses; protecting your work and my top shortcuts. The idea of this short handy bite-size book is to provide you with what I have found to be most useful elements of Excel within my day-to-day work and life. I don’t tell you about all the bells and whistles – just what you need on a daily basis. This ebook is suitable for anyone who is looking to learn Excel and wants to increase their productivity and efficiency, both at work and home. Please bear in mind I don’t cover all functionality of all areas, the point is that I strip out anything that’s not useful and only highlight the functionality that I believe is useful on a daily basis. Don’t buy a huge textbook which you’ll never fully read, pick an ebook which is most relevant to your current learning, read it, apply it and then get on with your day.

  • Author: Diane Griffiths
  • Published: 2015-12-11 12:05:12
  • Words: 10528
Microsoft Excel Essential Hints and Tips Microsoft Excel Essential Hints and Tips