"I can not use Google Sheets, because I use advanced formulas."
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:
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.
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
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!
- SPARKLINE() – Google Sheets brings the power of sparklines directly into formulas. No need of a separate feature as it is on Excel
- 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.
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.
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!
Want to know more about the differences of Google Sheets vs. Excel?
More usage examples of formulas mentioned here and other powerful use of Google Sheets?
Get Epic Sheets updates and the Epic Sheets’ Google Sheets Commandments – For Free Today:
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.
The complete list of formulas which are exclusive to Google Sheets or Excel
| Formula | Family |
|---|---|
| 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.
