Manisha Nigam - Data Analysis with Excel: Tips and tricks to kick start your excel skills

FIRST EDITION 2019

Copyright BPB Publications, INDIA

ISBN: 978-93-88176-67-5

All Rights Reserved. No part of this publication can be stored in a retrieval system or reproduced in any form or by any means without the prior written permission of the publishers

LIMITS OF LIABILITY AND DISCLAIMER OF WARRANTY

The Author and Publisher of this book have tried their best to ensure that the programmes, procedures and functions described in the book are correct. However, the author and the publishers make no warranty of any kind, expressed or implied, with regard to these programmes or the documentation contained in the book. The author and publisher shall not be liable in any event of any damages, incidental or consequential, in connection with, or arising out of the furnishing, performance or use of these programmes, procedures and functions. Product name mentioned are used for identification purposes only and may be trademarks of their respective companies.

All trademarks referred to in the book are acknowledged as properties of their respective owners.

Distributors:

BPB PUBLICATIONS

20, Ansari Road, Darya Ganj

New Delhi-110002

Ph: 23254990/23254991

BPB BOOK CENTRE

376 Old Lajpat Rai Market,

Delhi-110006

Ph: 23861747

MICRO MEDIA

Shop No. 5, Mahendra Chambers,

150 DN Rd. Next to Capital Cinema,

V.T. (C.S.T.) Station, MUMBAI-400 001

Ph: 22078296/22078297

DECCAN AGENCIES

4-3-329, Bank Street,

Hyderabad-500195

Ph: 24756967/24756400

Published by Manish Jain for BPB Publications, 20, Ansari Road, Darya Ganj, New Delhi-110002 and Printed by Repro India Pvt Ltd, Mumbai

Microsoft Excel dates back to 1985 when the first version of Excel v1.0 was released on Macintosh and a Windows version 2.0 followed in 1987. Since then Excel has come a long way and now it is one of the most popular and vastly used spreadsheets. It has functions for calculations, graphing tools, pivot tables, data analysis tools, macro programming and also allows add-ins that enable more complex operations.

Excel has multiple versions, popular being Excel 2013, 2016 and is available on multiple platforms like Windows and Mac. There is also an Office 365 version of Excel that comes with the Microsoft Office 365 Suite. This book has been written for desktop version of Microsoft Excel 2016 for Windows only and not Mac. Excel 2013 & 2019 users may also find information in this book relevant and useful.

Also note that this book does not cover the touch screen gestures. In case you are working on a device that has touch screen enabled, then you will need to follow the touch screen commands instead of the keyboard and mouse actions given in this book. For example, Click may be equivalent to Tap in your touch screen.

Anyone who wants to learn Excel and how it can help them to easily and quickly do the mundane tasks, complex calculations, analyze huge data from internal or external sources, take decisions based on predictions, do forecasting, create plans, charts, reporting dashboards for progress and status etc.

The intend of this book is to explain and simplify the usage of Excel features and functionalities, with help of examples. We hope that this book will enable users in working efficiently and will increase their workplace productivity. We assume that the users reading this book have basic knowledge of working with Excel like opening Excel, navigating around, enter data etc. Microsofts official support website has been used as a reference for this book. For understanding a subject in depth, like the statistical or finance terms used in this book, we advise users to refer to subject related documentation or an expert.

Chapters Getting Started with Excel and Performing functions with shortcut keys will help you in getting started with Excel. You will learn about the basic components of Excel, the available menu options and tools, some customizations, working with multiple workbooks and worksheets, most frequently used and useful keyboard shortcuts.

Chapter Formulas and Functions gives an initial understanding of what are formulas, functions, their components and in the later sections it goes on to explain important functions under different categories. Multiple examples have been used to explain the usage in real world scenario.

Chapters Data Visualization with new Charts types, Gantt and Milestone chart, SmartArt and Organization Chart give details on the different chart types available in Excel and how best we can use them for creating different views of data, good graphics or visualizations for dashboards etc. Using Gantt and Milestone chart, you can plan and track your projects efficiently.

Chapters Putting Data in Perspective with Pivots, Complex data analysis using ToolPak and Forecasting in Excel help in learning the different ways and methods to consolidate data, do complex analysis and predict or forecast based on trends. These chapters are very helpful for people who require to do lot of data analysis and based on results or predictions make important decisions.

Chapters Mail Merge using Excel and Macros in Excel explain the Excel features that help in automating tasks. You will learn how to generate multiple documents automatically with customization and use of macros to do repeated task automatically.

And the last Chapter Whats in Excel 2019 gives a list of new features introduced in the Microsoft Excel 2019.

Microsofts official support website has been used as a reference for this book.

Thanks for selecting this book! Excel with EXCEL!!

Manisha Nigam (author)

Manisha Nigam is a seasoned management professional with twenty plus years of information technology experience, working with multinationals across the globe. Her expertise in Excel comes from the vast experience she has in using and understanding the software over the years, that helped her in working efficiently and managing complex programs. A certified PMP (PMI - Project Management Professional), CSM (Scrum Alliance - Scrum Master), TOGAF 9.1 (The Open Group - Enterprise Architecture) and holds a post graduate degree in computer science and business management from prestigious universities in India.

When we talk about working in Excel, we basically work in a workbook, that is, the file that is created by Excel with the default extension .xlsx. Each workbook contains one or more worksheets and by default, they are named as Sheet1, Sheet2, Sheet3, and so on, in the sequence in which they get created. The users can rename the worksheets by right-clicking on the sheet name and selecting Rename from the options, with a condition that each sheet name in a workbook is unique.