Excel Moments

Another day, another Excel short.

Some days, you just want to go "old school," and today is one of those days.

Autosum is a quick way to subtotal a range of cells, both vertically and horizontally—though the latter is less known. However, there are some nuances that many Excel users may not be aware of. I’m here to share these insights with you all.

Watch, like, learn, share, and subscribe.

Quick Autosum Tricks in Excel #shorts This is a short to show some quick Autosum nuances and tricks. Some you know, some you don't - but learn all the saameSee workbook if you want to practicehtt...

Let me admit upfront that this video could be 1 minute long, but because I love to walk/talk my audience through the solutions, the video turned out to be 17mins 😁. This share would be good for people who want to get comfortable with VBA and get their hands dirty.

The video highlights a classic problem of trying to make bold a portion of text in a cell (not the entire text in the cell), which you can do manually, but when you have a lot of cells, you need some level of automation to get it done

Watch, learn, share, subscribe

Bold a part of a string Excel VBA bold part of string excel - In this video, I show elaborately how to make bold a part of a string by using Excel VBA. It is otherwise a short video, but for ...

Another day, Another Short!!!

knowing this trick is a good measure of your Excel age 😁

Just recently, I put up a post on 2-way Lookup using the XLOOKUP function, or more precisely a nested/double XLOOKUP. this problem could also have been solved using an INDEX and 2-MATCH functions.

In this current short, I introduce an old school approach which uses the intersection operator in excel which is a SPACE, so if you say =D1:D12 A7:E7, Excel returns the value in the cell at the intersection of both which is D7

This short combines 2 tricks in one, a quick trick to create names based off data on the top row and left column of the data set and the other using the intersect operator

Watch, Like, Share and Subscribe

Excel Intersection Operator for 2-way Lookup This is a short video that shows an older method of carrying out a 2-way lookup but not using an INDEX and 2 Match functions, rather the intersection Operato...

Once Bob Umlas shows up, a question/teaser/challenge accompanies him. While sitting in the lobby with Oz du Soleil preparing for our session at the Global Excel Summit, Bob showed up with a question, which involved sorting data, which would be challenging at first glance, because rather than having the data in a table with multiple columns, you have the data in 1-column. The problem would become more obvious when you watch the video.

The video shows my approach to the problem and trust me folks out there would have many other fantastic approaches to solving this problem, including PowerQuery, but I have shown here how I thought about it at the time.

Functions employed in this solution:
⭐WRAPROWS
⭐TEXTAFTER
⭐LET
⭐TAKE
⭐ROWS/COUNTBLANK

Watch, Learn, Share and Subscribe

Sort "Messy" Excel Data - Bob Umlas Excel challenge The task is to sort badly structured 1-D data that should ideally be in an Excel Table that would allow for easy or proper sorting. A challenge given to me b...

Another day, Another Short - Waterfall Chart

Not every one gets to use a Waterfall chart, but in some spheres it is the key to explaining the difference between the Start and Finish or how we got from A to B. I must say that the default waterfall chart in excel could do with some more flexibility, but, it is what it is for now.

This video is a "short" to show how to make a wedge a "Total" indicating that the wedge is not a variance explanation but a start or finish element, waterfall users understand the last statement, but trust me, in 55 seconds, you will understand fully what I mean

Watch, Like, Learn, Share and Subscribe

Waterfall Charts in Excel - Fix Totals A waterfall chart is easy to insert in Excel but sometimes a Total wedge does not show up as a Total starting from the base. This video shows a quick way to ...

Every now and again, when I post a video on Youtube, viewers ask for solutions to a slight modification of the problem, which is something I always welcome, because this makes for a learning opportunity for other viewers

I have posted videos on determining the Last blank cell, the last non-blank cell, and the first non-blank cell in a range. This particular problem required the user to return the first non-blank cell after the last blank cell, which I have coined using a fictitious delivery company model as the first delivery quantity after the last day of no deliveries. The video will surely explain better than the text, so do well to take a look

The video takes advantage of the X lookup functions (XMATCH and XLOOKUP) for determining the first/last instances of a lookup value to solve the problem and I wrap it all up with a lambda helper function to make the formula spill

Watch, Like, Learn, Share and Subscribe

Formula: First non blank after last blank cell in Range Each row has both blank and no-blank cells, for all you know it could be delivery information where days with no deliveries are blank and delivery days are n...

Back to solving Excel Challenges

Today's challenge is a rather interesting one, not because I have found a life-changing application for it, but just because of a few nuances, you can call them tips that are hidden in the solutions to the problem. This challenge(https://lnkd.in/dtTDXYEj) by Excel BI requires you to count how many times every character in the string occurs and sort the result, so if you have a string "hahahu", your result is "a:1, h:3, u:1" i.e a occurs once, h, 3 times and u once, and you return the result starting with the character that is first alphabetically

There are so many ways you can solve this problem, but I demonstrated 2 in this video and used a number of functions to get the job done.

The first uses a MID, SEQUENCE, SUM, LET, MAP LAMBDA, ARRAYTOTEXT while the second takes advantage of the new GROUPBY functions to make for an efficient solution

Watch, Like, Learn, Share, Subscribe
https://youtu.be/Vo_JILQeZhk?si=Q5Dw82mCLgYKnSy8

Excel Challenge: FREQUENCY OF ALPHABETS IN A STRING In this video, I demonstrate how to extract the number of times every unique alphabet appears in a string. 2 methods are shown, the "conventional" and one us...

Another day, Another short.

If "1-Mar-2024" was formatted as a number, what would it result in, without testing it out on Excel? I DON'T KNOW

Most times the inspiration for shorts are just simple things you think the whole world knows but they don't. This is one of such random tips that I shared with a friend earlier this week.

Say you inserted a scatter chart, which is supposed to be a date axis and you want to adjust the minimum and maximum dates to be displayed! When you open the format axis dialog, you see the Min and Max dates as 44000 and 45000 and you think to yourself, how do I know the number equivalent of the date I want to input, do I go back to the grid, type in the date, format as genera;/number to pick up the number to use?

The answer is No, just type into the Min and Max text boxes the dates in proper date formats and Excel accepts it, e.g. "1-Mar-24",13/01/2024. It is probably an obvious Tip to many but also not to many others

hashtag hashtag hashtag hashtag

Adjusting Min and Max dates for Excel Chart Axis #shorts There are times you want to limit the dates to a certain Minimum and Maximum of a chart Axis. But the format shows as numeric and dates and you are not sure ...

Another day, Another short

IMAGES and the GROUPBY function

This is one of the reasons I love the GROUPBY function because I can modify the row field within the formulas, without needing to create a new column in the source data. This "short" shows how to subtotal based on images(coming from a geography data table). I challenge you to do it in a pivot table without inserting a column from the images 😁 You don't have to take up the challenge

Watch, Like, Learn, Share and Subscribe
hashtag hashtag hashtag hashtag

GROUPBY Function with Images in Excel #shorts This video shows how to summarize by country/country flags(geography . data types) and subtotal on a numeric field. GROUPBY function makes this easier to handle

1 view at a time, 1 subscriber at a time.

Thanks to all who have subscribed and continue to watch, learn, and share my content

600,000 Views Milestone! Woohoo! ExcelMoments reached the 600,000 Views milestone on Mar 18, 2024 | via

The ( #) symbol is not just for spilled arrays -Another short

For fans of dynamic arrays, the " #" symbol is one that you must come across in our daily work when you reference a spilled array(in the form of C2 # which means every cell from C2 till the last cell the dynamic array spills into).

There is another use for the # symbol(which many may not be aware of) and that's its use in the hyperlink function as a shortcut to represent the current workbook(This is from my old notes).

To create a hyperlink to cell A1 on a sheet named "GrpTotal", I would write a formula that looks like =Hyperlink("GrpTotal!A1","GoToSheet"), where the first argument is the reference to navigate to, while the second is the name displayed in the cell.

This doesn't work as expected, because the first argument needs a reference to the workbook before referencing the sheet name and cell address, but that could look not to tidy, in such a case, just precede the reference with the # symbol which represents the current workbook and the function works. i.e =Hyperlink(" !A1","GoToSheet")

Watch, Learn, Like, Subscribe, Share

Hyperlink Function to link Sheets (# symbol magic) #shorts This video shows how to create hyperlinks to sheets based on the names of the sheets. The magic of the # symbol cannot be over-estimated in making this work,...

Thanks to everyone who viewed and continues to view content on the channel. This is to you

500,000 Views Milestone! Woohoo! ExcelMoments reached the 500,000 Views milestone on Dec 19, 2023 | via