Advanced Excel 365 E-Book

R. Arora

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 221

Veröffentlichungsjahr: 2025

Beschreibung

Excel is more than a spreadsheet—it's a powerhouse for data analysis, decision-making, and business productivity. This course begins with foundational concepts, such as cell referencing, formulas, and functions, before moving into tools like data validation, pivot tables, and conditional formatting. You'll also explore advanced techniques for working across multiple worksheets and creating impactful charts.

The journey continues with in-depth coverage of VBA programming, where you'll learn to automate repetitive tasks, debug errors, and create user-friendly applications. Starting with the basics of macros and variables, you'll advance to building custom add-ins and employing sophisticated VBA techniques to handle complex data processes seamlessly. Each lesson combines practical examples with actionable insights.

By the end of the course, you'll possess a versatile toolkit to handle everything from daily spreadsheet tasks to creating powerful custom solutions. Whether you're preparing reports, analyzing trends, or developing VBA-driven applications, this course equips you to work smarter and achieve more.

Leseprobe

ADVANCED EXCEL 365

Including ChatGPT Tips

LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY

By purchasing or using this book and companion files (the “Work”), you agree that this license grants permission to use the contents contained herein, including the disc, but does not give you the right of ownership to any of the textual content in the book / disc or ownership to any of the information or products contained in it. This license does not permit uploading of theWork onto the Internet or on a network (of any kind) without the written consent of the Publisher. Duplication or dissemination of any text, code, simulations, images, etc. contained herein is limited to and subject to licensing terms for the respective products, and permission must be obtained from the Publisher or the owner of the content, etc., in order to reproduce or network any portion of the textual material (in any media) that is contained in the Work.

MERCURY LEARNINGAND INFORMATION (“MLI” or “the Publisher”) and anyone involved in the creation, writing, or production of the companion disc, accompanying algorithms, code, or computer programs (“the software”), and any accompanying Web site or software of the Work, cannot and do not warrant the performance or results that might be obtained by using the contents of the Work. The author, developers, and the Publisher have used their best efforts to ensure the accuracy and functionality of the textual material and/or programs contained in this package; we, however, make no warranty of any kind, express or implied, regarding the performance of these contents or programs. The Work is sold “as is” without warranty (except for defective materials used in manufacturing the book or due to faulty workmanship).

The author, developers, and the publisher of any accompanying content, and anyone involved in the composition, production, and manufacturing of this work will not be liable for damages of any kind arising out of the use of (or the inability to use) the algorithms, source code, computer programs, or textual material contained in this publication. This includes, but is not limited to, loss of revenue or profit, or other incidental, physical, or consequential damages arising out of the use of this Work.

The sole remedy in the event of a claim of any kind is expressly limited to replacement of the book and/or files, and only at the discretion of the Publisher. The use of “implied warranty” and certain “exclusions” varies from state to state and might not apply to the purchaser of this product.

Companion files (color figures) are available for downloading by writing to the publisher (with proof of purchase) [email protected].

ADVANCED EXCEL 365

Including ChatGPT Tips

RITU ARORA

MERCURYLEARNING ANDINFORMATION

Boston, Massachusetts

Copyright © 2024 by MERCURY LEARNING AND INFORMATION.

An Imprint of DeGruyter Inc. All rights reserved. Reprinted and revised with permission.

Original title and copyright: Mastering Advanced Excel: With ChatGPT Integration.

Copyright © 2023 by BPB Publications. All rights reserved. ISBN : 978-93-5551-865-1.

This publication, portions of it, or any accompanying software may not be reproduced in any way, stored in a retrieval system of any type, or transmitted by any means, media, electronic display, or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or scanning, without prior permission in writing from the publisher.

Publisher: David Pallai

MERCURY LEARNINGAND INFORMATION

121 High Street, 3rd Floor

Boston, MA 02110

[email protected]

www.merclearning.com

800-232-0223

R. Arora. Advanced Excel 365:Including ChatGPT Tips.

ISBN: 978-1-50152-251-2

The publisher recognizes and respects all marks used by companies, manufacturers, and developers as a means to distinguish their products. All brand names and product names mentioned in this book are trademarks or service marks of their respective companies. Any omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to infringe on the property of others.

Library of Congress Control Number: 2024939128

242526321 This book is printed on acid-free paper in the United States of America.

Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc. For additional information, please contact the Customer Service Dept. at 800-232-0223 (toll free).

All of our titles are available in digital format at academiccourseware.com and other digital vendors.Companion files (color figures) are available for downloading (with proof of purchase) by writing to the publisher [email protected]. The sole obligation of MERCURY LEARNINGAND INFORMATIONto the purchaser is to replace the files, based on defective materials or faulty workmanship, but not based on the operation or functionality of the product.

To my beloved father, Ramesh Dhingra,a guiding light in my Excel journey

Contents

Preface

Acknowledgments

About the Author

Chapter 1: Overview of Excel 2021

Introduction

Structure

Objectives

Components of the Excel Window

Backstage View

Saving and Sharing Files Online

Interacting with Excel

Working with Default Settings

Formatting of Tables

Paste Special Preview

Flash Fill

Quick Data Analysis

Data Mining

TAT Saving Techniques

Conclusion

Exercises

Chapter 2: Cell References and Range

Introduction

Structure

Objectives

Using Different Types of References

Types of Cell Reference

Relative Cell Reference

Absolute Cell References

Mixed Cell Reference

Named Range

Creating a Named Range

Editing Named Ranges

Deleting Named Ranges

Conclusion

Exercises

Chapter 3: Working with Formulas and Functions

Introduction

Structure

Objectives

Using Formulas in a Worksheet

Array Formula

Using Functions

Example

IF Function

Example

Nested IF

Example

IF With AND

Syntax

IF With OR

IF With NOT

Lookup Functions

VLOOKUP

HLOOKUP

Making VLOOKUP Dynamic

Using the Column Function in VLOOKUP

Using the Match Function in VLOOKUP

Index

Index-Match

Conclusion

Exercise

Chapter 4: Data Validation

Introduction

Structure

Objectives

Trace Precedents

Trace Dependents

How to Use Trace Dependents

Setting Data Validation Rules

Methods of Data Validation

Creating a List

Conclusion

Exercises

Chapter 5: Protection

Introduction

Structure

Objectives

Employee Information System

Protecting a Worksheet by Using Passwords

Protecting a Workbook

Protecting a Part of a Worksheet

Password Protecting a File

Conclusion

Exercises

Chapter 6: Sorting a Database

Introduction

Structure

Objectives

Definition of Sorting

Simple Sort

Multilevel Sort

Customized Sort

Conclusion

Exercises

Chapter 7: Filtering a Database

Introduction

Structure

Objectives

Filters

AutoFilter

Number, Text, or Date Filters

Filtering a List Using Advanced Filter

Filtering Unique Records

Conclusion

Exercise

Chapter 8: Subtotals and Data Consolidation

Introduction

Structure

Objectives

Subtotals

Display Subtotal at a Single Level

Displaying Nested Subtotal

Consolidate Data

Example of Consolidated Data

Conclusion

Exercises

Region: East

Region: West

Region: South

Chapter 9: Pivot Tables

Introduction

Structure

Objectives

Examining Pivot Tables

Recommended Pivot Table

Creating a Pivot Table

Percent of Grand Total

Group Items in a Pivot Table

Grouping of Dates

Monthly Report

Create a Graph Using Pivot Data

Weekly Report

Grouping of Numbers (Creating Slabs)

Slicer

Timeline

Power View

Power Pivot

Benefits of Data Model

Creating a Pivot Table Using Power Pivot

Conclusion

Exercises

Chapter 10: Conditional Formatting

Introduction

Structure

Objectives

Conditional Formatting

Conditional Formatting Using Cell Values (Column-based Conditional Formatting)

Conditional Formatting Using Formula (Record-based Conditional Formatting)

Icon Set

Formulas with Multiple Conditions

Apply a Conditional Formula Based on a Different Sheet’s Cell Reference

Conclusion

Exercises

Chapter 11: What-if Analysis

Introduction

Structure

Objectives

Goal Seek

Using the Goal Seek Command

Projecting Figures Using a Data Table

One-Variable Data Tables

Two-Variable Data Tables

What-if Scenarios

Creating Scenarios

Create a Scenario Summary Report

Delete a Scenario

Display a Scenario

Merge Scenarios from Another Worksheet

Protecting Scenarios

Conclusion

Exercises

Task 1: Goal Seek

Task 2: Data Table

Task 3: Scenario Manager

Chapter 12: Working with Multiple Worksheets, Workbooks, and Applications

Introduction

Structure

Objectives

Links Between Different Worksheets

Sheetname!Reference

Creating Links Between Different Software

Auditing Features

Dependent and Precedent Cells

Workgroup Collaboration

Sharing Workbooks

Merging Workbooks

Tracking Changes

Creating Hyperlinks

Creating Links to a Different File

Conclusion

Exercises

Chapter 13: Working with Charts

Introduction

Structure

Objectives

Creating Charts Using Chart Tools

Chart Designs

Adding Titles and Values in Charts Using Chart Tools

Formatting Charts

Charts for Data

Chart Templates

Chart Filter Option

Waterfall Chart

Recommendations

Sparklines

Create a Sparkline

Customize Sparklines

Change the Style of Sparklines

Conclusion

Exercises

Chapter 14: Creating and Recording Macros in VBA

Introduction

Structure

Objectives

Introduction to VBA

Uses of VBA

Introduction to Macros

Creating a Macro

Adding a Developer Tab on the Ribbon

Recording a Macro

Defining a Macro

Macro Storage

Macro Shortcut

Macro Description

Stop Recording

Relative Reference Macro

Scenario 1

Running Your Macro

Running the Macro by Name

Scenario 2

Scenario 3

Conclusion

Exercises

Chapter 15: Assigning Buttons to Macros

Introduction

Structure

Objectives

Creating Buttons on the Quick Access Toolbar

Modifying Menus or Buttons

Scenario 4

Creating a Button in the Excel Worksheet

Scenario 5

Editing the Recorded Macros

Scenario 6

Scenario 7

Scenario 8

Practice 1

Practice 2

Conclusion

Exercises

Chapter 16: Functions and Subroutines in VBA

Introduction

Structure

Objectives

Writing Procedures

Visual Basic Editor

Project Explorer Keyboard Shortcuts

Inserting Modules

Writing Code Inside Modules

Sub Procedure

Macro

Function Procedure

Scenario 9

Branching a Procedure

Use If…Then...Endif

Use If...Then...Else…Endif

Use If...Then...Elseif…Then…Else…Endif OR Select Case… End

Scenario 10

Scenario 11

Scenario 12

Scenario 13

Scenario 14

Conclusion

Exercises

Chapter 17: Conditional Statements in VBA

Introduction

Structure

Objectives

If…End If

Example

Select Case

Example

Select Case vs. If … End If

Conclusion

Exercises

Chapter 18: Variables and Data Types in VBA

Introduction

Structure

Objectives

Variables and Constants

Variables

Constant

Declaring Variables and Constants

Data Types of Variables and Constants

Using the Option Explicit Statement

Message Box and Input Box

Selecting and Activating Cells

Selecting and Activating Rows and Columns

Working with Sheets

Working with a Workbook

Working with the Application Object

Scenario 15

Scenario 16

Conclusion

Exercise

Chapter 19: Looping Structures in VBA

Introduction

Structure

Objectives

Using Loops (Repeating Action)

Choosing a Loop to Use

Using Do…Loop Statements

Repeating Statements While a Condition is True

Checking Condition Before You Enter the Loop

Checking Condition After the Loop Has Run at Least Once

Scenario 17

Using For…Next Statements

Syntax

Scenario 18

Using For Each… Next Statements

Syntax

Scenario 19

Scenario 20

Scenario 21

Scenario 22

Scenario 23

Scenario 24

Auto-Executed Macros

Practice 3

Practice 4

Scenario 25

Scenario 26

Scenario 27

Conclusion

Exercises

Chapter 20: Arrays and Collections in VBA

Introduction

Structure

Objectives

Arrays

Declaring the Arrays

Syntax

Example

Using Arrays

Array Indexing

Declaring a Dynamic Array

Syntax

Resizing a Dynamic Array

Array Example

Conclusion

Exercises

Chapter 21: Debugging and Error Handling in VBA

Introduction

Structure

Objectives

Errors

Error Handling

Scenario 28

Error Number

Scenario 29

Debugging the Macro

Conclusion

Exercises

Chapter 22: User Forms and User Input in VBS

Introduction

Structure

Objectives

User Forms

Creating User Forms

Adding Other Controls

Handling Events for the Control

Scenario 30

Conclusion

Exercises

Chapter 23: Advanced VBA Techniques and Best Practices

Introduction

Structure

Objectives

Code to Set Initial Values for the Control

Code for Option Buttons

Code for Insert Button

Double-click Insert Button

Code to Show User Form

Add-Ins

Scenario 31

Code for the Change Case Form

Creating Menu with Code

Conclusion

Exercises

Chapter 24: Building Custom Add-ins with VBA

Introduction

Structure

Objectives

Protecting Your Add-Ins with a Password

Using Add-Ins

Conclusion

Exercises

Chapter 25: ChatGPT with Excel

Introduction

Structure

Objectives

Using ChatGPT With Excel

Conclusion

Exercises

Index

Preface

This book will explore the powerful trio of ExcelTM, Visual Basic for Applications (VBA)TM, and ChatGPT. These tools combine the strength of data analysis, automation, and conversational AI to empower you in the realm of information processing and decision-making.

Throughout these pages, you will be provided with practical knowledge, hands-on examples, and step-by-step instructions to master Excel’s data manipulation capabilities, unlock the potential of VBA for automation and customization, and study ChatGPT for natural language interactions.

Whether you are a beginner seeking to understand the basics, or an experienced user looking to enhance your skills, this book will serve as your roadmap to excel in these domains. It will guide you through the fundamentals of Excel, introduce you to the world of VBA programming, and show you how to integrate ChatGPT into your applications for dynamic and intelligent conversations.

This book will explore the endless possibilities of Excel, VBA, and ChatGPT. Here is a brief look at the various chapters:

Chapter 1: Overview of Excel 2021

Discover the new interface, components, and features of Excel 2021, including online file sharing, customizing the ribbon, and leveraging flash fills and instant data analysis for efficient data entry.

Chapter 2: Cell References and Range

Learn about several types of cell references and named ranges for easier referencing. Practice these concepts with hands-on exercises.

Chapter 3: Working with Formulas and Functions

Master Excel’s formulas and functions, including IF variations, lookup functions, and dynamic VLOOKUP. Reinforce your understanding through practical exercises.

Chapter 4: Data Validation

Set data validation rules to ensure data accuracy. Explore custom validation techniques through practical exercises.

Chapter 5: Protection

Secure your Excel files by protecting worksheets, workbooks, and specific parts with passwords.

Chapter 6: Sorting a Database

Organize data efficiently using simple, multilevel, and customized sorting methods.

Chapter 7: Filtering a Database

Filter data with Auto Filter and advanced filtering techniques to extract relevant information.

Chapter 8: Subtotals and Data Consolidation

Summarize and analyze data using the Subtotal feature and consolidate data from multiple sources.

Chapter 9: Pivot Tables

Create and format PivotTables for versatile data analysis, including advanced features like grouping items and generating graphs.

Chapter 10: Conditional Formatting

Apply cell value-based and formula-based formatting, including advanced techniques with multiple conditions.

Chapter 11: What-if-Analysis

Use What-if-Analysis tools, like Goal Seek and data tables, to project figures and create scenarios.

Chapter 12: Working with Multiple Worksheets, Workbooks, and Applications

Link different worksheets and software, merge workbooks, and track changes for collaborative work.

Chapter 13: Working with Charts

Create and customize charts using Chart Tools, templates, and sparklines to enhance data visualization.

Chapter 14: Creating and Recording Macros in VBA

Automate tasks by creating and recording macros, including relative reference macros.

Chapter 15: Assigning Buttons to Macros

Enhance user interaction by creating and customizing menus and buttons for macros.

Chapter 16: Functions and Subroutines in VBA

Understand and write functions and subroutines in VBA, including branching techniques.

Chapter 17: Conditional Statements in VBA

Use Select Case and If...End If statements to control program flow efficiently.

Chapter 18: Variables and Data Types in VBA

Declare variables and constants, understand data types, and use message boxes and input boxes.

Chapter 19: Looping Structures in VBA

Implement loops, like Do...Loop and For...Next, to repeat actions in VBA code.

Chapter 20: Arrays and Collections in VBA

Work with arrays and collections to store and manage multiple values effectively.

Chapter 21: Debugging and Error Handling in VBA

Manage errors and debug VBA code to resolve issues efficiently.

Chapter 22: User Forms and User Input in VBA

Design interactive user forms with controls like buttons and text boxes for enhanced user input.

Chapter 23: Advanced VBA Techniques and Best Practices

Explore advanced programming techniques and follow best practices for efficient VBA coding.

Chapter 24: Building Custom Add-ins with VBA

Create custom Add-ins to extend Excel’s functionality and protect them with passwords.

Chapter 25: ChatGPT with Excel

Integrate ChatGPT with Excel for enhanced tasks, content generation, and data analysis while maintaining data privacy and security.

Color images are available for downloading by writing to the publisher at [email protected].

By the end of this book, you will have the knowledge and confidence to leverage the combined power of Excel, VBA, and ChatGPT to streamline your workflows, automate repetitive tasks, and engage in intelligent, data-driven conversations.

Acknowledgments

I would like to extend my heartfelt gratitude and acknowledgements to the following individuals, whose unwavering support and love have been a constant source of inspiration and encouragement throughout the creation of this book:

To my supportive husband, Mr. Harsh Arora, whose unwavering belief in my abilities and unconditional love have been my anchor. Your encouragement and understanding during the writing process have been invaluable, and I am truly blessed to have you by my side.

To my dear mother, Mrs. Asha Dhingra, and my in-laws, Mrs. Shakun Arora and Mr. K. K. Arora, for their endless encouragement, love, and sacrifices. Their unwavering belief in my dreams and their constant presence in my life was a driving force behind the completion of this book.

To my cherished children, Vansh Arora and Mannat Arora, whose patience and understanding during this time have been remarkable. Your unwavering support and bright smiles have been a constant source of motivation, reminding me of the importance of balance and family throughout this endeavor.

To my sisters, Mrs. Sudha Khurana and Mrs. Namrata Lal, extended family, and friends, for their continual support, words of encouragement, and belief in my abilities. Your unwavering faith in me has given me the strength to overcome challenges and pursue my passion.

To the readers of this book, who have entrusted me with their time and curiosity. It is my sincere hope that the knowledge and insights shared within these pages will inspire and empower you on your own Excel journey.

I am immensely grateful for the contributions and support of each and every one of you. Thank you for being an integral part of this journey.

About the Author

Ritu Arora is a highly skilled and experienced Microsoft Certified Trainer, specializing in Power BI, Excel, PowerPoint, G Suite, and ChatGPT. With over 20 years of corporate training experience, including international assignments, Ritu has successfully trained over 70,000 individuals at companies like DDFS, EY, RateGain, LG, IIMs, SMBC, Ericsson, HCL, Tata Advanced Systems, BPCL, Nestle, Citibank, Adidas, and Hero Honda. Her expertise, excellent communication skills, and ability to tailor training programs to specific needs have made her a sought-after corporate trainer.

CHAPTER    1

Overview of Excel 2021

Introduction

Excel 2021 is a subscription-based version of Microsoft Excel that is part of the Microsoft 365 suite of productivity tools. It is a cloud-based version of Excel that provides users with access to the latest features and updates. Here is an overview of some of the key features of Excel 2021:

■Collaborative Editing: Excel 2021 allows multiple users to edit a spreadsheet simultaneously, making it easier for teams to work together on projects.

■Cloud Storage: Excel 2021 files are stored in the cloud, which means they can be accessed from anywhere with an internet connection.

■Power Query: Excel 2021 includes Power Query, a tool that allows users to connect to and import data from a variety of sources.

■Dynamic Arrays: With dynamic arrays, users can perform calculations on a range of values and return multiple results in a single cell.

■Artificial Intelligence: Excel 2021 includes AI-powered features that can analyze data and provide insights. This includes tools such as the Ideas feature, which suggests charts, graphs, and other visualizations.

■New Chart Types: Excel 2021 includes new chart types, such as the funnel chart and the map chart, which allow users to display data in new and interesting ways.

■Improved Data Analysis: Excel 2021 includes new data analysis tools, such as the Data Types feature, which allow users to convert raw data into structured data that can be used in calculations and analysis.

MS Excel is a spreadsheet software, which is a tool used to record data, support plotting, and analyze the entered data. This is a powerful tool with numerous features that can be used to track a budget, create a record of sales or invoices or maintain a training log. You can store the details of your products or service inquiries, or explore its other business applications.

As in the previous version, this version has a set of menus at the top of the window known as the Ribbon. All the Excel commands are present on the menu. An Excel document is known as a Workbook, and each Workbook is divided into a set of rows and columns. An intersection of this tabular structure is known as a Cell. Data is entered into cells. In fact, all operations performed in the spreadsheet are applied to the cells. MS Excel has a set of tools by which users can format data, perform analysis, and create charts.

Structure

This chapter will cover the following topics:

■Components of the Excel window

■Backstage view

■Saving and sharing files online

■Interacting with Excel

■Working with default settings

■Formatting a table

• Paste Special preview

• Flash fills

■Quick Data analysis

■Data Mining

■TAT saving technique

Objectives

After studying this chapter, the reader should be able to understand the new layout of MS Excel, figure out how to change the default settings, understand the tool in general, and identify the different types of references, as well as the Named Ranges.

Components of the Excel Window

Figure 1.1 Excel Welcome Window

When you open Excel by clicking its shortcut, a unique landing page known as the Welcome page appears. This welcome page offers various sample spreadsheets, such as movie lists, personal budgeting, trend, analysis, and more. Most importantly, it offers the Blank worksheet option, with which users can open a blank spreadsheet and enter the data according to their requirements. The Welcome Window of Excel is shown in Figure 1.1.

This window also has a text field that allows the user to search for online templates. This can be used to synchronize the user’s Excel interface with the online MS office templates library.

When you double-click the Blank window option, a blank spreadsheet opens, as shown in Figure 1.2.

Figure 1.2 Various Components of an Excel 2016 Window

Backstage View

Figure 1.3 Options Available in the Backstage View

From the Backstage view, you can manage your documents and the data related to them. Here, you can create, save, send, and inspect documents for hidden metadata or personal information. The File tab replaces the MS Office button and the File menu used in the earlier releases of MS Office. Figure 1.3 features the various options available in the Backstage view:

Below are the various options available in the Backstage view:

■Quick Access Toolbar: This toolbar is present at the top left corner of the window. It contains commands for saving the current workbook and undoing and redoing actions. This toolbar can be customized by adding buttons for frequently used commands. It is movable and can be moved underneath the Ribbon.

■Ribbon: The Ribbon is organized into various tabs, with each ribbon tab activating a ribbon. Each tab is divided into a set of commands known as groups, which contain commands and options that relate to the group name.

■Gallery: A Gallery may be displayed within a Ribbon, but more often, it is a drop-down group of commands or functions. The Gallery uses icons or other graphics to show the result of commands rather than the commands themselves. Figure 1.4 shows the gallery options.

Figure 1.4 Gallery Options

Saving and Sharing Files Online

Even if you do not have MS Office 365 or any of its versions, you can still access and view the essentials for free online. Refer to Figure 1.5.

Figure 1.5 Share Option in the Backstage View

Interacting with Excel

There are various ways by which any user can interact with the Excel worksheet. These are typing or using the mouse to choose a command, make selections, click buttons, and other do other actions.

■Using the Ribbon: The Ribbon is a main container for menus and tools. When you choose a Ribbon tab, it displays Ribbon groups, which contains tools (buttons and lists). Some of these tools expand to display simple lists and the gallery, as shown in Figure 1.3.

■Using Galleries: The Gallery is an interactive list of options which display the option under the click command. For example, the font gallery shows a list of fonts available. Some galleries use live preview, so that when you move the pointer over the options on a gallery, each option is previewed. For example, if you select text in the worksheet and display the font gallery, moving the pointer over each font in the gallery causes the selected text on the screen to display in that font. Refer to Figure 1.4.

■Using Tools: When you keep your mouse pointer over any tool, a small description about the tool appears, which is called a super tooltip. It provides a small description about the tool so you can understand what exactly the tool can do.

Tip  Press Alt+F4 to see the shortcuts related to the option inside the ribbon.

Working with Default Settings

Excel allows you to customize various aspects, behaviors, and methods by which you can interact with it. You can change the default settings of Excel, including font, number of iterations, file locations, and the file which opens on starting Excel. To select the dialog box of options, you need to click the File Tab button and then select Options, as shown in Figure 1.6:

Figure 1.6 Option Window of MS Excel

The various options are as follows:

■Personalize Options: You can change the workbook settings by using the Personalize Options tool to change the type and size of the font, the number of worksheets in the workbook, and to activate the Developer tab, which is used for Macros.

■Save Option: This option allows you to change the default file location, file format, and Auto Recover settings of the file.

■Customize the Ribbon: In Excel, you can create custom tabs and groups, and rename or change the order of the built-in tabs and groups. In the Customize the Ribbon list, the custom tabs and groups have “custom” after the name, but the word “custom” does not appear in the ribbon.

■Adding a Custom Tab and a Custom Group: Here we have a set of steps by which we can add a custom tab and custom group in the ribbon. Command can be added only in the custom groups.

To add a custom tab, follow these steps:

1.Click the File tab.

2. Click the Options button under Help.

3. Click Customize Ribbon.

4. Click New Tab.

5. To see and save your customizations, click OK. Refer to Figure 1.7.

Figure 1.7 Steps to Customize a Custom Tab and a Custom Group

Formatting of Tables

Excel provides various predefined table styles that we can use to format a table quickly. It is a format which is provided by Excel, so we do not have to change the style of or font used in the table.

You can format the table by using the following steps:

1. Select the range.

2. Select Home on Ribbon.

3. Select the Style Group.

4. Select the Format Table. This option opens the various format styles in the form of a drop-down list. By clicking on any style, you can apply it to your data.

Paste Special Preview