Excel formulas can always be very handy, especially when you are stuck with data and need to get something done fast. But how well do you know the spreadsheet formulas?
Discover these 15 extremely powerful excel formulas and save a ton of time next time you open that spreadsheet.
1. Change the case of cell contents – to UPPER, lower, Proper
Boss wants a report of top 100 customers, thankfully you have the data, but the customer names are all in lower cases. Fret not, you can Proper Case cell contents with proper() formula.
Example: Use
proper("pointy haired dilbert")to get Pointy Haired Dilbert
Also try lower() and upper() as well to change excel cell value to lower and UPPER case
2. Clean up textual data with trim, remove trailing spaces
Often when you copy data from other sources, you are bound to get lots of empty spaces next to each cell value. You can clean up cell contents with trim() spreadsheet function.
Example: Use
trim(" copied data ")to get copied data
3. Extract characters from left, right or center of a given text
Need the first 5 letters of that SSN or area code from that phone number? You can command excel to do that with left() function.
Example: Use
left("Hi Beautiful!",2)to get Hi
Also try right(text, no. of chars) and mid(text, start, no. of chars) to get rightmost or middle characters. You can use right(filename,3) to get the extension of a file name 😉
4. Find second, third, fourth element in a list without sorting
We all know that you can use min(), max() to find the smallest and largest numbers in a list. But what if you needed the second smallest number or 3rd largest number in the list? You are right, there is a spreadsheet function to exactly that.
Example: Use
SMALL({10,9,12,14,26,13,4,6,8},3)to get 8
Also try large(list, n) to get the nth largest number in a list.
5. Find out current date, time with a snap
You have a list of customer orders and you want to findout which ones are due for shipping after today. The funny thing is you do this everyday. So instead of entering the date every single day you can use today()
Example: Use
today()to get 08/13/2008 or whatever is today’s date
Also try now() to get current time in date time format. Remember, you can always format these date and times to see them the way you like (for eg. Aug-13, August 13, 2008 instead of 08/13/2008)
6. Convert those lengthy nested if functions to one simple formula with Choose()
Planning to create a gradebook or something using excel, you are bound to write some if() functions, but do you know that you can use choose() when you have more than 2 outcomes for a given condition? As you all know, if(condition, fetch this, or this) returns “fetch this” if the condition is TRUE or “or this” if the condition is FALSE. Learn more about spreadsheet if functions like countif, sumif etc.
Where as choose(m, value1, value2, value3, value4 ...) can return any of the value1,2.., based on the parameter m.
Example: Use
CHOOSE(3,"when","in","doubt","just","choose")to get doubt
Remember, you can always write another formula for each of the n parameters of choose() so that based on input condition (in this case 3), another formula is evaluated.
7. Repetitively print a character in a cell n number of times
You have the ZIP codes of all your customers in a list and planning to upload it to an address label generation tool. The sad part is for some reason, excel thinks zip codes are numbers, so it removed all the trailing zeros on the leftside of the zip code, thus making the 01001 as 1001. Worry not, you can use rept() the extra needed zeros. You can also custom format cell contents to display zip codes, phone numbers, ssn etc.
Example: Use
zipcode & REPT("0",5-LEN(zipcode))to convert zipcode 1001 to 01001
You can use REPT("|",n) to generate micro bar charts in your sheet. Learn more about incell charting.
8. Find out the data type of cell contents
This can be handy when you are working off the data that someone else has created. For example you may want to capitalize if the contents are text, make it 5 characters if its a number and leave it as it is otherwise for certain cell value. Type() does just that, it tells what type of data a cell is containing.
Example: Use
TYPE("Chandoo")to get 2
See the various type return values in the diagram shown right.
9. Round a number to nearest even, odd number
When you are working with data that has fractions / decimals, often you may need to find the nearest integer, even or odd number to the given decimal number. Thankfully excel has the right function for this.
Example: Use
ODD(63.4)to get 65
Also try even() to nearest even number and int() to round given fraction to integer just below it.
Example: Use
EVEN(62.4)to get 64
UseINT(62.99)to get 62
If you need to round off a given fraction to nearest integer you can use round(62.65,0) to get 63.
10. Generate random number between any 2 given numbers
When you need a random number between any two numbers, try randbetween(), it is very useful in cases where you may need random numbers to simulate some behavior in your spreadsheets.
Example: Use
RANDBETWEEN(10,100)may return 47 if you keep trying 😉
11. Convert pounds to KGs, meters to yards and tsps to table spoons
You need not ask Google if you need to convert 156 lbs to kilograms or find out how much 12 tea spoons of olive oil actually means. The hidden convert() function is really versatile and can convert many things to so many other things, except one currency to another, of course.
Example: Use
CONVERT(150,"lbm","kg")to convert 150 lbs to 68.03 kgs.
UseCONVERT(12,"tsp","oz")to findout that 12 tsps is actually 2 ounces.
12. Instantly calculate loan installments using spreadsheet formula
You have your eyes on that beautiful car or beach property, but before visiting the seller / banker to findout of the monthly payment details, you would like to see how much your monthly / biweekly loan payments would be. Thankfully excel has the right formula to divide an amount to equal payment installments over given time period, the pmt() function.
If your loan amount is $125,000,
APR (interest rate per year) is 6%,
loan tenure is 5 years and
payments are made every month, then,Use
PMT(6%/12,5*12,-125000)which tells us that monthly payment is $ 2,416 if you keep trying 😉
Also, if you want to find out how much of each payment is going for principle and how much for the interest component, try using ppmt() and ipmt() functions. As you can guess, even though EMIs or loan installments remain constant, the amount contributed to principle and interest vary each month.
13. What is this week’s number in the current year ?
Often you may need to find out if the current week is 25th week of this year. This is not so difficult to find as it may seem. Again, excel has the right function to do just that.
Example: Use
WEEKNUM(TODAY())will get 33
14. Find out what is the date after 30 working days from today ?
Finding out a future date after 30 days from today is easy, just change the month. But what if you need to know the date thirty working days from now. Don’t use your fingers to do that counting, save them for typing a comment here and use the workday() excel funtion instead. 🙂
Example: Use
WORKDAY(TODAY(),30)tells that Sep 24, 2008 is 30 working days away from today.
If you want to find out number of working days between 2 dates you can use networkdays() function, find out this and a 14 other fun things you can do with excel.
15. With so many functions, how to handle errors
Once you get to the powerful domain of excel functions to simplify your work, you are bound to have incorrect data, missing cells etc. that can make your formulas go kaput. If only there is a way to find out when a formula throws up error, you can handle it. Well, you know what, there is a way to find out if a cell has an error or a proper value. iserror() MS Excel function tells you when a cell has error.
Example: Use
ISERROR(43/0)returns TRUE since 43 divided by zero throws divide by zero error.
Also try ISNA() to findout if a cell has NA error (Not applicable).
Give these functions a try, simplify your work and enjoy 🙂
46 Responses to “Big trouble in little spreadsheet”
I have faced this self same problem with my own delegates recently ... TWICE with two separate delegates in different places.
Take a look at my own blog post on one of them where I describe my pivot table solution: http://excelmaster.co under the heading of you need pivots
Duncan
I have faced this self same problem with my own delegates recently ... TWICE with two separate delegates in different places.
Take a look at my own blog post on one of them where I describe my pivot table solution: excelmaster dot co under the heading of "you need pivots"
Duncan
Really interesting thanks, I always thought it was due to my very complicated sheets and a slow computer (AMD + 4GB ram). But after thinking a little, I removed a whole pile of formatting in my tables, and now things are running a little faster!
I do leave my spreadsheets in manual calc mode, but that's because I constantly work with spreadsheets that contain between 20,000 - 100,000 rows, sometimes up to 600,000, with generally around 20-30 columns (before any calculated fields are added). Maybe I'm an exception, due to the amount of data I work with, but I'll throw this out there and see if I'm missing opportunities for efficiency.
I have a workbook that analyzes accounts to determine call priority (to collect past due AR), factoring in their ATB (aged trial balance, how much they owe, how much is current, 1-30 days past due, 31-60 days past due, etc), ADP (average days to pay), percent of credit limit used, whether they've had an NSF (insufficient funds notice, basically their check bounced), if we have unapplied cash on their account (like an unused credit that could balance out a due amount if we call and get their approval to use it for that purpose), their pay terms (how long they have from invoice until payment is due), and factors in all those bits of data to come up with a weighted point total to see which accounts need to be at the top of the call list, which is then distributed amongst the collectors.
There's generally around ~20,000 accounts on the list. The data comes from 5 different SAP reports. I originally had a different workbook for each report and linked to them from the ranking/calc spreadsheet, but I sped it up a bit by putting all the data on tabs (in calculation order) in one workbook from left to right leading up to the calculation sheet at the end.
The things I've found so far that help cut down calculation time:
Clean and sort the raw data before putting it into the calculation workbook. This may seem obvious or blasphemous based on your background. I used to work in digital illustration and it was key to always have the original, untouched raw data locked in at the back with all the adjustments layered above it so you didn't ever lose any of the source, so my initial practice was to always build my sheets to use the raw data exactly as it comes out of SAP. However, the calculation time saved when you do just a little work to make sure the data is arranged in an orderly fashion is significant. Every time you touch the data there's another opportunity for error, though, so it's something to be balanced.
When you need to remove certain rows from a table based on criteria, sort the table by that criteria first and it drastically reduces the time needed when you delete all rows with matching criteria.
If you have multiple columns that reference the same source table, don't use index & match in every one of those columns. Use match in one column to find which row of the source table to look at, then just use index for the other columns and reference the row number returned from the match column. For example, the final piece of my workbook is a sheet that builds a worklist for an individual collector. You put in the collector number at the top and it compares that to the total number of collectors and builds the list. If you have 7 collectors, then this looks at the ranking and lists every 7th account from top to bottom. One column generates the row numbers of those accounts using match, then the rest use index to pull the data over.
I'm about to switch worlds again, this time to accounting, so I'm doing a bit of housecleaning to make sure all my SOPs are in order and the spreadsheets I use are as clean and efficient as possible. If anyone sees a flaw in what I've described, or an opportunity to speed it up (preferably without using VBA, I'm leaving the work to people that will need a lot of coaching just to understand index and match, so VBA is only going to be a deeper mystery to them) I'd be happy to hear it.
@Kenneth
I would also consider using of Power Pivot as I believe that can combine data from varying sources
Hui...
Thank you for the suggestion. Unfortunately it isn't viable in this case. I approached that angle about a year ago, but was shot down. We're still on Excel 2010 and installing the powerpivot addon requires a lot of hoops to be jumped through with IT to get someone to install it (our machines are locked down pretty tight). Since I built this monstrosity of a spreadsheet to simulate what an upcoming SAP module is going to do automatically, my request was shot down. The problem is that the upcoming SAP module, which is nearly finished, hasn't lived up to expectation and will provide only a fraction of the functionality my spreadsheet does. Fun world.
Kenneth -
Your situation resonates! IT really is digging itself a hole in many large organizations, and that has a lot to do with why we have a boom in "self-service" (or, "leave me alone!") user-led solutions.
Just one question: are you quite sure you aren't able to install the PowerPivot add-in? It's really very simple, and free.
If IT refuses to help you, AND to install the self-service add-in, then they really are shooting themselves in the foot.
- juanito
Installing anything, even something as mundane as the powerpivot addon, requires administrator password, which IT holds.
Thank you for the Index/Match comment! My spreadsheet was built over a couple of months, adding new formulas as new requirements arose. This resulted in 86,000 cells with Index and Match. I'm off to clean up some formulas!
Kenneth - instead of drawing data from 5 seperate reports, I'd suggest get IT to create a new report that does as much of the number-crunching within SAP as possible. I often see files where most of the formulas are used to combine data that comes from the same database. In those cases, a simple tweak to the SQL query behind the reports makes tens of thousands of formulas redundant. At the same time, it's pretty simple to have SQL create calculated fields for current, 1-30 days past due, 31-60 days past due columns.
What kinds of formulas do you have in this apart from Index, Match? Anything really resource intensive like SUMIFs etc? Can you post a sample file somewhere, like on the Chandoo forum? I'd like to take a quick look.
Jeff, in theory, that's a brilliat idea. However in my organization this would be impossible. I'm working in a commerical department support the division IT of a global company (400k+ employees). I cannot get them to change anything on the system, not taking into consideration that data may come from several different systems (yes all SAP, but at my company alone we have 70+ SAP systems). I really like Hui's idea of using PowerPivot which could really make things much easier, though..
Phil, can't agree more.
In real world (or in my experience), asking IT to revise something simple is actually difficult. That's why many people prefers Excel to BI tools. And as a result, hours and hours of manpower is wasted.
On the other hand, although every one knows that Power Pivot is great. Installing Power Pivot into a PC in my office is not easy...
Sad but True...
Good suggestion Jeff, but Phil and MF nailed it. Changes to SAP are projects, and those are a big deal. There's an upcoming SAP module that I actually bulit this spreadsheet to mimic, but it turns out my mimic is significantly better than the module, which is in the final stages of testing and looks like a half-hearted attempt to replicate part of my spreadsheet.
I feel your pain 🙂
Another thing you can do to minimise recalc time is to convert any of the formulas in your data sheet - except those in the last row - with values. That way,
1. Excel doesn’t try to recalculate these formulas (which it does whenever you open or close the file).
2. When you put new data in the table, it’s very easy to copy the formula down from the last row, and then again then replace every formula except those in the last row with values
Thanks for this Jeff. Its interesting to go to many of the forums that attempt to answer this exact question, yet they don't go into nearly as much detail.
Example, I often hear to limit the use of SUMPRODUCT. But have never read the reasons that you lay out, nor these preferred solutions.
Also, good to learn that the delete key on the keyboard behaves differently than delete within the program. Wasn't aware of that. Good tips.
Thanks
Rick Grantham
Thanks for the kind words, Rick.
Caveats are sadly lacking from most blogs. "OFFSET is cool, but they are volitile, which means...". "SUMPRODUCT is increadible, but they are resource intensive, which means...". "PivotTables are awesome, but they store a copy of your data in a PivotCache, which means..."
We educate people on one weapon at a time, but don't give them any basic firearms safety training, nor talk about how to consider which weapon from the armory - or which combination of weapons - best suits a particular set of battle conditions.
Thank you for a great post. I am a culprit of Manual calculations on my latest spreadsheet and would appreciate any advice. It falls into the "confused range" discussion. I have approx 10 worksheets (customer) which are formatted identically with rows of sales data, then a main sheet which summaries the 10 worksheets. The 10 customer worksheets all vary in the number of rows so a lot of my formulas are sum(k7:k9999) to allow for the varying no of rows so I don't accidentally exclude rows. How can I apply your discussion of confused ranges to formulas where the row numbers vary? Many thanks
One thought Cathy, is to change the data ranges into tables. that way you can use table references (structural references). instead of cell ranges. Tables references are dynamic.
Please take a look at one of Chandoo's prior posts on the subject:
http://chandoo.org/wp/2013/06/26/introduction-to-structural-references/
Yep, great suggestion, Chris. Whenever you have Excel 2007 or greater, and whenever you have data sitting in a block, and whenever you point formulas, pivots, charts at that data, then you want to change the data range into an Excel Table with the Ctrl + T shortcut.
It's a great habit to get into. As soon as you build a new sheet - or are reviewing an old one - then as soon as you input new data into a block - or come across some existing data in a block, then push Ctrl + T.
Including VLOOKUP tables.
Thanks Chris and Jeff. So the table will automatically adjust in size? Thanks for pointing me in the direction of tables, it's opening a whole new world.
Yes they do, which is why they are so handy when it comes to pointing PivotTables at them...you never have to click the 'Change Data Source' button again.
Not only ... Columns and rows are added as you wish.
If you link a graph or graphs to a table they update automatically, too.
Pivot tables are updated as your table expands.
These tables are so good. I managed a small college's student achievement database in an excel table: 1,700 students, four semesters, six subjects per semester, four assessments per subject per semester. Name, class, father's name, province ... Formulas for pass and fail per semester, attendance percentage ...
It worked really well.
I know, should have been in a database but you need to know where we were and the resources we had!
Duncan
I'm reading Phil's description and I give it at least a 15% chance he and I work for the same company......
Great tips. Ive read that after the delete action, you might need to go to cell A1 and save the document. Not sure if this helps,but thought i wd mention it 🙂
Chris
[…] Is your Excel file big and slow? Jeff Weir suggests a couple of ways to fix things. […]
What about the event where it is columns and not rows that are taking up unnecessary space in the used range? I tried modifying the code to correct that, but it didn't do so.
The fix for the extra rows worked great! But now I'm wondering the same about the extra columns. I tried modifying the code as well (just changed the word "rows" to "columns") and it didn't work.
Anyone know a fix for extra unneeded columns?
One other thing that will cause havoc - dynamic functions. In an industrial environment, there are several versions of historians (which collect process data real-time), which usually have excel plug-ins. The problem comes in if you have =TODAY() or =NOW() in those spreadsheets. All of a sudden, you have 20,000 cells recalculating every time you make a change. Better to use VBA to repopulate a cell with the current time and date when you open the spreadsheet.
Absolutely, Stephen. In fact I make that point at http://chandoo.org/wp/2013/09/29/i-said-your-spreadsheet-is-really-fat-not-real-phat/
under the heading Handle sweaty Dynamite and Volatile Functions with extreme care….
I plan to elaborate on this in a future post.
Thanks for the insightful comment.
nice information
@Cathy: My 2 cents of adrice would be to merge all different sheets with Customer data in one single sheet (that should be possible as you state that all sheets have same format).
Then add a new column (in column A for instance) calling it "Customer", so you have a field to differentiate all 10 imported data blocks with sales data from 10 different clients.
Next thing is to convert the data range into a table.
Then create a pivot table based on the created table to substitute the main sheet.
And "voilá" 🙂 You will have only two sheets (one containing data, other containing pivot with summary), easily maintainable (you can order sales data in data sheet by date or by customer, whatever suits you best).
Hope these ideas provide you with solutions to improve your spreadsheet design!
Bar
Martin
[…] me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little spreadsheet. Well today, I’m going to praise the work of a higher […]
Hi Jeff,
Extremely well written. Just wanted to say that your 'used range' tip (ctrl + end) helped me reduce my workbook size from 70mb to just 6mb! Thanks a bunch!
Thanks. I formatted a column and it entered the formula all the way to the bottom of the possible XL page. Plus add 4 pages wide onto that and i now had a large doc. I wasn't able to click on a square without " not responding" showing up.
Can I in the first place just limit the formula to let's say 500 lines? So I have room to add data without falling off the formula but not blowing out the Size and speed.
Thanks the general info you supplied was on the mark. The following replays is just over the top. For me that is.
The [CTRL] and [down arrow] didn't work for me. I just selected the data area and cut and paste into a new worksheet. I got rid of my extra 1,048,000 of lines.
Purely rhetorical, at this point, but... why would MS avail capability in software that, when used, makes said software crash or work so slowly that it is next to useless in terms of productivity and efficiency? Array type formulas are useful tools, however, when used tend to gum up the works wrt data analysis and normalisation.
For "The Confused range" solution, it is important to SAVE immediately after you delete the extra rows, then close and re-open the spreadsheet. I got this tip from a comment on http://xsformatcleaner.codeplex.com/releases/view/98007#ReviewsAnchor, and it is also mentioned in the Microsoft support article on this issue (https://support.microsoft.com/en-us/help/244435/how-to-reset-the-last-cell-in-excel)
I know it's an old post, but thank you. Your "confused range" tip just reduced one of my spreadsheets from 21.5Mb to 1.5MB. It obviously is still an issue with Excel 2016.
In my case, there were thousands of columns with fancy formatting but no data: someone had just copied the column formats across the whole worksheet. All I had to do was delete all the empty columns.
Fantastic.
Awesome...glad you liked it.
I have a large dataset which I need to lookup values from. Is it more efficient to have one large named range (identifier code + 134 columns) or to repeat the identifier code multiple times to break it down into smaller named ranges (e.g. identifier code + 50, identifier code + 50, identifier code + 34)?
Hi Katherine. It depends what the formulas that reference the named ranges do.
If they are VLOOKUP or MATCH formula, then if your current file is running slowly and you want them to run like greased lightning, sort your lookup items and use the "Binary Search" version of the lookup. Read my other guest post at the following link to see just how much faster this is:
http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/
If they are SUMPRODUCT formulas, replace them with SUMIFS or COUNTIFS if you can, or even better, use a PivotTable to crunch the numbers.
Given this article of mine a read, too:
http://chandoo.org/wp/2013/09/29/i-said-your-spreadsheet-is-really-fat-not-real-phat/
Wow, I just squeezed an excel sheet down from 88MB to 250KB using the confUSED range solution you propose. I had to do both, deleting the range first AND run the macro.
Thank you!
Thanks for sharing your knowledge. No formulas in my spreadsheet - yet it had bloated to over 145,000 KB. Using the CTL-End determined one of the workbooks had excessive empty rows. Copied the only 27 rows of data from that workbook to a new workbook then deleted the bloated workbook and now down to a slender 64 KB.
Now how can I do the same when I feel bloated?
massive Chandoo!!! you saved me !!
I really appreciate your post!
My company uses Google Sheets online to maintain spreadsheets with multiple contributers and I'm constantly (almost daily) having to apply what you call the confUSED range fix. I'm so glad it works, but of course no one else will take the time to learn the fix.
Can you help me with understanding how the bloating happens to begin with? Is it always someone applying formatting to the entire sheet? People in my company act like I'm speaking another language when I try to explain even the simplest of solutions... any help would be appreciated 🙂
Thanks!!
I'm constantly running into Excel bloating. I'll turn a file into binary and that will fix much of it, but sometimes it doesn't. I just created a new binary file and copied 9 worksheets from the old binary workbook (45mb) into the new workbook. By copied I mean right click on the worksheet and copy to a different workbook. Once I saved the new workbook with the same worksheets, my new binary file went to 3mb. No pivot tables, roughly 85000 rows x 710 columns (with 662 columns blank). Any idea what could cause such bloating?