How to depict an Infinite Series in Excel

 1. How to depict summation of 1/(2^n)

Here we assume that n is from 1 to 100. So we write in the formula

=sum(1/2^row(1:100))

Dynamically filter data from one worksheet to another

 1. Select range A1:O82 and press Ctrl+F3 > New > Name.  In the Name box, enter Dummy and click on OK.

2. Select range A1:O82 and press Ctrl+T.  Ensure that the "My data has headers" box is checked and click on OK
3. Save the file (assume on the Desktop for now)
4. Open a blank worksheet and go to Data > From Other Sources > From Microsoft Query
5. Select Excel files and click on OK
6. In the right had side panel, navigate to the folder where you saved the file in step 3 (Desktop in this case).  In the left hand side panel, select the specific Excel file which you saved in step 3 and click on Next
7. With Dummy appearing in the left hand side panel, click on the > symbol to transfer all columns of Dummy to the right hand side panel and click on Next
8. In the column to Filter box, select year and in the right hand side Year box, select Greater than Equal to and then select 1990
9. Click on OK Twice
10. Select "Return Data to Microsoft Excel" and click on Finish
11. In the Import Data box, click on OK

How to set up a sheet for Histogram

 Suppose we have a series as shown in A. so first we set up B

The formula for Bin and frequency is as follows:

The formula for cumulative frequency is as follows:

How to remove Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector

 here's how you fix it: Goto File in the upper left hand corner, then Options > Trust Center > Trust Center Settings > Privacy Options > then un-check the check box that says "Remove personal information from file properties on save", then hit OK. 

How to Extract the first letter of Every word in a sentence in excel

 Use this code

------

Function ExtractFirstLetter(text) As String

mystring = Left(text, 1)

For i = 2 To Len(text) - 1

If Mid(text, i, 1) = " " Then

    mystring = mystring & Mid(text, i + 1, 1)

End If

Next i

ExtractFirstLetter = WorksheetFunction.Substitute(UCase(mystring), " ", "")

End Function

--------------

Use it like this:

-------------------

How to Pull the distinct values from a column

 Regular distinct formula, just drag the cursor to get all the distinct values:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")

Where:

• A2:A10 is the source list.

How to subtract month from a date

 You can use the EDATE function to quickly add or subtract months from a date. The EDATE function requires two arguments: the start date and the number of months that you want to add or subtract. To subtract months, enter a negative number as the second argument. For example, =EDATE("9/15/19",-5) returns 4/15/19.

How to convert a text format to date format in excel - Tableau Way

 For example if you have a date like:

July 10, 2020 : Normally in tableau

How to convert ( Logic)

1. Month

 Extract the characters till you get a space and convert those characters to the month number

=MONTH(DATEVALUE(LEFT(A2,FIND(" ",A2))&"1"))

2. Day

Extract the characters between space and comma. Basically this is the way how we extract string between two characters

=MID(A2, FIND(" ",A2)+1, FIND(",", A2, FIND(" ", A2)+1)-FIND(" ",A2)-1)

3. Year

Extract the rightmost four characters

=RIGHT(A2,4)

4. Convert everything into date format which is given by

Date( Year, Month,Day)

=DATE(RIGHT(A2,4),MONTH(DATEVALUE(LEFT(A2,FIND(" ",A2))&"1")),MID(A2, FIND(" ",A2)+1, FIND(",", A2, FIND(" ", A2)+1)-FIND(" ",A2)-1))

How to find the unique or distinct values in a column in excel

The formula is 

=SUM(1/COUNTIF(B4:B722,B4:B722))

Where B4:B722 are the values in the column. 

Remember to close it by ctrl+shift+enter

This is an extremely useful formula

How to convert a text format to date format in excel

 Suppose we are given the following TEXT

8/26/1961. Let that it is in the cell J2

We would like to convert it to Date

8- month

26- day

1961 - year

So the formula to convert to date is

Date(year, month, day ) in yyyy,mm,dd format

So we go as follows

Year: RIGHT(J2,4)

Month: LEFT(J2,FIND("/",J2)-1)

Day: MID(J2,FIND("/",J2)+1,2)

So the formula becomes

DATE(RIGHT(J2,4),LEFT(J2,FIND("/",J2)-1),MID(J2,FIND("/",J2)+1,2))

How to deal with some wrong and some true dates

=IF(ISNONTEXT(C3),C3,DATE(RIGHT(C3,4),LEFT(C3,FIND("/",C3)-1),MID(C3,FIND("/",C3)+1,2)))

Power Pivot and DAX

 

What is Power Pivot

How to create Power Pivot from the data model

How to create Power Pivot from Insert menu

How to insert Power Piovt from Pivot Table icon

A measure is a calculated field

DAX is the formula language that drives power pivot

DAX can be used to add calculated columns or to ADD measures

Do not use calculated columns to calculate fields for the "value" area of the pivot table, use measure

Calculated columns are typically placed in the filters, slicers, rows or columns areas of the pivot

Where to create calculated columns

Where to enter DAX function

How to add a calculated column

How to calculate the customers age from the birthdate

You dont have to click on a particular cell for creating calculated columns or including columns, you can click on any cell

You can try excel formulae in DAX, they most probably will work but at the row level

How to change the header of a calculated column

How to delete a calculated column

Measures are used to generate dynamic values

Measures don't live in the table

While calculated columns are based on row context, measures are based on filter context ( slicers, filters, rows and columns)

Use Measures when a single row can't give you the answer

measures can only be placed in the values area

Implicit measures are simply values that you drag in the value area of the pivot

Just Say "No" to implicit measures, explicit measures are much better

Creating explicit measures by using AUTOSUM

How to turn on and off the calculation area

How to create an autosum measure

How to rename an autosum measure

Don't use the Autosum measure

How to delete an autosum measure

Where to find the button for measure

How to add a new mesure

Every measure value is an island and calculates independently of the other

For column reference, use the fully qualified name ( i.e. Table[Column])

For measure reference, just use the measure name [i.e. [Measure])

How to create a measure of the ratio using DIVIDE

How to change the layout of the field list

How to sort a field from increasing to decreasing

How to create a measure out of COUNT, COUNTA, COUNTROWS, DISTINCTCOUNT

How to create measure of IF, IFERROR, AND, OR

How to use Switch and Switch( True)

How to use Text Functions

Evaluates a given expression under a set of defined filters

How to use CALCULATE function

Filter() returns a table that represents a subset of another table or expression

Never use a filter where a normal CALCULATE function will accomplish the same thing

How to use Filter

Filtering with disconnected Slicers

How to add a field to a slicer

Syntax of ALL filter

How to apply ALL filter

Returns related values in each row of a table using relationships with other tables

Works just like VLOOKUP function

Syntax of RELATED() statement

This is rowwise aggregation

Syntax of X functions

How to use these functions

How to use RANKX function

Syntex of these functions

How to use Date and Time functions

These allow you to calculate common time comparisons

Syntex of Time intelligence functions

How to use Time intelligence functions

Speed and performance considerations

DAX best practices

Excel Data Modeling Hacks

 

Data Model provides tools for building relational databases directly in Excel

Excel Data Model

Data Model Window

Data View Vs. Diagram View

Database Normalization

Data Tables vs. lookup tables

Primay and Foreign Keys

Relationship vs. Merged Tables

How to create table relationship in Diagram view by click and drag

Always drag relationship from data table to lookup table

How to create table relationship using design tab

Connecting Lookups to lookups

How to modify table relationships

Active vs. Inactive Relationships

How to make a relation as active

Cardinality refers to uniqueness of values in a column

In Power piovt all relationships in data model should follow a "one-to-many" cardinality

Cardinality

One to one cardinality ( Bad)

Connecting multiple data tables

Always hide the foreign key columns in your data tables to prevent users from accidentally filtering on them

How to hide fields

Hierarchies are groups of nested columns that reflect multiple levels of granularity

How to create hierarchies

Data model best practices

Advanced Excel 2016 formulae hacks

 

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

SQL Hacks

 

Topic

Four fundamental functions of a data base system: CRUD: Create, Read, Update, Delete

In standard SQL, the SELECT statment is used for all queries that return values

The INSERT statement is used to add a row to a table

The UPDATE statement is used to change data

the DELETE statement is used to remove rows from a table

How to use SELECT statement, to retrieve data from SQL database

How to write the select statement

To write the select statement as a column name, to create an alias identifier to the column, or using AS statement

To get all the rows and columns of a table or using *

How to check the number of rows in table you have just received using SELECT

To get all the rows and columns of a table, ordered by a column

Select n columns from a table while retrieving

change the name of a column to something else when retrieving a table

change the name of a column to something else but in quotes

Select rows from a table using WHERE clause ( as a logical expression or filter)

Select rows from a table using WHERE clause ( as a logical expression or filter), ordered by a column, Or using ORDER BY

How to limit the number of rows in your SELECT result, or using LIMIT

Suppose I want to start by skipping first 5 results, or using OFFSET, how to do it

Order is important in SQL , FROM-> WHERE-> ORDER BY -> LIMIT --> OFFSET

How to select all the rows and all the columns or using *

Order is not important in how you select the columns from retrieving

How to count rows instead of listing them- using COUNT function

to count all rows

To count rows with a filter condition; using WHERE

To count rows with mulitple conditions using AND clause

Count all the rows of a particular column which has data

How to use INSERT INTO function to add rows to a table with the values using VALUES in selected columns

How to insert less values into a table with more columns, means to populate only some of the columns

NULL values means no data

Update the values in columns of a table with a given row ID using UPDATE, SET, WHERE

How to update the values in columns with null values

How to SELECT the fourth row of a table

Delete the fourth row of a table using DELETE, WHERE

how to create a table with CREATE TABLE statement

The column definitions are sometimes called database schema or the table schema

Insert values into table

Deleting a table using DROP TABLE

Deleting a table after checking if it exist by using IF EXISTS

To add a row or rows to a table

to Insert a rows with values

to insert a rows with some values in some of the columns

to insert default values in the table as a row

to insert values from some other table

Using DELETE from statement to delete rows where a column has certain value

NULL is a special state in SQL, it represents the absence of a value

How to SELECT if a column has a value NULL, you cannot put a = NULL

How to SELECT if a column doesn't have a value NULL

How to create a table where we put a constraint that a cell cannot have a null value, create a column with a not null constraint

how can you add a NOT NULL constraint in one or more of the column

How to give a column a default value while creating table using DEFAULT

How to make every value in the column unique using UNIQUE

how to change the table schema after it is defined use ALTER TABLE, eg. create a new column , also using ADD

An ID Column is a column that holds a unique value for each row in a table. Typically ID Columns are auto populated

Creating ID field, if needed using PRIMARY KEY

How to filter rows with a criteria

How to arrange the results in the descening order using DESC

How to filter rows where column contain null values and using OR

How to filter rows where column contain the word 'island' using LIKE and %

How to filter rows where column contain word that starts with the word 'island'

How to filter rows where column contain any letter as first letter but contain a in the second letter using '_' operator

How to filter rows where a column has some values using IN operator

How to find the values in a column without duplication with SELECT DISTINCT command, to get only unique results

Example of removing duplicates

Order by One column

Order by One column descending ( from high to low)

Order by One column descending ( from low to high)

Order by two columns

Select three columns Order by three columns, one column descending and two columns default

CASE WHEN value of a column is true or 1 THEN select one column ELSE select other column and then END ing the command, using if

Visualization of Joined Query

What is inner join or simple join

What is left outer join

What is right outer join

What is Full outer join

creating a join query to inner join two tables using JOIN and ON

creating a left outer join by using LEFT OUTER JOIN

An example of simple inner join

Relating multiple tables, joining three tables

Example of LEFT OUTER JOIN

How to write the SQL string

concatenating two strings using ||, CONCAT or +

Types of string functions, SUBSTR, LENGTH, TRIM, UPPER, LOWER

How to find the length of a string or a colunn of stings in SQL

How to find the length of a string or a column of strings in SQL

How to find a substring of a string or a column of strings in SQL starting from a position

How to find a substring of a string or a column of strings in SQL starting from a position and ending to a position

How to separate day, month and year from a date or a column of dates using substring

Removing space from a column of string or a string using TRIM, RTRIM and LTRIM

Converting upper and lower case for all the values in the column

Fundamental Numeric types

Interger types

Real types

Precision vs scale

to find the type of a value using TYPEOF

How to change one type of value to other using CAST

Rounding numbers or a column of numbers using ROUND

SQL standard datetime format

SQL dates and Time types

How to return the current date and time using DATETIME

How to return just the date using DATE

How to return just the time using TIME

How to add one day to the current date time

How to subtract one month from the current date time

How to add one year to the current date time

How to add 3 hours, add 27 minutes, and subtract one and and add three years from the current date time

Aggregate data is information derived from more than one row at a time

What is the total count of rows in the database

What is the toal count of rows aggregated by one of the columns using GROUP BY

What is the toal count of rows aggregated by one of the columns using GROUP BY, order by count from high to low

What is the total number of rows group by one of the columns in a joined table

How to group by a column with a filter condition using HAVING

How to group by a column with a filter condition using HAVING and a condition in the non aggregate part of the query using WHERE

WHERE clause has to be before GROUP BY clause( Non Aggregate data) and HAVING should be after the GROUP BY clause ( Aggregate data)

There are functions that operate on the aggregate values

How to count the numbers of rows in a column

How to find the average of rows in a column using AVG

How to find the average of rows in a column group by another column

How to find the min and max of rows in a column which is grouped by another column

How to find the sum of values in a column which is grouped by another column

How to operate the aggregate function on the distinct values using DISTINCT

A transaction is a group of operations that are handled as one unit of work

If any one of the operations fail, the database is restored to its previous state

How to use transactions in SQL using BEGIN TRANSACTION and END TRANSACTION or COMMIT

How to abort using ROLLBACK

Transactions increse the performance of the statements between them

Triggers are operations that are automatically peformed when some database event happens

How to implement triggers in SQL using CREATE TRIGGER, BEGIN and END, AFTER INSERT ON and NEW

How to implement triggers to prevent changes, use of BEFORE

RAISE to raise an exception

How to use Triggers to create timestamps

A SELECT statement can be used as a datasource in another SELECT statement, called subselect

How to use SUBSTR, so that we can split the values as separate columns similar to split function in Excel

How to use the data sources of SUBSTR as a select in another statement

How to provide a list of rows for searching a table, suppose you want an album that have tracks with a duration of 90 seconds or less

You can save a query as a view with CREATE ViIEW and DROP VIEW

You can create any query and create a view out of it. Now you can use view just like table