Pinned Posts
Forum Widgets
Latest Discussions
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), ) );
Celebrating 40 Years of Excel: A Journey of Impact, Innovation, and Community
🎉 Celebrating 40 Years of Microsoft Excel This year, Microsoft Excel turns 40! From its debut in 1985 to becoming the world’s go-to tool for analysis, decision-making, and creativity, Excel has shaped how we work, learn, and share insights. 📊 Excel in Pop Culture Over the decades, Excel has appeared everywhere — from TV shows to internet memes — proving it’s more than just software; it’s part of our culture. ⏳ Excel Through the Decades From the first release in 1985, to the introduction of pivot tables, charts, Power Query, dynamic arrays, and now AI-powered Excel, the journey has been remarkable. 💼 Excel’s Impact on Business For entrepreneurs, enterprises, and analysts alike, Excel has been a cornerstone of productivity, unlocking insights and driving smarter decisions. 💚 A Thank You to the Excel MVPs & Community A heartfelt thank you to the Excel MVPs and community for teaching, inspiring, and sharing knowledge with millions of users worldwide. Your passion has kept Excel thriving for 40 years. 🎂 Here’s to the Next 40 Years From formulas and charts to AI and beyond, the future of Excel is bright. Happy 40th birthday, Excel! 🎉 #ExcelAt40 #ExcelJourney #ExcelImpactExcel at 40: Days of Innovation, Insight, and Impact
Lookup Logic and Formula Mastery (Days 12–13) Day 12: INDEX + MATCH INDEX-MATCH offers precision and control that VLOOKUP can’t match. It allows searching in any direction, handling dynamic ranges, and building smarter formulas. Takeaway: INDEX-MATCH remains a favorite for its flexibility. Day 13: IF Statements The IF function is Excel’s gateway to decision-making — from flagging errors to categorizing data and building nested logic. Takeaway: IF unlocks conditional logic, the foundation of intelligent spreadsheets. Formatting and Data Integrity (Days 14–16) Day 14: Conditional Formatting Highlight trends, flag errors, and guide decisions — all without formulas. Takeaway: Color isn’t decoration; it’s direction. Day 15: Named Ranges Named ranges make formulas readable, reusable, and scalable. Takeaway: A named cell is a documented cell. Day 16: Data Validation Prevent errors before they happen. Data validation ensures consistency and control. Takeaway: Validation is your first line of defense. Advanced Functions and Developer Thinking (Days 17–18) Day 17: LET and LAMBDA Reusable logic, cleaner formulas, and modular thinking. Takeaway: Write once, reuse everywhere. Day 18: Excel as Code Excel can be structured, recursive, and debuggable. Treat it like code. Takeaway: Excel is a logic engine, not just a grid. Visualization and Dashboards (Days 19–20, 24–25) Day 19: Charting Excel’s Visual History From bar charts to dynamic visuals, Excel’s charting tools have evolved to tell better stories. Day 20: Sparklines Tiny visuals with huge impact — sparklines bring context to rows and columns. Day 24: Dashboard Design Tips Whitespace, hierarchy, and purpose-driven visuals matter. Day 25: Data Storytelling Turn numbers into narratives. Takeaway: Good visuals don’t just show; they persuade. Automation and Integration (Days 21–23) Day 21: Power Query Transform messy data into structured insights with just a few clicks. Day 22: Power Pivot Build relationships, create measures, and model data like a pro. Day 23: No-Code Automation Workflows that connect Excel to the Power Platform. AI, Python, and the Future of Excel (Days 26–32) Day 26: Copilot and Python in Excel Ask questions, run code, and automate analysis. Day 27: Excel in Schools and Turing Power Excel teaches logic, empowers students, and builds future thinkers. Day 28: Driven Impact Excel powers AI models and NGO dashboards — smarter sheets, bigger change. Day 29: Excel and GitHub Trigger reports from commits. Excel meets DevOps. Takeaway: Excel is part of your automation stack. Day 30: Excel Humor REF errors. Merged cell chaos. We have all laughed and cried. Day 31: Quick Excel Tips CTRL + SHIFT + L CTRL + E ALT + = Day 32: Excel and AI Predictions From reactive to predictive with natural language, smart forecasts, and proactive insights. Takeaway: Excel is not just reactive; it is predictive. Global Impact and Community (Days 33–36) Day 33: Global Impact and MVP Stories From classrooms to boardrooms, Excel empowers a global community. Takeaway: Excel is powered by people — educators, creators, and problem-solvers. Day 34: Excel in NGOs Supporting development, transparency, and impact measurement. Day 35: Excel in Enterprises Scaling models, compliance, and business-critical decisions. Day 36: Excel in Everyday Life From personal budgets to side hustles, Excel powers daily problem-solving. Final Thoughts Excel is more than a spreadsheet — it is a platform for logic, design, automation, and storytelling. As we celebrate 40 years of innovation, I am grateful for the Excel MVPs, product teams, and the global community of users who keep building smarter with Excel. What is your favorite Excel feature or moment? Share your thoughts and let’s celebrate the journey together.How to Be an Excel Detective: Finding and Highlighting Formulas
Hi everyone, I recently wrote a blog post on some simple, yet powerful, techniques for anyone who works with Excel spreadsheets, especially those with complex data. I wanted to share a summary of it with this community, as it might be helpful to others who are looking to understand and protect their work. The post covers two main things: Quickly Revealing All Formulas: A simple keyboard shortcut (Ctrl + `) or the "Show Formulas" option can instantly reveal all formulas in a worksheet. This is a great way to quickly see how a spreadsheet is structured. Permanently Highlighting Formulas: The article shows how to use the "Find & Select" > "Formulas" feature to select all cells containing formulas and then permanently highlight them with a fill color. This visual cue can help prevent accidental edits and protect your data. Watch This in Action: For a step-by-step guide on how to use these techniques, you can watch the video on my https://www.youtube.com/@BIGurus. 🔗 https://youtu.be/1x-1dbqlWXk You can also read the full article here: https://medium.com/@anandsharad/how-to-be-an-excel-detective-finding-and-highlighting-formulas-fe9d4fdbc1b1 I'd be happy to answer any questions you have or discuss other Excel tips and tricks in the comments!📣 ExcelAt40 – Days 5 to 10 Recap
Hello Excel Community 👋 I’ve been running a daily series called #ExcelAt40, celebrating 40 years of Microsoft Excel through stories, innovations, and personal reflections. Here’s a recap of Days 5 to 10 — each post dives into a pivotal moment in Excel’s evolution: Day 5: Charting the Future Before dashboards ruled the world, Excel gave us the power to visualize data with clarity https://www.linkedin.com/posts/olufemi-olamoyegun_excelat40-day-5-charting-the-future-activity-7364386306572828673-dpj8 🧠 Day 6: Formulas That Changed Everything From =SUM() to =XLOOKUP, Excel taught us how to reason with logic. https://www.linkedin.com/posts/olufemi-olamoyegun_excelat40-formulas-that-changed-everything-activity-7364968873474908161-PGNb 📈 Day 7: PivotTables Debut Celebrating Professor Pito Salas, the visionary behind dynamic data analysis. https://www.linkedin.com/posts/olufemi-olamoyegun_day-7-pivottables-debut-activity-7366077860282896384-euqr 🧬 Day 8: VBA Rises In 1997, Excel unlocked automation with Visual Basic for Applications. https://www.linkedin.com/posts/olufemi-olamoyegun_excelat40-day-8-vba-rises-activity-7366342708942008320-yJL7 🧩 Day 9: The Ribbon UI Revolution Excel 2007 introduced the Ribbon a bold redesign that changed how we work. https://www.linkedin.com/posts/olufemi-olamoyegun_day-9-the-ribbon-ui-revolution-activity-7367783381973053440-I-KK 🛡️ Day 10: Y2K Audits Excel’s role in averting global disaster during the Y2K crisis. https://www.linkedin.com/posts/olufemi-olamoyegun_y2k-audits-activity-7367795370120187904-5ewY I will love to hear your thoughts: 💬 Which Excel feature changed the way you work? 📌 What’s your favorite moment in spreadsheet history? Let’s celebrate the legacy together. #ExcelAt40 #MicrosoftExcel #OlufemiBuilds #NoCode #TechLegacy #ExcelCommunityExcelAt40 — Day 4: The Birth of Financial Modeling
When Excel launched in the late 1980s, it wasn’t just a new spreadsheet tool — it was the beginning of a revolution in financial modeling. The =NPV() function became a cornerstone for analysts, enabling precise valuation of future cash flows without leaving the grid. This wasn’t just a formula. It was a gateway to: 📈 Investment analysis 🏦 Corporate finance modeling 💼 Startup valuation Excel empowered a generation of finance professionals to build models that shaped decisions, raised capital, and forecasted futures. As we celebrate 40 years of Excel, we honor the formulas that built industries — starting with =NPV().Excel at 40 Week 1: Days 1–3
Celebrating 40 years of Excel in finance, analytics, and beyond On September 30, 1985, Excel launched on the Apple Macintosh. It replaced paper ledgers, VisiCalc, and Lotus 1-2-3 — and changed finance forever. From macros to Power Query, from PivotTables to Python, Excel has evolved into a powerhouse for financial modeling, auditing, and analytics. This 40-day series shares: ✅ 40 lessons from 40 years of Excel in finance ✅ Real-world use cases with Power BI and Microsoft Fabric ✅ Tips for mastering Excel’s most powerful formulas ✅ My journey as a finance analyst using Excel daily Day 1 — A Finance Legacy Begins 📅 From 1985 to 2025: A visual timeline of Excel’s evolution. Revisit the journey from the first release on the Macintosh to today’s AI-powered, cloud-connected version. Day 2 — Highlight Past Event Dates Automatically 🏅 Excel Tip: Highlight rows automatically when the date is 3 or 6 months old — no VBA required. Formulas used: 3 months → =AND(ISNUMBER($A2), $A2 < TODAY() - 90) 6 months → =AND(ISNUMBER($A2), $A2 < EDATE(TODAY(), -6)) ✅ Apply through Conditional Formatting to keep outdated entries visible without manual updates. 🔗 https://lnkd.in/dVS_DfdB Day 3 — The Evolution of Scale From 4 million cells in 1985 to over 17 billion today, Excel has grown into a data titan. This carousel highlights major milestones and its transformation into a programmable, AI-powered canvas. 💡 From finance models to fantasy football, Excel powers it all. 📌 Satya Nadella on Excel: “Excel formulas, the world’s most popular programming language, is now Turing-complete.” https://lnkd.in/dyex2ymK “Excel with Python is like GitHub with Copilot.” https://lnkd.in/dti5WfsQ 🔗 https://lnkd.in/dp58hDnP Follow the Series 📅 New lessons every week — follow along as we count down to all 40 lessons. #ExcelAt40 #MicrosoftExcel #ExcelTips #MicrosoftFabric #PowerBI #ExcelForFinance #FinancialModeling #AuditAutomation
Функції
Доброго дня, маю питання що до функцій, є така таблиця, ліворуч зверху (червоним відмічено) є дата, також по середині є рядок з розбивкою по днях ця середня дата міняється згідно тієї дати що зверху, і є рядок із буквами Р,П,С,РС, потрібно хробити щоб у кінці автоматично виписувались дати (у тому стовпці де пише Період) згідно того де стоять букви Наприклад 2.12.24,15.12.24, стоїть буква "П" потрібно щоб у кінці автоматично записувались ці дати у другому стовпці жовтих стовпців так само із буквами "Р","С" щоб вони записувались у своїх клітинках, а букви "РС" додавались у стовпці де прописано "Р","С" до прикладу як вказано на другому скріншоті
Copilot in Excel riddles
Hi everyone, over the last few weeks we have had a series of posts to show you some of the things that are possible to do with Copilot in Excel. Today we’re going to try something fun: tell me a riddle about an Excel feature Sure! Here’s a riddle for you: I can sum, I can average, I can count with ease, I can find the max and min, if you please. I live in a cell, but I’m not in jail, What am I? Can you unveil? What Excel feature am I? What is the answer? The answer to the riddle is a formula! 😊 Formulas in Excel can sum, average, count, find the maximum and minimum values, and perform many other calculations within a cell. Give me a riddle where the answer is conditional formatting Sure! Here's a riddle for you: I change colors based on rules, Highlighting cells, making them cool. With red, green, and yellow hues, What Excel feature am I, can you deduce? What am I? 😊 Maybe try with your friends or coworkers and test their Excel knowledge in a fun way. Over the coming weeks I will continue to share more examples of what you can do with Copilot in Excel. Thanks for reading, Microsoft Excel Team *Disclaimer: If you try these types of prompts and they do not work as expected, it is most likely due to our gradual feature rollout process. Please try again in a few weeks.
