10 Techniques the Pros Know About Microsoft Excel

10 TECHNIQUES

THE PROS KNOW

ABOUT MICROSOFT® EXCEL®

http://www.trainupnow.com

Prabhjot Singh

Copyright © 2013 by Prabhjot Singh.

ISBN:      Ebook         978-1-4836-9301-9

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 and retrieval system, without permission in writing from the copyright owner.

Although reasonable efforts have been made to ensure the accuracy of the information contained in this book, as of the date of publication, nothing herein should be construed as giving advice and no responsibility is accepted for any use or application of any information contained therein.

Rev. date: 09/19/2013

To order additional copies of this book, contact:

Xlibris LLC

1-800-455-039

www.Xlibris.com.au

[email protected]

504734

CONTENTS

Introduction

What’s in this book

1. Setting the Scene

2. VLOOKUP

Fetching data

How to use it

Putting it into action

Your bonuses

The alternatives

3. TRIM and its mates

Getting your data into shape

How to use TRIM

4. LOWER (and UPPER and more!)

5. Error-handling

What a conditional statement does

What’s in an (error code) name

6. ISERROR for fixing things up

Using IFERROR—two formulae for the price of one

7. Managing input into your spreadsheet

Drop-down lists

Making them

Restricting choices

8. AutoFilters

Making them

9. Using groupings, outlines and subtotals

10. Formatting

Cloning

Copying

Adjacent cells.

Non-adjacent cells

Now it’s your turn…

Additional Resources: Audio (MP3) version of this e-book

Introduction

Welcome! This book is designed to give you expert tips on how to handle some of the most useful Excel® tools quickly and painlessly. It applies to Microsoft® Excel® 2007 and above. It will show you how to simply and easily move your use of Excel® to the next level. There are examples that you can try for yourself by opening up a spreadsheet on your PC and following along. You’ll find them constructive for your own spreadsheets, and also (and above all) for spreadsheets that you make to share with other people.

If you’ve already used Microsoft® Excel®, then you’ll probably be familiar with the fundamentals—for example, entering and copying data for calculations into the cells in a spreadsheet. The power of Excel® will be apparent to you, like that marvelous way that it has of automatically adjusting addresses and formulae as you extend your spreadsheet along rows and down columns.

However, as you use Excel® to handle larger amounts of data and calculations, the complexity increases. Basic manual adjustments