I use the thin all borders format a lot. But there are times when I need to use the thin outline (outside) borders. This border is not as straight forward to apply to a range.
SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
Sometimes with Excel formatting you just want to clear everything and start again from scratch. You can clear just the formats, and there is an icon you can add to the Quick Access Toolbar to make clearing all the formats earlier.
I recently saw a post about using a LAMBDA function for the dates for yesterday and tomorrow. You don’t need to use LAMBDA, you can just use range names.
Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.
Excel allows you to easily hide and unhide rows and columns using a feature called grouping. There are two keyboard shortcuts that allow you to apply and remove grouping. These shortcuts can also be used to amend existing groupings.
Excel’s Custom Lists are great, but you need to drag them to create them. If you have a list that you use frequently why not create a custom function to display it? This has the added advantage of creating a spill range that can then drive other dynamic array formulas.
About 10 years ago I did a short blog post on Slope charts. I was reviewing my 10 year old posts and remembered I had written a macro to simplify the process of creating a Slope Chart. So here it is.
When working with YTD percentages you must be careful with the calculation. Adding up values or amounts is easy. Working with YTD percentages require a bit more work.
When I started learning Python, I saw it had a Reverse function and thought, “I could do that in Excel.”
Jean-Georges Vongerichten showed me the value in taking away, taking things off of a plate. … The more you put on the plate, the easier it is to hide. The more you take away, there’s nowhere to hide—it has to be good.
Chef Wylie Dufresne
Applying a date format before you drag with the Fill Handle may save you some time. See why.
In the previous post I created a custom function that creates a horizontal list of months based on three inputs. In this post I share a few ways that you can use this list.
It is common in reporting files, budgeting and forecasting files to create a list of months going across the page. The custom function in this post can automate the process.
In a protected worksheet users can still click on checkbox and option button controls. A warning message will pop up if the control’s linked cells are locked. There is a technique you can use to stop users clicking on these controls. This involves a macro that you can run just before you protect the sheet.
A recent update to Excel included ordinals as an option when you drag with the fill handle. A few months back I made a post about a Custom function for ordinals in dates. Dates are a bit easier because they max out at 31. I thought I would extend the function so that it handled all numbers.
The UNIQUE function in Excel has a problem when it comes to handling blank cells. Blank cells are treated as zero and if you have a blank cell and a zero in a range then UNIQUE will return two zeros. Also, if a cell has a function that returns a blank cell, then that is treated differently than a blank cell. Let’s create a custom function to fix UNIQUE’s blank cell blind spot.
Excel uses a number from 1 to 7 to refer to the days of the week. The WEEKDAY function returns the number based on a date. Excel doesn’t a have a function to return the day number based on a text day like Monday. Time for a custom function.
I have previously covered a formula that counts specific characters. Since it used multiple functions, I thought I would simplify it by creating a custom function.
Education isn’t something you can finish
Isaac Asimov (1920-1992)
It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.
The myth is that there isn’t enough time. There is plenty of time. There isn’t enough focus with the time you have. You win by directing your attention toward better things.
If you can’t make a mistake, you can’t make anything.
Sometimes Excel refers to columns using numbers. If you need to identify the column letter you can use a few functions in combination. Thanks to Marcus Small for the formula which is shorter and more elegant than my original one. You can also create a custom function.
Never give up your right to be wrong, because then you will have lost the ability to learn new things and move forward with your life.
It is common in Excel to calculate the percentage movement or difference between two values. This may be between this year and last year or between actual and budget. There are two common issues you will face when doing this calculation. The first is handling zeros and the second is handling negatives.
Mistakes are painful when they happen, but years later a collection of mistakes is what is called experience.