Griffiths - Microsoft Excel Essential Hints and Tips: Fundamental hints and tips to kick start your Excel skills

Microsoft Excel Essential Hints and Tips

Fundamental hints and tips to kick start your Excel skills

By Diane Griffiths

Published by Diane Griffiths

Copyright 2015 Diane Griffiths

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

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 doesnt 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 dont.

What I tend to find with Excel textbooks is that they give you a lot of information, its all very good information but I dont 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 dont 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 dont cover all functionality of all areas, the point is that I strip out anything thats not useful and only highlight the functionality that I believe is useful on a daily basis. Dont buy a huge textbook which youll 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.

Whats in it for Me?

Im 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 were on the same page.

Setting up your spreadsheet

Its important to have an excellent spreadsheet structure. You dont 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 isnt 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 Ill 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 thats not always a good thing! Throughout my career most of my roles have involved using Excel to a large extent, Ive 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 didnt really agree with the ribbon, but I loved it straight-away its 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 dont 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 dont 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 arent 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).