Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To split dimensions that like 100x50x25 into three separate parts, you can use some rather complicated formulas that use LEFT, MID, RIGHT, FIND, LEN, and SUBSTITUTE. Note: you can also use Flash Fill in Excel 2013 and above, and the "text to columns" feature in older versions of Excel. Both...Read more
To convert simple text values to numbers, you can use the the VALUE function, or simply add zero as described below. In the example shown, the formula in C5 is: = VALUE ( B5 ) Background Sometimes Excel ends up with text in a cell, when you really want a number. There are many reasons this might...Read more
If you need to round prices so that they ended in the nearest, .99 value, you can use the ROUND function then subtract .01. How this formula works The formula shown in the example is: = ROUND ( B6 , 0 ) - 0.01 With the value in B6 of 63.39, the formula is solved like this: = ROUND ( B6 , 0 ) - 0.01...Read more
If you need to round a number to the nearest multiple of 5, you can use the MROUND function and supply 5 for number of digits. How this formula works In the example, cell C6 contains this formula: = MROUND ( B6 , 5 ) The value in B6 is 17 and the result is 15 since 15 is the nearest multiple of 5...Read more
To test values to see if they begin with one of several characters (i.e. begin with x, y, or z) , you can use the COUNTIF function together with the SUM function. In the example shown, the formula in C5 is: = SUM ( COUNTIF ( B5 , { "x*" , "y*" , "z*" } )) > 0 How...Read more
Videos
In this video, we’ll look at how to perform a random sort with the SORTBY function, with help from the RANDARRAY function.
In this video, we show you how to use drag and drop, which can be surprisingly useful.
In this video, we’ll set up the XLOOKUP function to perform an approximate match in order to calculate a quantity based discount.
In this video, we show you how and why to use a table as a data source for your pivot table.
In this video we look at how to combine the IF function with the OR and AND functions to test more than one condition at the same time.
Blog
This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook.
A quick example of how to track testing for COVID-19 using Excel and publicly available data. In this project, the data is fetched and "shaped" with Power Query, then dropped back into Excel, where it can be refreshed with a single click.
Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.
Dynamic Arrays are the biggest change to Excel formulas in years. Maybe the biggest change ever. This is because Dynamic Arrays let you easily work with multiple values at the same time in a formula. This article provides an overview with many links and examples.
Pivot tables are one of the most powerful and useful features in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.
Feedback
This is fantastic. I am working on a report and this is exactly what I needed. Thanks. -Diane
I really like Exceljet and have already referred a few colleagues to it. Your shortcuts and tips are relevant and very easy to follow/learn. Thanks for providing such a great resource! -Theresa
Love your website. The info is clear to follow and very helpful for people like me who search for formulas when in middle of building a spreadsheet. Keep up the great work and thank you very much. -Love your website
We offer free resources and paid training.