Recent Discussions
Custom Sparkline Formula
I am in the process of moving to Excel from Google sheets. I am trying to build a project sheet and add a formula that will provide a "sparkline" for quick reference of the projected start and end dates, and then based on the status of the task (not started, in progress or complete) and today's date in relation to the start and end dates of the task, change color (Green, yellow, red, etc). Here is the formula I use in Google Sheets, but I cannot get it to work in Excel. =SPARKLINE(if(OR(today()<D5,today()>E5),{int(D5)-int($D$2),if(D5=E5,1,int(E5)-int(D5))},{int(D5)-int($D$2),today()-int(D5),1,int(E5)-today()}),{"charttype","bar";"color1","white";"color2",if(G5="Complete","grey",if(today()>E5,"red",if(AND(today()>D5,today()<E5,G5="Not Started Yet"),"darkred",if(AND(today()>D5,today()<E5),"orange",if(G5="In Progress","orange",)))));"max",int($E$2)-int($D$2)}) In this formula, Column D represents the start date and Column E represents the specific task end date. D2 and E2 represent the Project start and end date. Any Advice?
Movement in spreadsheet as form after enter
When using an Excel spreadsheet as a form, is there a way to move from one cell location directly to another cell location after pressing enter in the first cell? What I want to do is have the user enter the width in cell B7, hit enter, and have B9 become the "active" cell, then enter the length in B9, hit enter, and have B11 become the "active" cell, so on and so forth. Essentially I want to skip from one input cell to another without having to go through all of the rows. Not all "moves" from one cell to another would be exactly two rows down, other locations in the spreadsheet would require moves of 3 or 4 rows to get to the next "active" cell. All of my moves would be in the same column though. Thanks to all who look at this.Drawing data from multiple spreadsheets
Hi, So I have multiple spreadsheets with animal numbers and their weights I want to combine it on one spreadsheet The problem is that not all the animal numbers are on all the tabs, so I need a calculation that will "skip over" the one's not there. So for example this sheet (28 October) there are animals without tags and we gave them numbers 0.1-0.3, and then below that you'll see that 24004 wasn't weighed on that day so I don't want the formula on the final sheet to bomb out. If there's some way to do this process faster than what I'm doing currently - one by one = and then selecting the tab and the corresponding weight and animal number. Please and thanks so much!
Countif/Countifs
I'm trying to do a standard countifs formula for several columns of information. Each column is using the same criteria but has different ranges. I'm trying to make an equation that will be easy to drag so I do not have to rewrite the range each time. Does anyone know how to do this? I have cells with the prewritten ranges I'll want to use. I can give more information if that helps people understand.
Mastering Clipchamp – From Beginner to Pro
If you’ve ever wanted to edit videos but felt overwhelmed by complicated software, you’re going to love Clipchamp. It’s Microsoft’s free, browser-based video editor designed for beginners and creators who want professional-looking videos without a steep learning curve. first 4 parts: https://dellenny.com/mastering-clipchamp-from-beginner-to-pro-1/ https://dellenny.com/mastering-clipchamp-from-beginner-to-pro-2/ https://dellenny.com/mastering-clipchamp-from-beginner-to-pro-3/ https://dellenny.com/mastering-clipchamp-from-beginner-to-pro-4/New Landing Pages for Word, Excel and PowerPoint (rant)
So I found out this week that MS is piloting new landing pages for the core Office apps (again). Instead of loading in the M365 Home page, they now load in an entirely new page. Why was this necessary? We've already been here before and MS spent years moving everything to the Home app. The dust has finally settled and things are working great. Why move things out again?Announcing Office 365 for IT Pros (2026 Edition)
Office 365 for IT Pros (2026 edition), the 12th in an eBook series going back to May 2015, is now available. Covering all the essential aspects of Microsoft 365 tenant management from Entra ID to Exchange Online, SharePoint Online, OneDrive for Business, Teams, data lifecycle management, information protection, and more, Office 365 for IT Pros is an indispensable companion for tenant administrators who want to understand how Microsoft 365 really works. https://office365itpros.com/2025/07/01/office-365-for-it-pros-2026-edition/
Need Help with Name Manager
So, I created a name for name manager. Essentially, I am keeping track of daily tasks through certain departments (which are color coded) and counting those colors to add the time spent on each department daily. I used the name manager to help get the colors of each box to be counted. However, when I saved the sheet and tried to share it with my coworkers the sheet completely stopped working. It seems the name manager name did not save to the new sheets so it is not grabbing the coding and not adding correctly. If there is an easier way to do this I'm all ears!
In Microsoft Word headers, how do I enter content that differs on each page without section breaks?
I am currently finishing up a daily devotional book -- that is, a book that has one page for each day of the year (in this case including February 29th for leap years). I need a header that contains unchanging content (the name of the book), and two predictably variating items: the day of the year, and a running number of days. For example: Crumbs ~ February 01 / Day 32 Crumbs ~ February 02 / Day 33 I am hoping to find a way to feed a formula into the Word header that will cooperate (even with the February 29th issues). It is important that the header remain consistent in this upward count--even if I move some of the pages back or forth. I also want to avoid including a section break at the end of each page. Does anyone have any ideas of how to make this work?
Network connectivity test TCP Connection results unreliable
My team has spent the last 2 business days trying to get the https://connectivity.office.com/ TCP connection test to complete successfully to appease the Microsoft Unified Support Team. They don't want to take our MS Teams crash diags until they see SSL Interception Detection and TCP connection tests passing successfully. We can't get TCP connection tests to pass, we get one of the following: Success Attempt #1 Success, Attempt #2 Failure Any number of errors to unblock URLs such as (but not limited to) ocsp.digicert.com,ocspx.digicert.com,ocsp.omniroot.com,su.symcb.com,sr.symcb.com,sd.symcb.com,s1.symcb.com,sa.symcb.com We figured it was something with the corporate network but that's not the case. Last night we had about 50 employees try this test from their home (personal) computers that have no connection to our corporation. The TCP Connection test fails on home (consumer) PCs. What's the story with this TCP Connection check?#Spill Error in excel
I'm encountering a strange issue with Excel. When I open a PivotTable and move any field from the Rows area to the Filters area, the PivotTable disappears entirely and displays a #SPILL! error. However, if I remove that field and add a different one to the Rows area instead, the PivotTable works without any problems. This problem started for our users after installing new updates.
Conditional Formatting for Spilled Arrays
I have recently composed a bunch of functions to make using conditional formatting with spilled arrays more manageable. You can check out those formulas at the end of my post. I am curious if anyone has a different way to accomplish this goal. I will briefly describe the overall method and give a use case. In essence, to use conditional formatting you need to 1)specify a range of cells where the rule will be conditionally applied (the "Conditional Formatting Range") and 2) specify the condition for application. In many cases, the formatting is conditioned upon the placement of the cell/column/row/etc. relative to the spilled array which generated it (the "Spilled Array Range"). The problem (so far as I know) is that Excel's conditional formatting manager does not allow dynamic range references to specify the Conditional Formatting Range, so there is a fundamental disconnect between the Conditional Formatting Range and the possible range of the Spilled Array Range you want to format. It occurred to me that one possible solution to this problem is to A) set the Conditional Formatting Range to a defined range that is certain to be larger than your Spilled Array Range and B) create conditions that inherently self limit themselves to the Spilled Array Range regardless of the size of your Conditional Formatting Range. Mindful of the perils of using volatile functions, I wanted to create a solution that avoids turning values (string references primarily) into range references via functions like indirect and offset. That meant that I was limited to selecting a subrange from a larger range and constructing a dynamic range by way of a pattern like "index(array1, starting_row, starting_col):index(array2, ending_row, ending_col)" where the first index returns a single cell range reference to the first cell of the target range and the second index returns a single cell range reference to the last cell of the target range. This idea sort of distills down to the last few lines of the function I created: result, IF( NOT(base_in_container), NA(), LET( start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col), end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col), start_ref:end_ref ) ), If you name the range created in this way, you can simply enter "=named_range" into the applies to field in the conditional formatting interface and Excel will, at the time you apply the rule, resolve that name into whatever the then current range reference is of named_range, e.g. "A$7$:$G$52". Assuming your spilled array is contained in that range, your conditional formatting rule will apply to the spilled array. I call this larger containing range something like "CF_OVERSIZED_ARRAY." Once CF_OVERSIZED_ARRAY is set to a rule, you never have to change the Conditional Formatting Range again unless your spilled array size possibly exceeds whatever range you initially selected. (For instance, if your oversized array is 50 rows and 12 columns you need not change its size unless your spilled array suddenly grows from say 7 columns to 14). The elegance of this method over directly hardcoding the value is that if you have many conditional formatting rules, by entering "=named_range" for the applies to range in each rule, you both eliminate the possibility of inconsistent application ranges and have a visual confirmation when entering each rule that the value you are setting is exactly what you intend (rather than something like "=worksheet1!$A$23:$H$79"). Furthermore, by programmatically defining the oversized array, you can make it as "small" as reasonable, thereby avoiding having conditional formatting apply to many unused cells (such as the whole worksheet). At this point, the next computation minimization occurs - a guard clause for each condition is specified such that any cell in CF_OVERSIZED_ARRAY outside of the Spilled Array Range immediately returns a false and no further condition checking is performed. The general formula for checking if a cell is within the Spilled Array Range is as follows along with an example of a guard clause: is_within_array = LAMBDA( range, LET( start_row, ROW(TAKE(range, 1, 1)), start_col, COLUMN(TAKE(range, 1, 1)), AND( ROW() >= start_row, ROW() < start_row + ROWS(range), COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range) ) ) ); is_in_row_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( Now that the basic structure has been established, a number of workhorse functions are established: is_in_row_of_array - cell is within a specified row or range of rows in the spilled array such as "is in row 1" or "is in rows 4 through 6", is_in_col_of_array - cell is within a specified column or range of columns in the spilled array such as "is in column 1" or "is in columns 4 through 6", is_in_slice_of_array - cell is with a specified contiguous portion of the spilled array such as "is between rows 5 through 7 and columns 2 through 12" is_in_interval_of_array - cell is in set of every N rows or N columns such as "is one of every other row" or "is one of every third column" is_in_recurring_band_of_rows - cell is in a recurring grouping of rows such as "is 2nd and 3rd row of every group of 4 rows" is_in_recurring_band_of_cols - cell is in a recurring grouping of columns such as "is last column of every group of 7 columns" Here is an example function: is_in_col_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_cols, COLUMNS(range), current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1), end_resolved, IF( ISOMITTED(end_index), start_resolved, IF( end_index > 0, end_index, num_cols + end_index + 1 ) ), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_col >= final_start, current_col <= final_end) ) ) ); On top of the basic structure, convenience functions are created - the names of which should indicate how they solve common formatting needs: alternate_cols alternate_rows is_in_first_row_of_recurring_band_of_rows is_in_last_row_of_recurring_band_of_rows is_in_first_col_of_recurring_band_of_cols is_in_last_col_of_recurring_band_of_cols is_in_header_of_col is_in_last_row_of_col is_in_first_data_row_of_col is_between_first_and_last_row_of_col One major benefit flowing from this design is that these conditions are composable, e.g. alternate_cols * is_in_header_of_col would give you alternating formatting on the headers of a spilled array, such as an array with empty columns between each of the substantive columns. While I do not promise that the following formulas are perfect, what I can say is that they presently permit me to write rules like this: =cf.is_in_row_of_array(ins_rep.dynamic_array, 1)*cf.alternate_cols(ins_rep.dynamic_array,FALSE) =cf.is_in_first_data_row_of_col(ins_rep.dynamic_array,9) =cf.is_between_first_and_last_row_of_col(ins_rep.dynamic_array, 9,TRUE) =cf.is_in_last_row_of_col(ins_rep.dynamic_array,9) Which effectively gives me a rule for all headers (shading with underlining and bold), the ability to set the first data cell in column 9 to be a dollar format, the last cell in column 9 to be a dollar format with a top border, and all of the other cells in column 9 to be simple integers. So something like this (imagine the first cell is shaded as described and the last cell has a top border) is what I get for column 9 of a dynamically generated and spilled array: Fees $175 175 175 175 175 175 175 175 175 $1,575 Please let me know if you have found any other ways to address the problem of the Conditional Formatting Range being disconnected from the Spilled Array Range. I'm happy to answer any questions about my method or formulas, so feel free to ask. I'd also appreciate any feedback/suggestions/improvements on my idea/formulas. Here are the complete formulas (I have them saved within Excel Labs Advanced formula environment in separate modules) // _range module create_expanded_from_subset_of_containing_range = LAMBDA( base_array, desired_height, container_array, [desired_width], LET( req_width, IF(ISOMITTED(desired_width), COLUMNS(base_array), desired_width), /* --- Resolve anchors (references, not values) --- */ base_array_first_cell, INDEX(base_array, 1, 1), base_array_first_row, ROW(base_array_first_cell), base_array_first_col, COLUMN(base_array_first_cell), container_array_first_cell, INDEX(container_array, 1, 1), container_array_first_row, ROW(container_array_first_cell), container_array_first_col, COLUMN(container_array_first_cell), container_array_rows, rows(container_array), container_array_cols, columns(container_array), idx_base_in_container_first_row, base_array_first_row - container_array_first_row +1, idx_base_in_container_first_col, base_array_first_col - container_array_first_col +1, idx_base_in_container_last_row, idx_base_in_container_first_row + desired_height - 1, idx_base_in_container_last_col, idx_base_in_container_first_col + req_width - 1, base_in_container, and( idx_base_in_container_first_row > 0, idx_base_in_container_first_row <= idx_base_in_container_last_row, idx_base_in_container_last_row <= container_array_rows, idx_base_in_container_first_col > 0, idx_base_in_container_first_col <= idx_base_in_container_last_col, idx_base_in_container_last_col <= container_array_cols ), result, IF( NOT(base_in_container), NA(), LET( start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col), end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col), start_ref:end_ref ) ), result ) ); //cf module is_within_array = LAMBDA( range, LET( start_row, ROW(TAKE(range, 1, 1)), start_col, COLUMN(TAKE(range, 1, 1)), AND( ROW() >= start_row, ROW() < start_row + ROWS(range), COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range) ) ) ); is_in_row_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_rows, ROWS(range), current_row, ROW() - ROW(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_rows + start_index + 1), end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_rows + end_index + 1)), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_row >= final_start, current_row <= final_end) ) ) ); is_in_col_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_cols, COLUMNS(range), current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1), end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_cols + end_index + 1)), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_col >= final_start, current_col <= final_end) ) ) ); is_in_slice_of_array = LAMBDA( range, start_row, start_col, [end_row], [end_col], [include_slice_neg1_to_exclude], if( not(is_within_array(range)), FALSE, LET( final_end_row, IF(ISOMITTED(end_row), start_row, end_row), final_end_col, IF(ISOMITTED(end_col), start_col, end_col), row_match, is_in_row_of_array(range, start_row, final_end_row), col_match, is_in_col_of_array(range, start_col, final_end_col), selection, AND(row_match, col_match), mode, IF(ISOMITTED(include_slice_neg1_to_exclude), 1, include_slice_neg1_to_exclude), IF(mode = -1, NOT(selection), selection) ) ) ); is_in_interval_of_array = LAMBDA( range, row_interval, col_interval, [start_at_row], [start_at_col], [include_interval_neg1_to_exclude], if( not(is_within_array(range)), FALSE, LET( row_idx, ROW() - ROW(TAKE(range, 1, 1)) + 1, col_idx, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_row, IF(ISOMITTED(start_at_row), 1, start_at_row), start_col, IF(ISOMITTED(start_at_col), 1, start_at_col), row_match, IF(row_interval <= 1, TRUE, MOD(row_idx - start_row, row_interval) = 0), col_match, IF(col_interval <= 1, TRUE, MOD(col_idx - start_col, col_interval) = 0), selection, AND(row_match, col_match), mode, IF(ISOMITTED(include_interval_neg1_to_exclude), 1, include_interval_neg1_to_exclude), IF(mode = -1, NOT(selection), selection) ) ) ); alternate_cols = lambda( array, [start_with_even_df_TRUE], is_in_interval_of_array(array,1,2,,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE)) ); alternate_rows = lambda( array, [start_with_even_df_TRUE], is_in_interval_of_array(array,2,1,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE)) ); is_in_recurring_band_of_rows = LAMBDA( range, rows_in_pattern, first_row_in_band, [band_thickness], [include_selected_df_TRUE], IF( NOT(is_within_array(range)), FALSE, LET( relative_row, ROW() - ROW(TAKE(range, 1, 1)), row_in_pattern, MOD(relative_row, rows_in_pattern) + 1, actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness), is_in_band, AND( row_in_pattern >= first_row_in_band, row_in_pattern <= (first_row_in_band + actual_thickness - 1) ), include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE), IF(include_mode, is_in_band, NOT(is_in_band)) ) ) ); is_in_first_row_of_recurring_band_of_rows = lambda( range, rows_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_rows(range, rows_in_pattern, 1, 1, include_selected_df_TRUE) ); is_in_last_row_of_recurring_band_of_rows = lambda( range, rows_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_rows(range, rows_in_pattern, rows_in_pattern, 1, include_selected_df_TRUE) ); is_in_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, first_col_in_band, [band_thickness], [include_selected_df_TRUE], IF( NOT(is_within_array(range)), FALSE, LET( relative_col, COLUMN() - COLUMN(TAKE(range, 1, 1)), col_in_pattern, MOD(relative_col, cols_in_pattern) + 1, actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness), is_in_band, AND( col_in_pattern >= first_col_in_band, col_in_pattern <= (first_col_in_band + actual_thickness - 1) ), include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE), IF(include_mode, is_in_band, NOT(is_in_band)) ) ) ); is_in_first_col_of_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_cols(range, cols_in_pattern, 1, 1, include_selected_df_TRUE) ); is_in_last_col_of_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_cols(range, cols_in_pattern, cols_in_pattern, 1, include_selected_df_TRUE) ); is_in_header_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, 1), is_in_slice_of_array(array, 1, column_no) ) ); is_in_last_row_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, -1), is_in_slice_of_array(array, -1, column_no) ) ); is_in_first_data_row_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, 2), is_in_slice_of_array(array, 2, column_no) ) ); is_between_first_and_last_row_of_col = lambda( array, [column_no], [exclude_first_data_row_df_FALSE], is_in_slice_of_array( array, if(isomitted(exclude_first_data_row_df_FALSE),FALSE,exclude_first_data_row_df_FALSE)+2, if(isomitted(column_no),1,column_no), -2, if(isomitted(column_no),-1,column_no), ) );OneNote vs. OneNote for Windows 10
I received an email this morning reminding me of the impending end of support for the OneNote for Windows 10 app. As I do periodically when I am reminded about this, I launched the OneNote on Windows (OneNote M365) app and lamented how much less user friendly it is than its doomed cousin. The lack of a "Recent Notes" view is my biggest gripe, but the awkward "pop-over" search comes a close second. The tabs and search result are on an ugly grey background and everything is just slightly too small to read comfortably. Am I the only one?
Recent Blogs
- PivotTables can now collapse into a single cell with a #SPILL! error, so you can stay focused and resolve errors on your own terms.Oct 01, 2025
- Stuck on what to wear this Halloween? Ask Copilot for ideas and DIY tips!Oct 01, 2025
