How Google Sheets is Leapfrogging Excel with Exclusive Formulas

I can not use Google Sheets, because I use advanced formulas.
— Favorite objection of Excel users to collaborate with colleagues on Google Sheets

In a nutshell (TL;DR)

Google Sheets and Excel formulas overlap for the majority of them.

What’s interesting is the formulas which are only available in 1 of the 2 software. That’s where we find that the formulas unique to Excel are mostly useful for scientists and statisticians, not a majority of business users. The formulas unique to Google Sheets on the other side dramatically improve data manipulation and dashboarding abilities. As a native cloud software, Google Sheets even takes advantage of the web and Google integration for magic features.

Google did not just copy Microsoft to replicate its popular Excel spreadsheet software. They took the best of it, and then added more, turning Google Sheets into a productivity booster for agile modern businesses with collaborative teams.

See the formula comparison table at the bottom of the page.


How Google Sheets and Excel formulas compare

The key value of Google Sheets is collaboration (natively in the cloud, share-able by url…), which is by itself such a strong value that it should be the default spreadsheet software choices (How often do you work on a spreadsheet that no one else will work on with you? In a team, work is to be shared sooner or later)

However, Google Sheets is still often seen as a poor cousin of Excel and rejected on grounds it can not do what one does in Excel.

Let me put it blankly: for the past 4 years, I do not use Excel. And I use spreadsheets every day, helping teams build budgets, strategic dashboards, board meeting decks, and other data analysis. Teams I work with saves hours or days using powerful Google Sheets while increasing the data flow within their organisations.

I’m at a point where I could not do what I do on Excel.

So, I wondered, How many formulas do Excel and Google Sheets have? How are the formulas restricting usage of Excel or Google Sheets?


Next time you hear someone arguing against Google Sheets on the basis of formulas, here are the facts:

Comparison of number of formulas Google Sheets vs. Excel
  • Excel has 471 formulas

  • 378 of these are common with Google Sheets

  • The 93 formulas available only in Excel… will satisfy mostly statisticians

  • However the 48 formulas exclusive to Google Sheets brings common business data manipulation to its users

Google Sheets has nothing to envy Excel when considering formulas!
426 formulas (and counting) is more than most humans use (I use around 90 on a regular basis; How many do you use?).

Worth noting too that Google adds formulas regularly (14 formulas added over the period of Jan to Oct 2018 for example), while Excel’s formulas updates are brought to life with new version only every several years (That’s one of the advantages of cloud software… getting updates on the fly, vs. desktop software).

In this article, we focus on formulas, as they offer a simple interface to interact with a spreadsheet. Some of the functionalities can be achieved without formulas. We will highlight the key formulas Google Sheets users can enjoy to build powerful, scalable dashboards.


Highlight of powerful formulas exclusive to Google Sheets

  • FILTER() - Allows to extract data from a multi-column list based on several criteria. Fantastically powerful to generate dashboards and for data analysis.

  • UNIQUE() - Returns a de-duplicated list of items.

  • SORT() - Takes a table and sorts the rows based on one or several column. Ever wanted to find out the top products or salespersons from a table? There you go.

  • SORTN() - Similar to SORT(), with a simple way to reduce the number of rows. So you can turn a table of many products and their sales numbers, and reduce it to the top 3 products.

These formulas have been with Google Sheets for years and are some of the most useful formulas in a business settings. They allow to run data analysis and build dashboards which scale with a business growth.

How FILTER(), SORT() and SORTN() allow to turn a dataset in useful extract, on the fly

How FILTER(), SORT() and SORTN() allow to turn a dataset in useful extract, on the fly

 

Google Sheets makes web and Google data available to users through formulas such as GOOGLEFINANCE(), GOOGLETRANSLATE(), and foster collaboration with IMPORTRANGE().

  • GOOGLEFINANCE() - Do you have to convert currencies to another? Do you want to monitor the stock price of Apple, Tesla or Xiaomi? GOOGLEFINANCE() brings it right in your spreadsheet

GOOGLEFINANCE() functions converting a multi-country revenue target exercise to consolidated numbers and returning stock prices.

GOOGLEFINANCE() functions converting a multi-country revenue target exercise to consolidated numbers and returning stock prices.

 
  • DETECTLANGUAGE()

  • GOOGLETRANSLATE()

Have you ever received a table in a foreign language? With Google Translation formulas included natively in Google Sheets, you can run the translation for thousands of rows in seconds. On a list of more than a few items, that’s days of savings!

Profit & Loss statement layout converted directly Google Sheets

Profit & Loss statement layout converted directly Google Sheets

 
  • IMPORTRANGE() - Google Sheets can be connected one to another with this IMPORTRANGE(). That means different people/teams can manage their own spreadsheets, while managers see an aggregated dashboard. No more copy/paste and stale data.

 
  • SPARKLINE() - Google Sheets brings the power of sparklines directly into formulas. No need of a separate feature as it is on Excel

Sparkline.png
 
  • SPLIT() - It happens that one spends hours typing manually the content of some cells because it mixes numbers and text (or different text elements needed individually)... That’s when SPLIT() comes in handy… does the text parsing job in seconds.

SPLIT() used to split the supply number and crypto currency tickers

SPLIT() used to split the supply number and crypto currency tickers

 
  • REGEXEXTRACT()

  • REGEXMATCH()

  • REGEXREPLACE()

Bringing the power of regular expressions to spreadsheets with these 3 formulas allows Google Sheets to run powerful data and text manipulation.

 
  • IMAGE() - While both Excel and Google Sheets allow to bring images as an overlay to a spreadsheet, this formula allows to insert a picture straight into cells, keeping spreadsheet clean and organised. With logos, icons and other illustrations, Google Sheets can make your brand or story shine. Thanks to the formula, images are part of a dataset and can be manipulated.

Image within a dataset with the IMAGE() formula

Image within a dataset with the IMAGE() formula

 
  • ISDATE() - Useful to analyse data, more precise than ISNUMBER() in some cases to ensure a dataset includes proper dates.

  • ISEMAIL()

  • ISURL()

Nice to have for data analysis and form inputs (Google Forms responses directly feed a Google Sheets, which can be analysed in place)

 
  • AVERAGE.WEIGHTED() - Nice one to calculate weighted averages in one go.

  • ARRAY_CONSTRAIN() - Extracts a certain number of rows and columns from a table, allowing to build scalable spreadsheets as the input data does not impact the size of the output.


How about the formulas exclusive to the Excel side?

  • Cube functions: Very powerful… but did you ever work with OLAP cubes? The formula requires much more than spreadsheet knowledge. If you wonder, OLAP is “a multidimensional database that is optimized for data warehouse and online analytical processing applications.”

  • INFO(), SHEET() and SHEETS(): Do you want to know which operating system your computer is running or how many sheets you spreadsheet has? You will get it with these formulas. You can probably live without it.

  • Statistical functions: This is where Excel has much more to offer than Google Sheets. If you’re a scientist or statistician, this article is probably not for you.


Have a look at the table of formula comparison below… Do you find any formula that you use in the Excel line? If not, you’re good to go on Google Sheets!


Thanks to Ohan Hominis, Mas Kubo, Aaron Kurzak, Shab Kumar, Arnaud Bonzom, Jacqui Hocking for helping me constantly look for the best spreadsheet usages and refine this article.


Want to know more about the differences of Google Sheets vs. Excel, usage examples of formulas mentioned here and other powerful use of Google Sheets? Get Epic Sheets updates:


The complete list of formulas which are exclusive to Google Sheets or Excel


Microsoft Excel formulas list is available here.

Google Sheets formulas list is here.

The full list of 378 common formulas between Excel and Google Sheets (as of Nov 21th, 2018):

FormulaFamily
TRANSPOSE()Array
BETADIST()Compatibility
BETAINV()Compatibility
BINOMDIST()Compatibility
CHIDIST()Compatibility
CHIINV()Compatibility
CHITEST()Compatibility
CONFIDENCE()Compatibility
COVAR()Compatibility
CRITBINOM()Compatibility
EXPONDIST()Compatibility
FDIST()Compatibility
FLOOR()Compatibility
FTEST()Compatibility
GAMMADIST()Compatibility
GAMMAINV()Compatibility
HYPGEOMDIST()Compatibility
LOGINV()Compatibility
LOGNORMDIST()Compatibility
MODE()Compatibility
NEGBINOMDIST()Compatibility
NORMDIST()Compatibility
NORMSDIST()Compatibility
NORMSINV()Compatibility
PERCENTILE()Compatibility
PERCENTRANK()Compatibility
POISSON()Compatibility
QUARTILE()Compatibility
RANK()Compatibility
STDEV()Compatibility
STDEVP()Compatibility
TDIST()Compatibility
TINV()Compatibility
TTEST()Compatibility
VAR()Compatibility
VARP()Compatibility
WEIBULL()Compatibility
ZTEST()Compatibility
DAVERAGE()Database
DCOUNT()Database
DCOUNTA()Database
DGET()Database
DMAX()Database
DMIN()Database
DPRODUCT()Database
DSTDEV()Database
DSTDEVP()Database
DSUM()Database
DVAR()Database
DVARP()Database
DATE()Date
DATEDIF()Date
DATEVALUE()Date
DAY()Date
DAYS()Date
DAYS360()Date
EDATE()Date
EOMONTH()Date
HOUR()Date
ISOWEEKNUM()Date
MINUTE()Date
MONTH()Date
NETWORKDAYS.INTL()Date
NETWORKDAYS()Date
NOW()Date
SECOND()Date
TIME()Date
TIMEVALUE()Date
TODAY()Date
WEEKDAY()Date
WEEKNUM()Date
WORKDAY.INTL()Date
WORKDAY()Date
YEAR()Date
YEARFRAC()Date
BIN2DEC()Engineering
BIN2HEX()Engineering
BIN2OCT()Engineering
BITAND()Engineering
BITLSHIFT()Engineering
BITOR()Engineering
BITRSHIFT()Engineering
BITXOR()Engineering
COMPLEX()Engineering
CONVERT()Engineering
DEC2BIN()Engineering
DEC2HEX()Engineering
DEC2OCT()Engineering
DELTA()Engineering
ERF()Engineering
ERFC()Engineering
GESTEP()Engineering
HEX2BIN()Engineering
HEX2DEC()Engineering
HEX2OCT()Engineering
IMABS()Engineering
IMAGINARY()Engineering
IMARGUMENT()Engineering
IMCONJUGATE()Engineering
IMCOS()Engineering
IMDIV()Engineering
IMLN()Engineering
IMPOWER()Engineering
IMPRODUCT()Engineering
IMREAL()Engineering
IMSQRT()Engineering
IMSUB()Engineering
IMSUM()Engineering
OCT2BIN()Engineering
OCT2DEC()Engineering
OCT2HEX()Engineering
ACCRINT()Financial
ACCRINTM()Financial
AMORLINC()Financial
COUPDAYBS()Financial
COUPDAYS()Financial
COUPDAYSNC()Financial
COUPNCD()Financial
COUPNUM()Financial
COUPPCD()Financial
CUMIPMT()Financial
CUMPRINC()Financial
DB()Financial
DDB()Financial
DISC()Financial
DOLLARDE()Financial
DOLLARFR()Financial
DURATION()Financial
EFFECT()Financial
FV()Financial
FVSCHEDULE()Financial
INTRATE()Financial
IPMT()Financial
IRR()Financial
ISPMT()Financial
MDURATION()Financial
MIRR()Financial
NOMINAL()Financial
NPER()Financial
NPV()Financial
PMT()Financial
PPMT()Financial
PRICE()Financial
PRICEDISC()Financial
PRICEMAT()Financial
PV()Financial
RATE()Financial
RECEIVED()Financial
SLN()Financial
SYD()Financial
TBILLEQ()Financial
TBILLPRICE()Financial
TBILLYIELD()Financial
VDB()Financial
XIRR()Financial
XNPV()Financial
YIELD()Financial
YIELDDISC()Financial
YIELDMAT()Financial
CELL()Info
ERROR.TYPE()Info
ISBLANK()Info
ISERR()Info
ISERROR()Info
ISFORMULA()Info
ISLOGICAL()Info
ISNA()Info
ISNONTEXT()Info
ISNUMBER()Info
ISREF()Info
ISTEXT()Info
N()Info
NA()Info
TYPE()Info
AND()Logical
FALSE()Logical
IF()Logical
IFERROR()Logical
IFS()Logical
NOT()Logical
OR()Logical
SWITCH()Logical
TRUE()Logical
XOR()Logical
ADDRESS()Lookup
CHOOSE()Lookup
COLUMN()Lookup
COLUMNS()Lookup
GETPIVOTDATA()Lookup
HLOOKUP()Lookup
HYPERLINK()Lookup
INDEX()Lookup
INDIRECT()Lookup
LOOKUP()Lookup
MATCH()Lookup
OFFSET()Lookup
ROW()Lookup
ROWS()Lookup
VLOOKUP()Lookup
ABS()Math
ACOS()Math
ACOSH()Math
ACOT()Math
ACOTH()Math
ASIN()Math
ASINH()Math
ATAN()Math
ATAN2()Math
ATANH()Math
BASE()Math
CEILING.MATH()Math
CEILING.PRECISE()Math
CEILING()Math
COMBIN()Math
COMBINA()Math
COS()Math
COSH()Math
COT()Math
COTH()Math
CSC()Math
CSCH()Math
DECIMAL()Math
DEGREES()Math
EVEN()Math
EXP()Math
FACT()Math
FACTDOUBLE()Math
FLOOR.MATH()Math
FLOOR.PRECISE()Math
GAMMALN()Math
GCD()Math
INT()Math
ISEVEN()Math
ISODD()Math
LCM()Math
LN()Math
LOG()Math
LOG10()Math
MDETERM()Math
MINVERSE()Math
MMULT()Math
MOD()Math
MROUND()Math
MULTINOMIAL()Math
ODD()Math
PI()Math
POWER()Math
PRODUCT()Math
QUOTIENT()Math
RADIANS()Math
RAND()Math
RANDBETWEEN()Math
ROUND()Math
ROUNDDOWN()Math
ROUNDUP()Math
SEC()Math
SECH()Math
SERIESSUM()Math
SIGN()Math
SIN()Math
SINH()Math
SQRT()Math
SQRTPI()Math
SUBTOTAL()Math
SUM()Math
SUMIF()Math
SUMIFS()Math
SUMPRODUCT()Math
SUMSQ()Math
SUMX2MY2()Math
SUMX2PY2()Math
SUMXMY2()Math
TAN()Math
TANH()Math
TRUNC()Math
AVEDEV()Statistical
AVERAGE()Statistical
AVERAGEA()Statistical
AVERAGEIF()Statistical
AVERAGEIFS()Statistical
BETA.DIST()Statistical
BETA.INV()Statistical
CHISQ.DIST.RT()Statistical
CHISQ.DIST()Statistical
CHISQ.INV.RT()Statistical
CHISQ.INV()Statistical
CONFIDENCE.NORM()Statistical
CORREL()Statistical
COUNT()Statistical
COUNTA()Statistical
COUNTBLANK()Statistical
COUNTIF()Statistical
COUNTIFS()Statistical
DEVSQ()Statistical
EXPON.DIST()Statistical
F.DIST.RT()Statistical
F.DIST()Statistical
F.INV.RT()Statistical
F.INV()Statistical
F.TEST()Statistical
FINV()Statistical
FISHER()Statistical
FISHERINV()Statistical
FORECAST()Statistical
FREQUENCY()Statistical
GAMMA.DIST()Statistical
GAMMA.INV()Statistical
GAUSS()Statistical
GEOMEAN()Statistical
GROWTH()Statistical
HARMEAN()Statistical
INTERCEPT()Statistical
KURT()Statistical
LARGE()Statistical
LINEST()Statistical
LOGEST()Statistical
MAX()Statistical
MAXA()Statistical
MAXIFS()Statistical
MEDIAN()Statistical
MIN()Statistical
MINA()Statistical
MINIFS()Statistical
NORMINV()Statistical
PEARSON()Statistical
PERCENTRANK.EXC()Statistical
PERCENTRANK.INC()Statistical
PERMUT()Statistical
PHI()Statistical
POISSON.DIST()Statistical
PROB()Statistical
RANK.AVG()Statistical
RANK.EQ()Statistical
RSQ()Statistical
SKEW()Statistical
SLOPE()Statistical
SMALL()Statistical
STANDARDIZE()Statistical
STDEVA()Statistical
STDEVPA()Statistical
STEYX()Statistical
T.INV.2T()Statistical
T.INV()Statistical
T.TEST()Statistical
TREND()Statistical
TRIMMEAN()Statistical
VARA()Statistical
VARPA()Statistical
Z.TEST()Statistical
ARABIC()Text
ASC()Text
CHAR()Text
CLEAN()Text
CODE()Text
CONCAT()Text
CONCATENATE()Text
DOLLAR()Text
EXACT()Text
FIND()Text
FIXED()Text
LEFT()Text
LEN()Text
LOWER()Text
MID()Text
PROPER()Text
REPLACE()Text
REPT()Text
RIGHT()Text
ROMAN()Text
SEARCH()Text
SUBSTITUTE()Text
T()Text
TEXT()Text
TEXTJOIN()Text
TRIM()Text
UNICODE()Text
UPPER()Text
VALUE()Text

We reclassified some formulas to be consistent between Google Sheets and Excel


Note: Microsoft announced they will be adding some of the formulas mentioned in this article to Excel 365. No date has been announced for the next release.

Florian Cornu1 Comment