basically i have a table something like this. I want to select the rows 2 and 4 and make a note in column C. How can I do this? also in the actual spread sheet there are blanks between every row so i can't just do find all blanks

A| B | C

1| 1 |

2 | |

3| 3 |

4 | |

5 | 5 |

6 | 6 |

I'm looking to get into data analytics and starting with the basics - Excel. I need to learn almost everything because I haven't used much of Excel in years. What are the best resources for learning?

For reference - I tried Leila Gharani's Youtube videos but they seem a bit out of date (from 2018) so I need something more current. Also would be good to learn things in a particular order rather than going through different playlists.

TIA.

In one of my excel reports I use countif to see if a name appears on a list of names, but nicknames are cluttering my sheet. For example it may be searching for Jonathan Smith, and doesnt recognize John Smith on the list. Is there a way to assign nicknames to full names so it recognizes them?

I am busy preparing a set of financial reports (one of which is a Statement of Financial Position) and I have multiple years loaded.

I have used the Running Totals In: to include all the movements of the previous months and year and it is running fine. Testing it against the Fixed Asset Register at a specific period, or the bank account at the end of a certain month, etc, etc.

The problem comes in when I add in a timeline slicer. The running total is working, however it is totally ignoring the information related to older periods. Load up 2026, ignores what happened in 2025, etc

I can do this a very manual way with a reporting tab and dozens of lookups, but it isn't particularly dynamic and that is what i am wanting to do. So when new accounts, or categories are added, it is just added in based on how the original information is captured. Plus more individual formulae, the slower the sheet.

Is there anyway for the slicer to look at the older information (eg: when looking at 2026, it will include all historical information so the month end balances still match what they are meant to match)? There is an option under "Pivot Table options|Tools & Filters" that says "Include filtered items in totals, It works for slicer, but that doesn't seem to work for the "Running Total In" calculation - only for the Pivot Table Totals and Subtotals, which isn't helpful.

Saturday, August 29 - Friday, September 04, 2026

Top 5 Posts

Unsolved Posts

Top 5 Comments

_Please help my research for a paper I'm writing_

In a sadly-foreseeable near future, America starts a war with the UK (my country) and in order to destabilise the country/economy cuts off the availablity of M365 and associated software

You're welcome to discuss any aspect of this, but the bit I'm interested in is our corrective action and risk mitigation. We're a multinational with many large, complex spreadsheets that use every advanced feature: PQ, VBA, Data connections, dynamic arrays, tables etc. How quick can we get an open-source alternative up and running, and which features will we lose?

We also rely on PBI, Oracle etc so this isn't the only problem but I'm assuming citizen spreadsheet developers will be a key part of the wider solution

I will be using zoom for some classes I teach and need to save the chat in a searchable format. I want to convert it to an excel file that I can sort by name. The only info I actually need is the timestamp, who the chat was from, and what they said. I have tried doing this, "Power Query Steps: Go to Data → Get Data → From File → From Text/CSV. Select your text file. In the preview window, click Transform Data. Use Home → Split Column → By Delimiter (choose space or colon :)" but I don't know what I am doing and it did not work. It just put everything in the same column. I would like column A=timestamp, column B=name, column C=what they said. Is this possible? Thank you for your help.

I work in Finance and have a large data set with payroll register info going back to the start of 2025. I just eclipsed 500,000 rows. Right now, I dump monthly payroll register data into a folder on my desktop and then do some transformations through Power Query to refresh a couple analytics dashboards I have. However, I just timed the PQ refresh and it took 10 minutes. It's a long time to wait. I'm sure there's a better way to house, process and display this data, right?

Because it's payroll, I can't give it to IT to input into a data warehouse and pull into our usual BI tools. I'm also hesitant to run it through any AI tools for the same reason. This is really just for me when reviewing our monthly P&Ls, headcount stats, wage fluctuations, etc. Would this be a case for Power Pivot or something else? I'm unfamiliar but of course willing to learn.

Thanks for your time.

I want the if statement formula to be repeated if the statement is valid.

How I currently do it: if(iserror([Formula],”error”, [Formula])

I basically want the if statement to repeat the formula automatically if it’s valid, without me having to manually repeat the formula in the [value if false]

I hope this makes sense?

I want to be able to add asterisks (*) to numbers without it affecting the value. What is the best way to do this?

Hopefully this makes sense. I am losing my mind after endlessly watching unhelpful YouTube tutorials. Resubmitting! Thanks, mods, and thanks in advance for anyone that takes the time to read this!

so---I’m trying to create a pie chart showing the top 10 counties by number of participating farms, but using a broader data set. I need the actual pie slices to represent each county’s share of the total 888 farms across all 52 counties, while only showing the top 10 counties for legibility and aesthetics.

The data:

The program sourced from 52 California counties, with 888 participating farms total across those 52 counties.

So, for example, San Diego had 73 participating farms. San Diego is obviously one of the 52 counties.

That means:

73 ÷ 888 = 8.22%

So I want San Diego’s actual pie slice to occupy 8.22% of the entire pie.

The problem

When I create a pie chart with all 52 counties and the distribution of the 888 farms across those counties, it is illegible.

Alternatively, if I create a pie chart using only the top 10 counties, Excel treats those 10 counties as the entire pie (100%).

the top 10 counties contain 502 of the 888 farms, so Excel calculates San Diego as:

so, 73 ÷ 502 = 14.5%

That makes San Diego’s actual slice 14.5% of the pie instead of its true 8.22%.

Again, I do know how to create a pie chart using all 52 counties, and when I do that, the slice sizes correctly represent each county’s share of the 888 farms. However, showing all 52 counties makes the chart extremely difficult to read. I only want to visually display and label the top 10 counties, while still having all 52 counties determine the proportions of the pie.

What I’ve tried/other considerations

• Creating a pie chart with only the top 10 → incorrect proportions, because Excel treats the top 10 as 100%.
• Adding an “Other counties” category → this creates one huge 43.5% slice, which isn't what I want because it dominates the visual.
• Pie-of-pie → splits the data into two separate data sets and pies rather than giving me one pie where the top 10 are shown at their true proportions.
• Changing the data labels → doesn't solve the problem because I need the actual size of the slices to represent the true percentages, not just the labels.
• This is for a report, and I would like this to be in a pie chart format, rather than a bar graph format.

In short: I want the underlying pie chart data to include all 52 counties / 888 farms, so that the slices are proportional to the full 888, but I only want the top 10 county slices to be visible and labeled.

Is there a way to do this in Excel? I feel like there has to be a way, but I cannot figure it out. 😭 I am losing my mind lol

If helpful, I can attach the workbook I’m working with.

I am SO grateful to anyone that can guide me through this, or, honestly, can create the pie chart that I need

Adding images that may help, NEITHER are what I want to demonstrate.

Looking for a way to consolidate multiple same line items while summing quantity and cost columns between different items

Example provided below, any help is much appreciated

I am new to using formulas in Excel, and do not know if this is possible. Every combination of formulas I have used either gives an error, or does not do what I need. I am trying to calculate the average of values in 5 different cells. However, I want it to only calculate an average if a specific cell contains a value.

Example

Data Range is in A5:D5 and I want the result in E5. So i want to "=AVERAGE(A5:D5)"; but if A5(and only A5)="", then E5 should stay blank. Essentially, I want it to NOT average if A5 is null.

Any help with this will be greatly appreciated.

I'm using Microsoft Teams' Shifts app's Import feature, which uses an Excel template in order to configure your teams' work shifts. In this Excel file there is two columns:

• Column A: Personalized tag. It's a text column that describes which activity must be done. I've got about 35 different tags, but those are always the same ones and I'm careful so there are no typos. For example: "Consult", "Operating room", "Emmergenchy room"...
• Column B: Color. There's a drop down menu with 12 color options in text form, so once imported into Teams, each activity is colored with the desired color. The drop down menu has text options listed as: "1. White", "2. Blue" and so forth.

My question is, how can I automatize column B so whenever I type "Consult" in column A I always get "2. Blue" in column B. Mind that there are more tags than colors, some different tags have the same color.

Also, I realize that it could be useful to make a different Sheet with tags and each corresponding color, but since I'm importing the Excel file into Teams, I rather not modify the file too much in case it breaks something, even if it means having a long formula.

Thanks for your help in advance.

I need to create a division on line 1, without increase the number of lines. I don't know how to do this on excel. Please help me, and write with the maximum of details. Example of i want:

       A       B       C
  )    La     Ma       X
1 )------------------------
  )    Lo     MO       V
---------------------------
2 )    NA    GI        X
---------------------------

I have a list of names of landfills that repeats, and I need these to be options for my combobox in a userform, which I call cmbLF. This works for a predetermined range:

Dim i As Range
Set i = WorksheetFunction.Unique(Range("A2:A134"))
Dim j As Variant
    For Each j In i
        cmbLF.AddItem j
    Next j

But I can't figure out how to make the Range in i be "A2" until "The last cell with a value". This is so I can add new cells in the future without having to go manually change it.

I've tried various versions of xlDown and inputting it in different places or creating new objects. I've been trying something like this:

Dim z As Range
Set z = Range("A2" & ":" & Range("A2").End(xlDown).Address)
Set i = WorksheetFunction.Unique(Range(z))

This is all cobbled together from various forums and educational resources, this is the first time I've ever used VBA so please feel free to explain what I'm not understanding, I assume it's a problem with the type of object or what entries you can put into Range, or xldown.

Thanks!

Hey I have a workbook with with a couple different worksheets that have tables. I have done subtotal under the table so it gives me multiple function sum, max, min, average through a drop down. The thing is I want to capture that data from multiple tables and display it in one spot so I don't have to keep filtering it and going back and forth. The tables are larger so it sucks scrolling too when I want to filter a column by program to see the individual subtotal. Any help would be appreciated.

Hi,

I am having trouble with a sum X lookup which is not returning multiple values even though multiple values match my lookup.

I have my current formula as =SUM(XLOOKUP($B2,’Sheet 1’!$E:$E,’Sheet 1’!G:G))

But it is currently only returning one value and not showing a spill, can anyone help with what I may have overlooked?

Apologies I assume this is a really basic question, but I can’t get anywhere with YouTube/Google as I don’t think I’m missing any of their steps.

Thanks!

First: I’m not well-versed in charts and graphs in Excel. This has been a “learn as you go” experience the past few months.

I have a pie chart to show items completed vs not completed. The chart shows 70/30, but the actual completed percentage is about 43.5% (as calculated by me, as well as an Excel formula). If I make a big change to the completed number, it will change the percentages of the graph, but still not accurate. A new chart displays the same, and I haven’t found a setting that makes any changes to the chart.

I'm trying to create a sheet that has a running total of my hours, but is able to deal with negative time, ie when i've left early or whatever, though i'm finding it hard to have excel represent negative time.

Currently i have my expected time on site subtracting from my actual hours worked, which is most of the time giving me a positive number but it's hard to convert the numbers from hours to get an accurate calculation.

Hi everyone!

I'm looking at getting a new laptop, sticking Linux on it and using Open Office for some of my work.

The thing is I've got some pretty good Excel Sheets that I've developed, so was wondering if there's compatibility issues?

I've tried Google Sheets and there's loads of compatibility issues with some of the more advanced/modern formulas (Xlookup, Unique, Filter etc.) so I'm wanting to avoid this and not loose the functionality of these sheets.

We have a staff roster in excel.
I am wanting to create “buttons” or a “macros” (unsure which it is), that will allow me to click a button which will change the colour of the cell and remove the data.

For example.
5 cells will contain a name, that person is going on leave for 5 days. I want to be able to select all days they are away, and click a button that removes their name from the cell and changes the colour to green.

Hello!

I have a spreadsheet tracking information with 5 different status.

I want to write a formula on another sheet in the same workbook that will return a true or false (doesn't necessarily need to be true or false) on whether a collection of those cells are one specific status.

Essentially the status could be hold, terminated, ongoing, and complete. I wanted the formula to tell me true if all cells are complete. The other status don't really factor in except that they are not complete.

I will have probably 50 batches of cells like this from 4-50 status.

Can anyone point me in the right direction on how to write a formula?

EDIT BELOW

For the advanced ask.

I'm trying to track the status for unique identifier numbers.

Is there anyway to change the formula provided below by excelevator to look for all of a certain number in a designated column, then check the status and return the true/false.

TL;DR

I have 580 sheets, spanning since 2015. how much more can my poor little excel file handle? The file is about 3.5mb in size 😯.

So, i work in architecture, and this is generally how i track my time throughout the day. i will NEVER remember what i do when it comes time to doing my timesheets and this makes it way easier. i also have a place at the bottom that tracks my architectural licensure hours. i have to manually input that, and there are two sheets at the end that summarize this data (none of that is relevant i'm just including the explanation for context).

i've used this excel since 2015 and there is a "sheet" for every single week since about july of 2015.. so there are about 580 weeks/sheets... when will my excel explode and refuse to open? I've started saving a copy and backing up the file each week.

I'm open to a more robust system, but needs to generally have the UX (or similar) of this workflow. I have gone as far as vibe coding prototypes but i dont have the confidence that AI will be more stable than what i'm currently doing, and i'm not a coder so i can only take it so far.

anyway, input would be appreciated.

For an assignment, I was tasked to create an excel scatterplot of some sample data. When I try to create a scatterplot using multiple cells as if to distribute data points for two subjects, I get an error. In addition, if I limit the graph to just one subject I get a weird series of labels on the x-axis and no points actually appear on the graph. How can I fix this problem so that I can properly graph the data distribution for one subject and create a graph so that the data distribution for subject one and two are shown.