Price Michael - Excel 2016 in easy steps

Michael Price & Mike McGrath Excel
2016 In easy steps is an imprint of In Easy Steps Limited 16 Hamilton Terrace Holly Walk Leamington Spa Warwickshire CV32 4LY www.ineasysteps.com Copyright 2015 by In Easy Steps Limited. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without prior written permission from the publisher. Notice of Liability Every effort has been made to ensure that this book contains accurate and current information. However, In Easy Steps Limited and the author shall not be liable for any loss or damage suffered by readers as a result of any information contained herein. Trademarks Microsoft and Windows are registered trademarks of Microsoft Corporation.

All other trademarks are acknowledged as belonging to their respective companies. Contents Introduction This chapter shows how the spreadsheet, the electronic counterpart of the paper ledger, has evolved in Excel, taking advantage of the features of the different versions of Microsoft Office, and the Windows operating system. The Spreadsheet Concept Spreadsheets, in the guise of the accountants ledger sheet, have been in use for many, many years. They consisted of paper forms with a two-dimensional grid of rows and columns, often on extra-large paper, forming two pages of a ledger book, for example (hence the term spread sheet). They were typically used by accountants to prepare budget or financial statements. Each row would represent a different item, with each column showing the value or amount for that item over a given time period. For example, a forecast for a 30% margin and 10% growth might show: Ledger sheets pre-date computers and handheld calculators, and have been in use for literally hundreds of years.

Any changes to the basic figures would mean that all the values would have to be recalculated and transcribed to another ledger sheet to show the effect, e.g. for a 20% margin and 60% growth: To make another change, to show 10% margin and 200% growth, for example, would involve a completely new set of calculations. And, each time, there would be the possibility of a calculation or transcription error creeping in. With the advent of the personal computer, a new approach became possible. Applications were developed to simulate the operation of the financial ledger sheet, but the boxes (known as cells) that formed the rows and columns could store text, numbers, or a calculation formula based on the contents of other cells. The spreadsheet looked the same, since it was the results that were displayed, rather than the formulas themselves.

However, when the contents of a cell were changed in the spreadsheet, all the cells whose values depended on that changed cell were automatically recalculated. The first spreadsheet application was VisiCorps VisiCalc (visible calculator). Numerous competitive programs appeared, but market leadership was taken first by Lotus 123, and now by Microsoft Excel. This new approach allowed a vast improvement in productivity for various activities, such as forecasting. In the second example shown on the previous page, youd set up the initial spreadsheet using formulas, rather than calculating the individual cell values. A spreadsheet might contain these values and formulas, for example: The = sign signals to Excel that what follows is a formula and must be calculated.

However, what will be displayed in the cells are the actual values that the formulas compute, based on the contents of other cells: When you want to see the effect of changes, such as different values for margin and growth, for example, you change just those items and instantly see the effect, as the values calculated by the formulas are adjusted and redisplayed. The capabilities of the spreadsheet applications have evolved, and the use of spreadsheets has extended far beyond the original use for financial planning and reporting. They can now handle any activity that involves arrays of values interrelated by formulas, grading examination scores, interpreting experimental data, or keeping track of assets and inventories, for example. In fact, the newest spreadsheet applications seem to support just about any possible requirement. Sets of predefined functions were added, plus support for writing small programs, or macros, to manipulate the data. Further developments incorporated graphs, images, and audio.

Microsoft Excel VisiCalc and Lotus 123 were MS-DOS programs, subject to its command-line interface, but Microsoft Excel was developed for Windows. It was the first spreadsheet program to allow users to control the visual aspects of the spreadsheet (fonts, character attributes, and cell appearance). It introduced intelligent cell recomputation, where only cells dependent on the cell being modified are updated (previous spreadsheet programs recomputed everything all the time, or waited for a specific Recalc command). Later versions of Excel were shipped as part of the bundled Microsoft Office suite of applications, which included programs like Microsoft Word and Microsoft PowerPoint. Versions of Excel for Microsoft Windows and Office include:

It still consists of a large array of cells, organized into rows and columns, containing data values or formulas with relative or absolute references to other cells. This means that many of the techniques included in this book will be applicable to whichever version of Excel you may be using, or even if you are using a spreadsheet from another family of products, though, of course, the specifics of the instructions may need to be adjusted.