MS Excel Bible, Save Your Time With MS Excel!: 8 Quality Excel Books in 1 Package

By Andrei Besedin

No doubt, there are lots of computer programs available, but no one can deny the fact that Microsoft Excel is one of the most important computer programs. It is owing to its functional and key role in many sectors. Talk of data organization, class work, businesses activities and many more, you can find Excel very useful. 
So if you are a novice, just starting up Excel or a professional our Excel Bible might be all you need. Many institutional, personal, and business enterprises have embraced the use of Excel because of its utility and ability to serve as visual basic for different applications.
You can embrace our Excel Bible because it has many benefits to offer you as you ride on in your Excel career. Our incredible Excel Bible is the combination of all our exceptional Excel books. So imagine how great and powerful this book can be.
Some of the benefits you can derive from our top winning Excel Bible are:
•Powerful go-to guide for all Excel needs
•It makes it easy for you to navigate through all Excel functions 
•A fully inclusive set of indexes allows users to find particular functions
•It takes you lesser time to read because it lacks gibberish and unimportant contents. 
•Gives you access to all our Excel book at once
Little can we tell you about the benefits embedded in this top winning book. You can do yourself a favor by buying this book. No doubt, you will have direct access to all the benefits our book has to offer. 
There is one fact that we cannot deny, our book does not contain every information about Microsoft Excel. The main aim of our Excel Bible is to provide meaningful and practical help in implementing some of the most powerful and important excel functions. 
Buying our book could save you about US$1000 which is more than enough to take care of some other things on your bucket list.
You don’t need to wait until tomorrow before you make your purchase of this incredibly advantageous short book. Start saving your career today because tomorrow might be too late.
To obtain a strong knowledge of some of the powerful excel functions all you need is just a single click. Click the buy button at the upper right side of the page. You would not regret doing it!
Why wait, when you can explore the most important excel functions in just one book and take your Excel career to the next level. Purchase your copy of the top winning book now!

• Language: English
• Publisher: Andrei Besedin
• Release date: Jul 31, 2023

Book preview

Legal Notes and Disclaimer

Text Copyright © [Author name here]

All rights reserved. No part of this guide may be reproduced in any form without permission in writing from the publisher except in the case of brief quotations embodied in critical articles or reviews.

Legal & Disclaimer

The information contained in this book and its contents is not designed to replace or take the place of any form of medical or professional advice; and is not meant to replace the need for independent medical, financial, legal or other professional advice or services, as may be required. The content and information in this book has been provided for educational and entertainment purposes only.

The content and information contained in this book has been compiled from sources deemed reliable, and it is accurate to the best of the Author's knowledge, information and belief. However, the Author cannot guarantee its accuracy and validity and cannot be held liable for any errors and/or omissions. Furthermore, changes can be periodically made to this book as and when needed. Where appropriate and/or necessary, you must consult a professionalbefore using any of the suggested remedies, techniques, or information in this book.

Upon using the contents and information contained in this book, you agree to hold harmless the Author from and against any damages, costs, and expenses, including any legal fees potentially resulting from the application of any of the information provided by this book. This disclaimer applies to any loss, damages or injury caused by the use and application, whether directly or indirectly, of any advice or information presented, whether for breach of contract, tort, negligence, personal injury, criminal intent, or under any other cause of action.

You agree to accept all risks of using the information presented inside this book.

You agree that by continuing to read this book, where appropriate and/or necessary, you shall consult a professional before using any of the suggested remedies, techniques, or information in this book.

Chapter 1. SUM

We all need to pay bills. Suppose you use 10 utilities (rent, electricity, Wi-Fi etc.). And you need to pay bill for that. And now you want to know how much did you spend on that per month. Here excel will save your time. You don’t need to input the numbers 10 times in a calculator.

The Excel SUM function returns the sum of values supplied as multiple arguments. SUM can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

Syntax

=SUM (number1, [number2], [number3],)

Arguments

•      number1 - The first item to sum.

•      number2 - [optional] The second item to sum.

•      number3 - [optional] The third item to sum.

Usage notes

1.      Select the numbers you want to add. You can do it individually by clicking each cell or drag them with mouse from top to bottom (shown in this pic).

2.      Hit Enter and you will see the sum.

The keyboard shortcut for sum is (Alt) + (=)

SUMPRODUCT

Suppose you bought 4 products in many quantities. This is time consuming to multiply unit with price and the add them together. Excel has a built in formula for that.

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility. Other functions can easily be used inside SUMPRODUCT to extend functionality even further.

Syntax

=SUMPRODUCT (array1, [array2], ...)

Arguments

•      array1 - The first array or range to multiply, then add.

•      array2 - [optional] The second array or range to multiply, then add.

How to use SUMPRODUCT in Excel:

Select array 1 then multiply with selected array2 and press enter.

How to do this calculation manually:

(3*2) +(5*1.50) +(1*60) +(2*10.5) =94.5

Usage notes

The SUMPRODUCT function works with arrays, but it doesn't require the normal array syntax (Ctrl + Shift + Enter) to enter. The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

Chapter 2. Vlookup

Vlookup is vertical lookup. Vlookup is used to find something from a vertical list or table. Suppose there are three sales rep. we want to know how much commission they will get if they achieve certain category.

VLOOKUP is an Excel function to look up and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.

Syntax 

=VLOOKUP (value, table, col_index, [range_lookup])

Arguments 

Value - The value to look for in the first column of a table.

Table - The table from which to retrieve a value.

Col_index - The column in the table from which to retrieve a value.

Range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

EX 1: Approximate match

Ex 2: Exact match

How vlookup works

Step 1: In the Ex 1 the look up value is $5356.00. Vlookup formula will approximately match this value in the table array column named sales. In ex 2 vlookup will find exact match and will return the desired value (must care for empty spaces otherwise excel won’t find the exact match).

Step 2: When vlookup finds a bigger value than $5356.00 (in the ex 1 which is $6000) It will automatically jump back to the previous row and put the value of corresponding column to the cell. In exact match it will return value If the name is exactly matched.

Important Note: There are two types of range lookup

1.Approximate match:(Here we used approximate match in ex 1. For approximate match we will put 1 or true in the range look up value). For approximate match data must be in ascending order.

2.Exact match: For Exact match we will put 0 or false in the range look up value.

Other notes:

Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.

Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.

If range_lookup is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.

When range_lookup is omitted, the VLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.

If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.

If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.

Hlookup

Though vlookup is mostly used but HLOOKUP is used when the table is horizontal.

Hlookup is horizontal look up where lookup values appear in the first row of the table, moving horizontally to the right.HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

Syntax 

=HLOOKUP (value, table, row_index, [range_lookup])

Arguments 

Value - The value to look up.

Table - The table from which to retrieve data.

Row_index - The row number from which to retrieve data.

Range_lookup - [optional] A Boolean to indicate exact match or approximate match. Default = TRUE = approximate match.

Usage notes 

HLOOKUP searches for a value in the first row of a table. At the match column, it retrieves a value from the specified row. Use HLOOKUP when lookup values are located in the first row of a table. UseVLOOKUP when lookup values are located in the first column of a table.

Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.

Set range_lookup to FALSE to require an exact match.

If range_lookup is TRUE (the default setting), a non-exact match will cause the HLOOKUP function to match the nearest value in the table that is still less than value.

When range_lookup is omitted, the HLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.

If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, HLOOKUP may return an incorrect or unexpected value. If range_lookup is FALSE (require exact match), values in the first row of table do not need to be sorted.

Lookup

The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. LOOKU P's default behavior makes it useful for solving certain problems in Excel.

The Excel Lookup function has two forms: The Vector form and the Array form.

Syntax Vector form

=LOOKUP (lookup_value, lookup_vector, [result_vector])

Arguments 

lookup_value - The value to search for.

lookup_vector - The one-row, or one-column range to search.

result_vector - [optional] The one-row, or one-column range of results

Ex 1: Vector form

Explanation:

1. We want to know at which position product 6 is located. So we set product 6 as lookup value.

2. The lookup vector is in ascending order. (Product 10 comes before product 2) we need to finish all 1’s then all the 2’s.

Syntax array form

=LOOKUP(lookup_value, array)

Arguments

lookup_value-The value that you want to look up.

Array-A 2-dimensional array (or table) of data, containing values to be searched in the first row or column and values to be returned in the last row or column.

Ex 2:Array form

Explanation:

We want to know the price of the pen. So we will set lookup value pen and lookup function will give its value.

Usage notes array form

The shape of the array - whether it is square (equal number of columns and rows) or a rectangle (unequal number of columns and rows) - affects where the LOOKUP function searches for data:

If an array is square in shape or if it is a tall rectangle (taller than it is wide), LOOKUP assumes that the data is arranged in columns and therefore searches for a match to the Lookup_value in the first column of the array.

If an array is a wide rectangle (wider than it is tall), LOOKUP assumes that the data is arranged in rows and therefore searches for a match to the Lookup_value in the first row of the array.

Usage notes vector form

Use the LOOKUP function to look up a value in a one-column or one-row range, and retrieve a value from the same position in another one-column or one-row range. The lookup function has two forms, vector and array. This article describes the vector form.

LOOKUP has default behaviors that make it useful when solving certain problems (i.e. retrieve approximate matched value instead of position, find the value in the last non-empty cell in a row or column, etc.). LOOKUP assumes that values in lookup_vector are sorted in ascending order and always performs an approximate match. When LOOKUP can't find a match, it will match the next smallest value.

LOOKUP assumes thatlookup_vector is sorted in ascending order.

When lookup-value can't be found, LOOKUP will match the next smallest value.

When lookup_value is greater than all values in lookup_vector, LOOKUP matches the last value.

When lookup_value is less than all (i.e. the first) value in lookup_vector, LOOKUP returns #N/A.

result_vector must be the same size as lookup_vector.

LOOKUP is not case-sensitive.

Match

Suppose we want to know the row or column position of a cell value. So why do we need to know that? It will help to do calculation In INDEX. VLOOKUP, IF and in many other functions.

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Syntax 

=MATCH (lookup_value, lookup_array, [match_type])

Arguments 

lookup_value - The value to match in lookup_array.

lookup_array - A range of cells or an array reference.

match_type - [optional] How to match, specified as -1, 0, or 1. Default is 1

Explanation:

1. We want to know the relative position of product 3. Which column or row product 3 is located?

2. We select lookup_array as the products list and do an exact match. The relative position of product 3 is in the row

Usage notes 

Use the MATCH function to get the relative position of an item in an array. Match offers several different matching modes, which makes it more flexible than other lookup functions. Used together with INDEX, MATCH can retrieve the value at the matched position.

Match type information

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.

If match_type is 0, MATCH finds the first value exactly equal to lookup_value. lookup_array does not need to be sorted.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.

If match_type is omitted, it is assumed to be 1.

Match is not case-sensitive.

Match returns the #N/A error if no match is found

The argument lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.

If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters’ asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Index

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

Syntax 

=INDEX (array, row_num, [col_num], [area_num])

=INDEX (reference, row_num, [col_num], [area_num])

Arguments 

array - A range of cells, or an array constant.

row_num - The row position in the reference or array.

col_num - [optional] The column position in the reference or array.

area_num - [optional] The range in reference that should be used.

Explanation:

1.We want to know how much student 5 got In physics. So we first type index and select array (all the numbers).

2.Forrow_num we can give 5 or use match function (it is easier to use match function when the list is long.)

3. For column_num we can give 4 or use match function (it is easier to use match function when there are many column).

Usage notes

Use the INDEX function to get a value from a list or table based on its location. For example, the formula =INDEX(A1:B5,2,2) will return the value at the address B2.

The INDEX function has two forms: array and reference.

Array form

In the array form of INDEX, the first parameter is array, which is supplied as a range of cells or an array constant. The syntax for the array form of INDEX is:

INDEX (array, row_num, [col_num])

If both row_num and col_num are supplied, INDEX returns the value in the cell at the intersection of row_num and col_num.

If you row_num is set to zero, INDEX returns an array of values for the entire row. To use these array values, enter the INDEX function as an array formula in horizontal range.

If you col_num is set to zero, INDEX returns an array of values for the entire column. To use these array values, enter the INDEX function as an array formula in vertical range.

Reference form

In the reference form of INDEX, the first parameter is reference, which is supplied as a reference to one or more cell ranges. The syntax for the reference form of INDEX is:

=INDEX (reference, row_num, [col_num], [area_num])

The reference form of INDEX returns the reference of the cell at the intersection row_num and col_num.

If reference is supplied as multiple ranges,  area_num indicates which range to use.

area_sum is supplied as a number. For example, in the formula =INDEX((A1:C5,A7:C10),2,2,2), area_num is supplied as 2, which refers to the range A7:C10.

Chapter 3. IF

A teacher teaches many students. He takes tests after certain period of times and puts the marks in excel sheet. How to know if a student has passed or not? Easy, passing score. But it is hard to match every students number with the passing score manually. Excel can do this easily using a formula called if.

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to pass scores above 45: =IF(H10>45,Passed,Failed). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.

Syntax 

=IF (logical_test, [value_if_true], [value_if_false])

Arguments 

logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.

value_if_true - [optional] The value to return when logical_test evaluates to TRUE.

value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Explanation:

Here logical test is If the result is greater than or equal to passing mark the student will regarded as passed. If the result is lesser than or equal to passing mark the student will regarded as failed.

Usage notes 

Use the IFfunction to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.

For example, let's say you want to assign either Pass or Fail to students based on a test