Excel :The Ultimate Comprehensive Step-by-Step Guide to Strategies in Excel Programming (Formulas, Shortcuts and Spreadsheets): 2

By Kevin Clark

Did you know that there was a lot more you can do with Excel than just manipulating and entering data? Were you looking for a way to learn the different strategies and techniques you can apply in Excel? If you did, you must definitely read this book.

You would have gathered the basic information about Microsoft Excel, and how you can manipulate or format data in Excel. You would have also gathered information on how you can use different statements in Excel to perform repetitive tasks. There is, however, more to Excel that just VBA and formatting of data. You can perform different types of analysis in Excel based on your data. If you have come to learn more about what you can do with Excel, you have come to the right place.

This book sheds some light on the different strategies that you can use in Excel. You will learn more about how to paste data, what types of formatting you can use and how you can use different formulae and graphs to help you understand your data better. Over the course of this book, you will gather information on:

• How to add or delete rows and columns in a worksheet

• How to identify and remove duplicates in the data set

• How to use the Paste Special option in Excel

• How to sort data using multiple sort levels

• Different functions that one can use in Excel to perform numerous operations

• How to use Autofill

• Simple mathematical operations that can be performed in Excel

• Some shortcuts in Excel

If you are looking at different ways to improve your skills in Excel, Grab a copy of this book NOW.

• Language: English
• Publisher: Kevin Clark
• Release date: Mar 7, 2020
• ISBN: 9781393720614

Book preview

Introduction

Microsoft Excel is a program that’s often used to store and retrieve data, such as sales figures, grades, commissions, and any other important data that is connected to a certain company. Needless to say, when it comes to documentation and encoding purposes, Microsoft Excel is really essential. The said program also proves to be confusing to a lot of people. Some do not even know how to make use of Excel formulas, or how to minimize the number of things they see onscreen to make the program more cohesive and efficient. With the help of this book, you’d learn everything you need to know to make the most out of Microsoft Excel! You’d be surprised at how easy it is.

Microsoft Excel is not just for business use; it can be used for anything from comparing two similar products’ specifications to personal finance and easy list and table creation! You can use it for running your home as well as your business and you can also do a lot of different and fun things on it. You will also gather information on how you can develop macros, and how you can improve the performance of macros. There are times when you will have some trouble with using Excel worksheets. The book also throws some light on the issues with using spreadsheets and how you can overcome them.

As well as showing you how to use formulas in Microsoft Excel, I am also going to show you how to do some more advanced things, how to become a power user in a few easy steps and how to astound your colleagues, family and friends with your newfound knowledge. The book also sheds some light on how the different formulae that you can use to improve or automate some functions in Excel. You will also gather some information about VBA and how you can improve your knowledge of VBA. This book sheds some light on the different resources you can use to improve your understanding of VBA.

You have been given a lot of information on how you can use Excel for your house too! There is a bonus chapter at the end that leaves you with the best part of Excel! If you are someone who loves to explain graphically, you will love this chapter.

Chapter One: Automating Processes Using VBA

You can use a visual basic application (VBA) to automate any process that you want to in any MS Office product. You only need to know how to use VBA. This chapter will provide some real examples of how you can use VBA to automate processes in Excel. You can see how you can transform an entire business process into the click of a button.

Let us look at how you can create a composite key, and identify all the records in a file that will match the master source file that you are using. We will also see how you can analyze the records that do not match the source data by using a pivot table to recognize or identify patterns.

Before we delve into learning how to write the code, we will need to ensure that you develop the right mindset to ensure that VBA works for you. You can save enough time when you use VBA to automate the processes. This will also make you a hero at work, but you must ensure that you do use it wisely. There is a fundamental shift that will happen when you begin to work with VBA, and you need to learn how to make that shift work for you and for the company.

The Macro Mindset

When you have automated most or all of the processes that are related to your job, you will have adopted a new outlook on how you can use the different MS Office products, especially Excel. You will develop this mindset once you have a good understanding of the object hierarchy of the applications. You will also gain this mindset when you learn how to use the macro recorder effectively.

When you understand how a macro works, you will look for different ways to automate other processes and potentially save yourself a lot of time. A macro is like any other program – you will need to map some input parameters to output parameters. This is often done to automate work. When you do this, you can say that your mindset has shifted from the fundamental mindset to the macro mindset.

This mindset will cause problems if you do not know how you should use it, or in what context you should use it in. You certainly can automate every process that your firm does, but what is the cost of doing that?

You need to keep two points in mind:

How much time will I take to automate the process?

Will I ever have an issue with any overlapping in the functionality of other processes or applications?

Let us now look at how you should decide on when you should use VBA to automate processes.

Understand the Context of Your Automation Project

As with every project, you should first understand the parts of the process that you want to automate. You should define the timeline, and define your deadlines and the life expectancy of every solution that you develop.

You should also try to understand what the risks are of using VBA are and also look at other alternatives. For instance, if the department you are in is investing in some new business intelligence tool, which will solve the issue; you should focus on testing the tool before you write any VBA code.

You should always look at the timeline, and understand how long it will take you to finish writing the code. You should compare that with the time it will take you to finish that task manually. Also, try to understand how long you will need to perform