It  has hundreds of  answers to common Microsoft Excel questions, sample formulas  and calculation solutions  which you can adapt for your own use saving you tons of time when using Excel or studying excel online to get an excel certificate.

Our Microsoft Excel Reference book  covers all of Excel’s main  techniques and functions especially  If, nested Ifs, Vlookup, Index, Match, Sumif, Sumifs, Countif, Countifs, Sumproduct, Offset, Date & Time Functions and Text Functions. including Pivot tables and data analysis. See full contents below.
It’s an indispensable guide  to learn excel online or when doing online excel classes with certification . Its one of the best ways to learn Excel online.

### Array Formulae:

How to  rank values , even when they are ties ?  17

How to move a vertical range of cells into a horizontal range of cells?       22

How can I add cells that have conditional formatting only?            23

How to  find the Biggest/Lowest  Quantity in a list ?          27

How to  Look up Multiple Index columns?             29

How to  Look up a Value based on  Column and  Row ?    31

How to Look up a Text and Number in two  columns in Excel using Arrays?             33

How to find the total number of all possible combinations for a given number of items..  35

When do you need to use brackets in Formulas. 38

How to combine reports that are not the same-can I still use the Consolidate Tool ?           40

How to count items in a list based on multiple conditions?            44

How to calculate percentage markup on selling price?     49

How to calculate the  Markup percentages  on Cost?        50

How to create a basic Markup formula? 51

How to  calculate a  running  Percentage?             53

How to calculate  permutations in Excel ?              60

How to Round up or down to nearest 5? 62

How to round prices up or down  to the nearest €10 ?     63

How to Roundup and Round Down numbers ?    65

How to round numbers in Excel?               67

How to create a two input data table in Excel?    68

How to create a running total formula in Excel?  71

How to create a tiered commission plan?              73

## Calculations:

How to find the total number of all possible combinations for a given number of items.   75

How can I rank values in Excel?  78

How to  create a random number between 0 and 1 in Excel ?       80

How to multiply a group of numbers together in Excel ?  81

Is there a function in Excel to find  Percentages ?               83

What does the  NOT Function do?            84

How to round up or round down a number to nearest user defined multiple ?      84

How to returns the nth largest value from a set of values ?            85

How to check whether a number is odd in Excel ?              86

How to determine whether a cell entry  is a numeric value?          88

How to use the ISLOGICAL Function ?      89

How to find out if a number is even or not ?         89

How to round a value down to the nearest whole number ?          91

How to convert  text into usable cell reference ? 92

How to compare data ranges with intervals in Excel ?       94

How to use the  FORECAST Function in Excel ?     96

How to round  a  value down to the nearest  user defined multiple ?         98

How to get a  text representation of a number , rounded to a specified number of decimal places?             99

How to return the factorial of a number in Excel 101

How to round up a number to nearest Even whole number?        102

Using the DAVERAGE Function   103

How to calculate  depreciation based on fixed percentage ?          105

How to convert a number into a currency format ?            107

How to calculates the difference between two dates ?    109

How to convert values to similar  measurements in Excel ?            110

How to count only certain items in a list in Excel 111

How to count the blank cells in a column or row ?              112

How can I count the number of cells that have values ?   113

How to converts normal numbers to ANSI characters       114

How to get details about specific cells in Excel?   116

How can I round of number to the nearest multiple ?       118

When do you need to use brackets in Formulas? 119

The AGGREGATE Function in Excel 2010  121

How to generate unique random numbers that don’t repeat?      126

How to calculate  percentage markup on selling price?    131

How to calculate the  Markup percentages  on Cost?        132

How to  calculate a  running  Percentage?             132

How  to use the SUMPRODUCT with multiple conditions?              135

How does the SUMPRODUCT function work ?      140

How does the SUMIFS function work?    144

How to  rank values , even when they are ties ?  148

How does the CONVERT Function work ?               152

How to count the numbers of rows in a range?   156

How do I convert units of measurements in Excel?            157

How to calculate  permutations in Excel ?              160

How to calculate the number of combinations using Excel?           161

How to name Constants ?             163

In what order does Excel perform calculations?   165

How to round prices up or down  to the nearest €10 ?     167

How to RoundUp and RoundDown numbers ?     168

How to round numbers in Excel?               171

How to rank values in Excel ?      172

How to create a running total formula in Excel?  174

In Excel is there a quick way to summarize lots of worksheets?   176

How to create a tiered commission plan?              177

How to  consolidate / summarize  worksheets quickly ?  179

My Formulas are not recalculating. Why?              180

How can I add certain cells that are colored?       181

How to change Text numbers to Real numbers in Excel? 183

## Common Excel Problems:

My number is text, how can I convert it to a number?     184

How to copy Formulas without them changing.   186

Using the TRANSPOSE Function 189

How do I find the day of the week from a date?  191

How to find the second or third or nth smallest value in a list of values    192

How to your formulas visible-is there a shortcut?               193

How to replace text with another text ? 194

How can I rank values in Excel?  195

How to convert the first letter of text to capital letter and rest of the letters to lower?      196

How can I test for one or more  conditions in Excel ?        197

How to returns the nth largest value from a set of values?             198

How to find out if a number is even or not?          199

How can I tell if a cell contains a value?  200

How to convert  text into usable cell reference? 201

How to find exact position of a character in a text string?               203

How to display the name of the current workbook?         204

How can I check if both text strings are exactly the same?              205

How to round up a number to nearest Even whole number?        206

How to test for more than one condition in Excel?             207

How to keep a workbook in the recent Document list?     210

How to protect Excel formulas but still allow users to sort, format etc.?   211

How to hide formulas in Excel?  212

How to protect formulas in Excel?            214

How to enter data quicker by using Excel’s built-in Data Entry Form?        216

How to protect cells that contain formulas?         219

How to combine reports that are not the same-can I still use the Consolidation tool?         223

How to use the Excel tool for consolidating data?              226

I can’t get an Ampersand in my Header and footer, why not?       231

How to use the 3 different wild characters ( *, ?,~) when using Find & Replace?   233

How to create double Space inside my data?       237

How to delete Blanks rows from a Range but keep the original order?      242

How to remove blank rows from a range?             248

How to convert a range of cells into a table?        250

How to combine formulas into a single formula? 256

How can I get unique items from a List of Cells    262

Sort cells by colour in Excel          264

Sort data into a certain custom order.     266

How to rename a Table?               268

In Excel, How can I fill Blanks?     269

How to remove leading and trailing spaces?         272

How to Create a Two input data table ?  273

How to dynamically choose a specific value from a list?   277

How to count the numbers of rows in a range?   278

How to calculate the number of combinations using Excel?           279

In Excel how do I fill blank cells with Zero?            281

In Excel How do I Parse Multi-Line Cells that were created by using ALT+Enter?   283

How to convert formulas to values in Excel?         285

## Essential Excel Primers

Essential Excel: The OFFSET Function.      287

Using Offset function in a SUM Function.               290

In Excel how do I Convert Numbers to Text?        292

In Excel, what does the Quick Sum indicator in the status bar do?               295

How to name Constants? Formulas are the combination of dynamic values and constants.             296

How to Name cells to create Named Ranges?      298

When adding a new record to my lookup table, I am getting an #N/A Error?          300

How to hide or trap  errors in Excel 2001/2010 ? 302

Sort data with pictures. 303

How to copy the totals from subtotal report?      305

How can I get multi line text into one cell in Excel?            307

How to copy hundreds even thousands of formulas quickly in Excel?         310

How to copy a list of custom items using the fill handle? 311

What is the difference between Excel Date Function and DateValue Function?     312

How to move a vertical range of cells into a horizontal range of cells?       313

How to Round up or down to nearest 5? 314

How to create a two input data table in Excel?    315

How to build an amortization table in Excel?        317

What is the intersection operator?           319

How to change from the A1 style to the RICI reference style?       320

In Excel, how can I create a dynamic cell address?             322

In Excel is there a quick way to Summarize  or consolidate  many  worksheets?    323

In Excel 2007/2010, how do I get back to using the Excel 2003 dialogs?    324

How to access Formatting Options Using the Mini Toolbar?           325

Access Formatting Option            325

Use the grouping method to change cells in many worksheets    326

The new three-panel Backstage View in Excel 2010?          327

How to increase the number of workbooks visible in the Recent Files List ?             329

When I try to close the Excel 2010 File Menu, my document closes. Why?              331

How do I share Excel files with users who are using earlier versions of Excel?         332

What is the difference between all these Excel file extensions – .xls, .xlsx, .xlsm, .xlsb, .xlam and .ods file types?    334

How do you use the keyboard to access Excel commands?            336

How can I hide the ribbon or part of the ribbon? 343

How to  convert column number to letter?           344

How to change the column width to an exact size?            345

Make an exact copy of a formula range  without changing the  cell references ?  346

What is the best way to summarize the totals of each expenditure for the whole week ? 347

My Formulas are not recalculating. Why?              348

Having problems editing range name in edit box.               350

Help, my Vlookup not working, even those the values look the same?      351

How to check for values in your whole workbook ?           353

The Find and Replace Tool in Excel is not working ?           354

Are there any alternative to using lots of IF’s in Excel? Yes, the Choose Function  355

## Conditional Formatting:

In Excel, how do I display up/down arrows?         356

How can I add cells that have conditional formatting only?            364

How to create an Event calendar with formatting in Excel?            367

How can I apply formatting to a whole row  of a table  ?  371

How do I highlight payments that are due in the future ?                373

How to use Excel to check lottery numbers?        374

How to highlight certain items in a list ? 376

How to use  Excel conditional formatting to highlight duplicate entries in a column ?         377

How to flag certain conditions using Excel conditional Formatting ?           379

How to use conditional formatting to hide errors ?            381

## Data Analysis:

What does the  HLOOKUP Function do ? 382

How to compare data ranges with intervals in Excel ?       384

How can I dynamically choose a specific item from a list ?              385

The AGGREGATE Function in Excel 2010  386

How to  enter data quicker by using Excel’s built-in Data Entry Form?       391

How to find differences in two lists?         393

How to use the Advanced Filter?               399

How to convert a range of cells into a table?        404

How to get the last entry in a large  column of data?        410

How to add the visible cells when filtering?          415

How to count items in a list based on multiple conditions?            420

Find Unique Items in a List            425

How to Format Subtotal Rows.   427

Add  Text To  Subtotals in Excel  430

How can I Subtotal By Two Fields/Categories       433

How to join up two tables ?         436

How does the SUMPRODUCT function work ?      441

How does the SUMIFS function work?    445

How to sum records that match a Criterion ?       448

How to  count  records  that match  a criterion ? 454

How does the Goal Seek Tool  work?       457

How does the Scenario Manger tool work ?          459

How to merge  two lists into one unique list using advanced Filter?           464

How to  combine two lists into one unique list ? 465

## Dates:

How to calculate  past or future dates ?  469

How to  calculate the number of working days between two dates ?         470

How to find the last day of a month in Excel ?      472

How to find a date that is a certain numbers of months in past or future ?              473

How to calculate the days between two dates based on 360 days year ?  474

How to convert  text into a date for calculations ?              475

How to calculates the difference between two dates ?    476

An Excel Age Calculator  478

How to return the Date of the N-th Occurrence of a Weekday in a Month?            479

How to  calculate the number of years between two dates?         481

Calculate difference between 2 dates by years, months, days or hours.    484

Does a date fall on a Saturday or Sunday?             487

How to  find the last day of the month ? 488

How to find a date that is a specified number of working days from some starting date?  489

How to  determine the Day a certain Date fell on?             490

How to  count the number of Days between Dates?         492

How to  separate parts of a date?             493

Parts of my Dates are in different columns. How can I get them in one cell?           494

What is the difference between Excel Date Function and DateValue Function ?    497

How can I change from US Date formats to European Date formats in Excel?         498

How do I highlight payments that are due in the future ?                499

How to find the newest Date/Time in a list?          500

How to look up a date using Vlookup Function?  503

I always want to have the current date in my workbook. How can I do this?           504

## Dropdown Boxes

How to fill a dropdown list box with different lists ?          505

## Duplicates in Excel:

How to get unique items from a List ?     507

How to use Excel to check lottery numbers?        510

How to use  Excel conditional formatting to highlight duplicate entries in a column ?         512

How can I Subtract Times in Excel?           516

## Essential Excel:

Using the INDIRECT Function.     517

The Indirect Function     517

Essential Excel: The OFFSET Function.      520

Using Offset function in a SUM Function.               522

Essential Excel: The Powerful INDEX & MATCH Functions in Excel.              524

The Index & Match Function:      524

The Important MATCH Function:               526

Excel Essentials: Using Dates in Excel.      530

Excel Essentials: Understanding custom number formats in Excel                532

Excel Essentials:How can I test for two or more conditions in Excel?          537

Excel Essentials: The IF Function  and nested IF Functions ?            541

The IF Function: 541

IF Function Example        541

Nested IF Function Example:       543

How can I name a cell or  range of cells in Excel? 544

## Excel Errors

How to test if a cell contains #N/A            551

How can I hide the ribbon or part of the ribbon? 746

How do I insert /Delete rows and columns in Excel?          747

Deleting Columns             747

Inserting Columns            747

Can you explain what absolute reference means  in Excel?             748

What is the point method in Excel?          749

The Point Method:          749

How to change the column width to an exact size?            751

How in Excel can I put a worksheet name in a cell?            752

How can I print the titles at the top of each page in Excel?             754

How to Create a two or more Column text box ? 756

Is there a quick way in Excel to number from 1 to 100?    757

Check if values have changed from comparing lists in Excel?         758

Is there a quick way to enter all the months of the year in Excel? 760

How to check for values in your whole workbook ?           761

The Find and Replace Tool in Excel is not working ?           762

How can I save my workbooks so that they are compatible with older versions of Excel?  765

How do I hide or show the Ribbon in Excel 2007/2010?    766

Where is the Options command in Excel 2007-Excel 2010?             767

Can’t find the developer tab on Excel 2007-2010 768

Where is the Developer ribbon In Excel? 769

I can’t find certain Excel commands         770

Filter:    771

How to copy the totals from subtotal report?      771

How to quickly subtotal sections of data-The Subtotal Tool.           774

Add Only The Visible Cells In Filtered Data.            778

How can I add certain cells that are colored?       780

IF Function:

How can I test for one or more conditions in Excel?          781

Excel Essentials: How can I test for two or more conditions in Excel?         782

Excel Essentials: The IF Function  and nested IF Functions ?            785

The IF Function: 785

IF Function Example        786

Nested IF Function Example:       787

Are there any alternative to using lots of IF’s in Excel? Yes, the Choose Function  788

## Lists:

How can I dynamically choose a specific item from a list?               789

How to find differences In two lists?        790

How to remove blank rows from a range?             796

How to get the last entry in a large column of data?          798

How to count items in a list based on multiple conditions?            802

How to dynamically choose a specific value from a list?   808

How to compare two lists and note the differences between values?       810

How to merge two lists into one unique list using advanced Filter?             812

How to combine two lists into one unique list ?  814

How to count  the unique values in a range?        817

How to find the newest Date/Time in a list?          818

How to find the Biggest/Lowest  Quantity in a list ?           820

How to Look up Multiple Index columns?              822

How to Look up a Value based on  Column and  Row ?     825

## Macros:

How to create a Macro? 826

How to share macros with others?           835

Numeric Values:               838

How to convert text that looks like a number into an actual value?            838

Is there a quick way in Excel to number from 1 to 100?    839

How to change Text numbers to Real numbers in Excel? 840

How to find text numbers and change them to real values?           841

How do get the second or third largest, smallest value?  842

## Pivot Table:

How to do calculations outside the Pivot Table that refer to values in the pivot table?       843

How to create two Pivot Tables that do not share the same Pivot Table cache?     845

How to make 2007/2010 Pivot Tables compatible with previous versions?             849

How to create a Pivot Table  that  shows the Min, Max, Average and count for my data?  850

How to use the ‘show value as’ feature  in Pivot tables?  853

How to create a unique list with a pivot table?    856

How to create an Excel pivot table from Access database?             857

What is Power Pivot?     858

How to fix wrongly spelled names in a  pivot table?           859

How to add a Calculated Item to Group Items in a Pivot Table?    860

What does the Report Filter area of the Pivot Table do?  863

How to create Calculated Fields in Pivot Tables? 863

In Pivot tables, how can I group data by age-groups?       867

How to create charts in pivot tables?       869

How to create a report for every customer/product from a pivot table?   871

What does the Report Filter area of the Pivot Table do?  874

How to  use the Filter feature in Pivot Tables?     876

How can I group my customers in my Pivot table?             877

How to see the top items in a Pivot Table?            880

How can I group pivot table data by year?             883

How to group  by date in Pivot tables?    885

How to sort data in a pivot table?             888

How to re-arrange the Order of Data in Pivot Tables?       891

How to use Conditional Formatting in Pivot Tables?          894

How to format certain sections of a  pivot table?                897

Formatting Pivot tables?               899

How to show Yes/No in Pivot Table?        901

In pivot tables, how to keep the column widths from  changing? 903

How can I change the number format of a Pivot Table Field?         904

How can you expand or collapse pivot table fields?           906

In Pivot tables  how  to replace blanks with zeros ?            907

How can I change my pivot table to the other layouts available?  909

Pivot tables data is not updating, why?   911

How can I change pivot table  to different layouts?           912

Where did the Pivot table get that number from, Can I find out? 918

How can I convert my pivot table to ordinary  data ?         920

My Pivot Table Field List box is not visible.            921

How can I summarize my Pivot table data using three conditions?              922

How can I add or delete fields from a Pivot Table?             924

How can I use pivot tables to summarize data?   926

Will Pivot tables update from Excel table?             929

## Printing:

How  to print a page number on each page ?        930

How do you add a Watermark to each worksheet ?          933

How to change a Wide Report Fit to One Page Wide by Many Pages Tall?                934

How to create an Event calendar with formatting in Excel?            935

How to use Excel to check lottery numbers?        939

Text:      941

How  to substitute a specific piece of text with another piece of text ?      941

How to convert text that looks like a number into an actual value ?            944

How to convert text to uppercase letters?            945

How to delete leading and trailing spaces?           946

How to convert numbers to text format?               947

How to replace text with another text?  948

How to convert the first letter of text to capital letter and rest of the letters to lowercase letters. 949

How to convert characters to lower case letters?               950

How to count the number of characters in text string?     951

How to extract a certain numbers of characters from the left side of text string?  952

How to check if a cell entry is  text ?         953

How to check whether the value in the cell is a text or a number?              954

How to find exact position of a character in a text string?               955

How can I check if both text strings are exactly the same?              956

How to add text together In Excel?           957

How to get the ANSI value of the characters in Excel?      959

How to remove all nonprintable characters from a string?              961

How to create double Space inside my data?       962

How to remove leading and trailing spaces?         966

How can I save Excel data as a text file?  967

When I enter text in my Formula, I getting an #Name error.          968

How to lookup text that is case sensitive using Sumproduct?        969

How do I lookup text that is case sensitive?          971

How to search for text containing certain characters using the Vlookup function?               974

How to Create a two or more Column text box ? 977

Can I use the Fill handle in Excel to get the alphabet A, B, C etc?  978

How to find text numbers and change them to real values?           979

How to get rid of spaces in Excel?             980

How to change the case of characters in Excel?   981

How to  extract the first  part of a text string?      982

=left(“Mary Kate Ryan”,find(” “,”Mary Kate Ryan”,)-1)     982

=left(“Mary Kate Ryan”,4)            983

Time:

Convert a Text string to a Time In Excel   983

How to round Times in Excel.      985

Essential Excel: Excel Time arithmetic.     986

How can I change text into a Time in Excel?          990

How can I show the current time in Excel?            991

How to return the hour part of a Time Value in Excel ?     992

How to convert Excel Time to decimal hours?      993

Vlookups:

When adding a new record to my lookup table, I am getting an #N/A Error?          994

I don’t want my Vlookup to return #N/A but blanks?        997

How to  Look up Multiple Index columns?             1000

How to Look up a Value based on  Column and  Row ?     1002

Using Vlookup to find a decimal number in Excel?             1003

How to Look up a Text and Number in two  columns in Excel using Arrays?             1005

How can I Look up a Text and Number in two columns ? 1007

How to lookup text that is case sensitive using Sumproduct?        1009

How do I lookup text that is case sensitive?          1011

How to search for text containing certain characters using the Vlookup function?               1013

How to look up a date using Vlookup Function?  1015

How can i get the Vlookup function  to return null or a blank instead of #N/A ?    1016

Can’t use Vlookup, as value is to the right ?           1017

Web:

How to get data from a web page using static tables?      1019

What If:

How to test for three conditions in Excel?             1020

How does the Goal Seek Tool  work?       1023

How does the Scenario Manger tool work ?          1025