Hi, I have a table with a set of recommendations based around skills.

Basically, it is organised like:

So I want to be able to filter all the rows (ideally to another sheet). So if someone is a 0 in distance running, I need all the rows for distance running with a rating of 0 or above. If they are a 1 for problem solving, then all the rows for 1 and above.

Is that possible? Or should I be looking to reorganise my data somehow?

Hi,

I am trying to have the waterfall chart auto hide where row value = 0, ex:

https://preview.redd.it/6raitjnsnlzc1.png?width=574&format=png&auto=webp&s=f2b1e3680b29426196cb930ab626311fd8a31bdd

For the source data used for the waterfall chart, A-J are formula calc'd off another tab and all A-J may sometimes have values. I hoping there is a way to for waterfall chart to identify and auto-hide where value = 0 instead of having to manaully adjust the charts.

Thanks!!

I have a set of data of target lower and upper values that when pasted into a template file added 5 decimal places to each. I need to change some of these to 2 , 1 or even no decimal places. Based on a column with the number of decimal places needed, is there a formula to do it quickly?

https://preview.redd.it/8ev3qsi5klzc1.jpg?width=878&format=pjpg&auto=webp&s=867cf18cd841ccad4e3fb77dcfec06ccffe8b80f

Hello, I am wondering how I can add Pay period number to a calendar lookup table within power query. Would Ideally start at 1 and go through 26 then go back to 1 every 14 days.

I have a calendar lookup made in power query already, I am also open to just making a separate calendar table outside power query that would create the pay periods however I am struggling to find a way to even do this.

Thanks

Hi,

I'm currently trying to create a formula to choose a cell in between two of them...

I would like to chose the first cell if the 2nd cell doesn't have information in it... how am I doing this..

Thanks in advance!

Hello,

I am currently working on a workbook that shows projected revenue.

I would like to know how to create a graph in sheet 1 that carries over to any sheet.

context-

sheet 1 is a template and can be filled in with different numbers to create different outcomes.

the graph in sheet 1 is a bar chart that shows initial costs & change orders, total revenue, total cost, misc expenses, and employee salary information.

sheet 1 is copied into sheet 2 and all numbers can be edited but i would like a graph to automatically appear in sheet 2 that uses the same cells from sheet 1 but on sheet 2.

is this even possible?

I want certain cells to automatically be filled in Orange when the year input in that cell was 35+ years ago.

In one worksheet (Sheet A), I will have Update Year values listed in individual cells in 3 comluns. In another worksheet (Sheet B), I have the same # of cells/columns that individually calculate the # of years since the update year listed in the cells on Sheet A. Formula for that is: =SUM(F3-'Sheet A’!AC5) I am using this method instead of just basing the formatting conditions off the Update Year itself so I don't have to update the formulas every year when the year changes.

So basically what I want is if the year input into Sheet A AC5 is 1989, which was 35 years ago as calculated in Sheet B G2, that cell AC5 with the value that was more than 35 years ago to be automatically filled in as Orange. BUT I don't want to have to do the individual formula in Conditional Formatting for each individual cell if there's a way to basically bulk associate the formatting like you would formulas.

FYI I'm still very much a beginner in the more complicated side of excel, so I apologize in advance for having to ask for simplified explanations 😅

Thanks in advance!!

I’m relatively new to excel and I’m wanting to create a kind of hub to track projects and daily tasks at work - does anyone know of any resources that might be able to help with this? Or any layout suggestions?

Is there a way to keep a workbook shareable. Someone keeps unsharing a group log. It is not worth making office drama over. But if there is a way to lock it into shareable mode I would be interested.

Getting the error message for this. I have looked into it a bit and it seems most are having the issue due to passwords shared users, etc. Well, my document isn't password protected, and I am the only user. I access on my phone and laptop. But I get the error on my phone regularly, and only get it on mobile.

Hi all, I'm having an issue with a combo graph. As you can see in the picture, I have 2 axes, the primary has negative values and the secondary positive ones. I'd like the secondary to have the positive values above the X axis. I thought that setting "Axis value" equal to 0 would do the trick, but actually it doesn't change anything. Could anyone help me?

https://preview.redd.it/nfmdmasb4lzc1.png?width=1119&format=png&auto=webp&s=a0f341daff943d6f78a9f405b08d98eb05e5e857

Hi, is there a way to copy and paste a list into two separate columns in excel? For example, I have the following in the notes app:

4/18 2.5 4/19 1 4/22 1 4/23 6 4/25 7.5 4/26 1.5 4/29 .5 4/30 2.5

I’d like to have the dates in the first column and numbers in the second. Thank you.

Long time lurker. First time posting!

What would be the best tools for consolidating multiple excel workbooks and sheets and then querying/manipulating the data to produce specific individual data points and tables? The required reports and data inputs are the same every month so I’d like to automate this in some way.

A specific example is data relating to vacancies and positions filled. Various different departments and divisions complete the same workbook which has multiple sheets all recording different aspects. Then all these sheets need to be consolidated into an overall workbook which has the same sheets but consolidated data. The data is consolidated either by summing the data OR by taking all the data from the various departments and adding it all to one long list (eg a complete listing of placements in the month). Then the consolidated tables need to be further manipulated to provide specific statistics (eg number of females under 30 employed during the month) or turned into summary tables.

I’ve heard about Power Query and Power Pivot but haven’t used either before. Is there one that would be better suited to this kind of data and requirement?

Note: this is currently being done manually which is unbelievably painful.

And are there any recommendations for online courses to learn these tools? I’m quite happy with paid courses but would like something that gives a very good overview/understanding of all the capabilities of the tools.

Thanks!

Not from a calc or excel cell's, but a screenshot of them. Must be OCR.
Here's an example of what I need to convert:
https://i.postimg.cc/g0ss971B/y-Fc5ro-EKi-L.png

And I need to convert it to this below, I need to extract the numbers and separate each cell with a comma:
2 ,3, 5, 12, 2, 1, 8, 9, 42, 45, 1, 23, 32, 4, 12, 3

-Thank you.

I have an information table with two columns of sites, and for each row, depending on these two previous cells, a name is assigned. It's worth noting that in some cases, like SITE 1 A, regardless of SITE 2, it will be assigned Name_1. Then, in another table that I have to fill out, I'll write a name from the SITE 1 list in one cell and another name that may not appear in the SITE 2 list in another cell. Depending on these cells, I want to fill out a third cell with the corresponding name.

Is this possible to do in Excel with some formula or in some other way?

Thanks in advance!"

EDATE function not working when expanding this table:

https://preview.redd.it/yzrbry54skzc1.png?width=363&format=png&auto=webp&s=9f9dd4a0c61d139a4d0e2724a12333c73bb0a425

I tried to add DATEVALUE to the formula, but it doesn't seem to work, any ideas on a fix?

im working on a survey dataset (the horror) and i have stumbled on an issue that im wondering if there's an easy solution for it.

Suppose i have a following mini table [image below] andd what im trying to do is stack the column 1 and column 2 in one single column, However i also want to stack along with that, the corresponding values of each value of the column. So the output might look smth like the 2nd table in the image below.

Note: there is no unique identifier.

I would die happily if anyone can provide me with a solution because the set im handling has more than 300 columns of this type, and lets just say im suffering lol

https://preview.redd.it/wlzc19qkrkzc1.png?width=492&format=png&auto=webp&s=956e664a5381ee5d129c283358f19e36370bd0e7

I have a sheet with date information and have forgotten how I set it up.

Column A holds dd/mm/yyyy dates. Column B is automatically populating with =TEXT(Ax,”MMM”). Row insertion has broken this auto population and I cannot figure out how to reapply this to the whole column.

Can anyone advise or point me at a short that covers this? I’ve spent so long looking and am getting disheartened.

I'm currently trying to create a form that will help management in my company to create their own job adverts by auto populating information into a cell based on what option is selected in a drop down option. For example the drop down options are job roles such as technician, driver, etc. and then another column called job summary that will say "the role of the technician is blah blah".

At the moment I have a seperate data validation tab with the list of all job types with each cell named for example Technician1 and the next column being the text used for that job which I've named Technician2.

The formula I'm using at the moment is =IF(D10=Technician1,Technician2,IF(D10=Driver1,Driver2))

This works fine with it only being two entries but I'm potentially going to be adding dozens of job entries and I don't want to keep adding to this formula, is there a way to easily apply the formula to several rows Using Excel 2019?

Hi,

Sorry if this is low level question. I have a spreadsheet where I keep track of elements of my finances. I am tracking gains/losses I make on investments and I have a set of cells that track the highest profit value investments have made. I update this manually.

Is there a way to automate this via formula or something?

So, if the cell value is £400 profit and it goes up to a new high value of £410 I would like that value copied over to a new cell that tracks the highest value. When it drops back down in value to £405 it does not change the cell value that tracks high value, that remains at £410. And no matter how many different values it is, it won't change until the value goes above £410 and then it copies the new high value and so on.

Can this be done?

Thanks in advance

EDIT: I have tried searching, but could not find answer

edit: think i got it to work :)

hello

my excel knowledge is very limited but i will try and explain my situation the best i can.

i have a spreadsheet where people call in, tell us when and where they are and when they leave for safety reasons, i won't go into details about why, but what i want is for when the "out" cell is filled in, for that entire row to change colour.

so for example, when the F column is filled in, in this case F31, for the entire row 31 to change colour.
i've looked up other tutorials and tips but can't seem to find what i'm looking for

thanks in advance :)

https://preview.redd.it/v1nk6wdxckzc1.png?width=2113&format=png&auto=webp&s=827a09d5206dd5d2cd8f03d6a5fe0532bf7a9b2d

Hey Guys,

I thought this would be simple however I am having issues. I have a table of data that counts Number of pass and fail rates per day and the subsequently works out the percentage pass rate up to the current day. My issue is, there are times when there is no data for the first few days (but I still need to show it) and therefore it won't show anything. I need this to show as 100% still. Is there a way to do this maybe with an IF Formula? Will add screenshot in comments.

Hi guys,

so I get some files that look like what's on the left

https://preview.redd.it/k0uyzya09kzc1.jpg?width=734&format=pjpg&auto=webp&s=2bcb0739b0b8aab839c24f13a07273bb788011fb

And the numbers on column A should be moved to a line where in column B it says either 'sum' or 'sum xxx' or 'xxx sum'

Which means the upper left example should look like what's on the right side

Is there any smart way to do this?

Thanks in advance!

I need to count how many entries of a specific catalog we have. The thing if, the catalog name is fine, but in the bigger table, every single one has "FyXX" in front of it.

I can't split text to columns effectively cause it's a table with over 70 catalog names total, and over 20k entries X_X

Below is a small scale example:

https://preview.redd.it/g782dv0y4kzc1.png?width=405&format=png&auto=webp&s=fc51fe8909f191058cf5b66884e92a718e92b3ca

Basically what would be ideal would be to have a countif which used the Catalog Name as partial text.

I've tried =COUNTIF(C:C, "*E2*") but all it seems to read is the 2 and it counts every entry that has a 2 in it.

Any ideas how I can tell excel to count, in column B, how many entries there are with each Catalog Name from column E?

If I do: =COUNTIF(B:B, CONCAT("*",F2,"*")) excel doesn't seem to count everything. I have 28 entries for a specific catalog, and with concat I only get a count of 16.

I have a data table that is refreshed with brand new data each week. I want to create a chart (a line graph) that will track progress, so rather than rewriting itself each time the source data in the table is refreshed I want it to add a new data point, so I can see the trend over time. The x-axis will be number of days since baseline, and the y-axis will be numbered for the running total. I'm unsure if this is even possible, let alone how to do it. I'm using Excel 365

Hello community, I saw this spreadsheet on a website, I don't remember which one, but could someone who knows Excel help me create this spreadsheet? I don't know how to sort the columns from a to z without losing the data in other columns, can someone help me? A spreadsheet: imgur.com/a/huYMiLy

ok so in this game, it goes like this 3 tier 1 = 1 tier 2 3 t2 (tier 2) = 1 t3 3 +3 = 1 †4 and so on, how can i make a spreadsheet where you input the number of tier 1 and it outputs the most optimal (just based on tiers) Example: 68 t1 = 2 t4 + 1 t3 + 1 t2 + 2 t1

Hello all!

I’ve made a macro that converts coordinates (40.71427,-74.00597) into a map (screenshot imported from google maps) in excel. The next step would be to transfer this image to a PowerPoint, as I need to to this process for over 20,000 coordinates. ¿Is there a way to automate this?

Dear Community,

One of my users was using an Excel workbook which was created with an old excel version. He kept using the new Excel app with the old workbook and always skipped the request to switch to the new one.

Yesterday he accidentally accepted to switch to the new version, and it deleted 2 months of work.

If you go to version history it only shows a date from two months ago. Also in unsaved workbooks there it isn't showing up. It was saved on SharePoint the entire time.

Hope somebody experienced this as well and has a solution?

Thanks in advance.
BR
Jack.

I made a nicely functional workbook. Shared it, and the other person not using Office 365 couldt work with it. For example: VSTACK wasnt supported...

How do i know wich functions i shouldt use in this case?