| How to locate the formula in a worksheet |
| How to go back from looking formula to the normal sheet |
| Shortcut for a quick print preview |
| How to print column and row numbers in the sheet |
| How to highlight the cells that contain formulas |
| How to highlight the cells that DO NOT have formulas |
| How to find the formula in the sheet using formula bar |
| How do we check all the dependencies of a cell |
| How to remove the dependencies arrows |
| How to trace from where the formula is coming |
| Shortcuts to highlight dependent cells affected by the value of cells but not arrows |
| Shortcuts to highlight precedent cells to the value of cells but not arrows |
| How to add two different rows |
| Autosum |
| How to make the active cell same after you have entered the formula |
| How to write a formula in one cell and convert it into value for the whole column |
| How to find flash fill in excel |
| How to convert a formula to value by dragging |
| How to convert a formula to value by dragging left or right |
| How to convert a column with a formula by dragging on to that column |
| How to update values without formula using paste special |
| Debugging Formula with the F9 Key |
| How named cells will help you in writing formula |
| How you can avoid using f4 key to fix an absolute address |
| You can use sum and average better using range name |
| How to delete a range name |
| How to show all the range names with ranges on the worksheet |
| How to summarise data from multiple identifcal sheet by clicking |
| How to summarise data from multiple identifcal sheet: another method: faster |
| Short cut for autosum |
| How to use autosum in a different ways |
| shortcut for format cell |
| How to apply a formula to all cell in the range at once |
| Tips for using long functions |
| Using IFS as a nested excel function |
| Use the full column or rows for V or H look up |
| Using H lookup |
| for less than or equal to lookup, the lookup table must be in ascending order |
| If you want to use vlookup the table better be trimmed before lookup |
| A typical example where you can use nested lookup |
| Using CHOOSE function to convert a date into quarter |
| Using the SWITCH function for formula embedded selection |
| To see if a number exist in some other list |
| How to use index function |
| Using Index and Match together |
| How many entries contain "Time" using COUNTIF |
| How to use filter to find entries containting Tim using wildcard symbol |
| How to use COUNTIFS with criteria eg. >3 |
| How to use AVERAGEIFS with crieteria eg. > "Entry in some other cell" |
| How to find the maximum values by category |
| How to find the max values by category and greater than some date |
| How to total many sub ranges at one time |
| How to display the text of the formula |
| How to prevent double counting using SUBTOTAL |
| How to rank the entries with highest as First |
| How to rank the entries with highest as Last |
| How to rank the entries with the rank of common of those as the average |
| How to find the 2nd highest, 3rd highest 4th highest etc |
| How to count the blank cells |
| If you are getting stuck because there are blank cells in the table, convert it into table |
| COUNTA counts the number of cells that have data |
| COUNT counts the number of cells that have numbers |
| How to use the ROUND function |
| Don't use the increase decrease decimals a the tab for rounding |
| HOW to USE ROUNDUP or ROUNDDOWN function |
| How to express a number as a multiple of 100 |
| Working with MROUND, CEILING and FLOOR for Specilized rounding |
| MROUND allows us to round to the nearest for example 05 |
| CEILING allows us to round up to the nearest for example 05 |
| FLOOR allows us to round down to the nearest for example 05 |
| How to make a number that is divisible by eg. nearest 26 |
| How to make a number that is divisible by nearest 26 UP |
| How to make a number that is divisible by nearest 26 DOWN |
| The difference between TRUNC and INT function is that the INT function always returns a lower value |
| ODD number is the nearest odd number moving away from zero |
| EVEN converts to nearest even number moving away from Zero |
| How to use MOD function |
| How to highlight every nth row in the worksheet |
| How to pull out 5 records at random |
| How to sort a sheet in the random order |
| How to generate data using whole numbers |
| How to turn the volatile random numbers into fixed ones |
| How to create random dates between two time frames |
| How to change the values of random numbers |
| How to use CONVERT |
| How to find average etc of data which have errors |
| How to show the aggregate ignoring hidden rows |
| Using the Roman and Arabic functions to display different number systems |
| The underlying concept is every date has a value starting from Jan 1st 1900 |
| How to check the value of a date or time |
| How to create a date out of a year, month and a day |
| How to split a date into year, month and day |
| How to create a time out of hour, minute and second |
| How to split a time into hour, minute and second |
| Shortcut for putting today's date |
| Shortcut for putting today's time |
| How to write the current time |
| Today and now changes the cell dynamically |
| Comma button changes dates to days |
| Using data type button and selecting numbers changes days to dates |
| How to use DATEDIFF to count the number of Years, Months or days between two dates |
| How to find the day of the week from date using formatting |
| How to find the day of the week from date using WEEKDAY |
| Which number is which day of the week |
| How to change the number to day of the week using formatting |
| How to change a date that excludes weekdays |
| How to change the format of the adjacent cell by dragging |
| How to find the number of days between two dates not taking into consideration weekends |
| How to take holidays into account when finding the number of days between two dates |
| Remember Network days take into consideration first date and last date |
| Simple Difference between two dates doesn't take into considertation one of the start or end date |
How to use NETWORKDAYS if the Sat and Sunday are not weekends, but some other combination or single |
| How to find the end date given a starting date and a period and given weekends |
| How to find the difference between two dates in years, months,days, hours,minutes and seconds |
How to find the difference between a date and today in years, months,days, hours,minutes and seconds |
| How many days have passed since the last yearly anniversary |
| How many months have passed since the last yearly anniversary |
| How many days have passed since the last monthly anniversary |
| In DATEDIFF you don't get any tooltip |
| EDATE takes care of 30 or 31 days in a month |
| If there is a start date then after a period what will be the end date |
| How to get the end of the month date |
| How to get to the start of the next month date |
| If there is a end date then after a period what is the start date |
| How to find the last day of the current month |
| How to find the last date of the month immediately gone using EMONTH |
| How to find the last date of the month immediately gone using today and day(today()) |
| How to do sum product using Array formula or find weighted average |
| How to find the average of the difference itemwise |
| What is the average amount of increase itemwise in the column |
| Counting Unique Entries in a range using ARRAY formula |
| Counting Unique Entries in a range using ARRAY formula even easily by converting it into a table |
| Determining frequency distribution with the FREQUENCY function |
| How to shift a row dominant matrix into a column matrix with all the formulas intact |
| How to add a TREND function to a series |
| How to add GROWTH function to a series |
| How to update last date or last value in a dynamic table using OFFSET |
| Find out the averge of last n values using OFFSET in a dynamic table |
| Returns the reference specified by a text string |
| How to create the two tiered pick list for data validation |
| How to get rid of blanks in the data validation list |
| How to find those sentences that have letter G in them |
| Search in a column how many times in one cell letter G is present |
| How to use Flash fill to extract the first two letters of cells in a column |
| How to use MID and FIND together to extract words in between the cells in a column |
| How to get everything in the left of comma |
| How to use CONCATENATE with ISBLANK |
| How to concatenate using a range |
| How to concatenate using a delimiter |
| REPLACE allows us to replace information basis the positition |
| How to replace 5th character with X |
| How to substitute all dashes with X |
| How to format a number as text with leading zeros |
| How to repeat a letter X five times |
| How to use TEXT, REPT and LEN to create an exactly 6 digit code |
| How to use CELL to write filename of the current file that we are using |
| How to find if the type is lower or upper case using TYPE |
| How to find the operating system version |
| How to see the directory using INFO |
| How to check if a cell contains a text or number |
| ERROR function refernece |
| How to find which cell contain a formula |
| How to highlight all cells whcih contain formula or which may contain formula |
