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.
There is nothing either good or bad, but thinking makes it so.
William Shakespeare, 1564-1616
SUMIFS Wildcard Limitation
SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
The difficulties of life are meant to make us better, not bitter.
Anonymous
Clearing All Formats in Excel
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.
The greatest value of a picture is when it forces us to notice what we never expected to see.
John W Tukey, Exploratory Data Analysis
Yesterday and Tomorrow in Excel
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.
Education is the ability to listen to almost anything without losing your temper or your self-confidence.
Robert Frost
Macro to Format a Spill Range in Excel
VBA and spill ranges can work together
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.
Changing row and column grouping in Excel
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 Custom Function for a List
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.
A mind all logic is like a knife all blade. It makes the hand bleed that uses it.
Rabindranath Tagore
Slope Chart Macro
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.
Calculating YTD percentages in Excel
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.
Four Thousand Weeks (2020)
An alternative view of being productive and striving to do the most you can, in the time you have.
Being efficient and effective is messy and a one size fits all rule based system usually won’t last or work.
Has some good ideas on what to focus on and what not to focus on.
Custom Function to Reverse a String in Excel
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
A Conversation with Wylie Dufresne
Paste Special Values Shortcut
Excel has a new keyboard shortcut for Paste Special Values.
Ctrl + Shift + V
Formats Affect Excel’s Fill Handle
Applying a date format before you drag with the Fill Handle may save you some time. See why.
Now is no time to think of what you do not have. Think of what you can do with what there is.
Ernest Hemingway (1899-1961)
The Old Man and the Sea
Using the Custom Month List Function
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.
Excel Month List Custom Function
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.
Stop Excel Control being Clicked
Textbox hack
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.
Drop Down Selection update
Woohoo!
It has taken a decade or so but Excel finally has an in-cell drop down that you can type a letter and reduce the entries listed – see screen shot below.
Excel and Ordinals
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.
Fixing the UNIQUE Function
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.
Convert Day Text into Day Number in Excel
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.
Custom Function to Count Specific Characters in Excel
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)
Hiding Multiple Sheets in Excel Using xlVeryHidden
Flexible solution
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.