Our 1,000+ page Microsoft Excel Reference book contains over “625 Excel Questions and Answers” is now available to download for Free.
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.
Table of contents:
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
Business Calculations:
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 to add Comments to a Formula? 260
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 add Comments to a Formula? 308
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 to use the Quick Access Toolbar? 340
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
Absolute Cell Reference Addressing 748
Mixed Cell Addressing 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
How to add new data to your Pivot table? 910
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
Spreadsheets: 935
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