Questions tagged [microsoft-excel]
A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.
24,714
questions
0
votes
0
answers
5
views
Collapse and sum total corresponding values
hoping I explain this correctly:
I have a large spreadsheet like the below
enter image description here
I need to collapse multiple rows (with same order number) and total the individual cost prices ...
- 1
0
votes
0
answers
7
views
How do I stack multiple columns into one column in Excel 365 Mac
I currently have my data in different columns after using a Text To Columns feature. Now what I want to do is to put them all into one column. Can you please advise what formula to use or is there a ...
- 1
0
votes
0
answers
11
views
How do I plot data vertically instead of horizontally?
My data is reading horizontally right now but I want my data to read vertically (e.g. I have an 'x' column with data below it and likewise for 'y' so I can plot with vertical data instead of my ...
0
votes
0
answers
2
views
In Excel 2016, how do I link a CSV where you can specify the file to import like I could in Excel 2013?
In Excel 2013, I used to be able to specify a sheet to Data Refresh and at the time of creating a linked csv file, I could prompt for the location or file with every Data Refresh. In Excel 2016, I am ...
- 6,112
0
votes
0
answers
16
views
How to output multiple value on excel?
I am using excel to perform some analysis.
I have a spreadsheet(Base) that contains the fields: ID Number Type Roles
and another spreadsheet (Results) that contains the fields: ID Number
How Can I add ...
- 11
0
votes
0
answers
7
views
Have problems sharing an Excel Sheets
I am working on an Excel Book and I want to share it with some people.
All the Sheets are linked to another Excel Files, so I need some help with sharing this Excel without any #REF or ERROR in the ...
- 1
0
votes
1
answer
11
views
Is there a way with VBA to hide or disable Show Hidden Excel Power Pivot?
Is there a way with VBA to hide or disable "Show Hidden" button in Power Pivot? So no one can see the raw data.
- 1
0
votes
0
answers
12
views
Excel - Aggregating the same ID pairs with value subtraction
Source data :
Result I am looking for :
I would like a formula to do this for thousand of occurrences (always by pair, it is flow data). The smallest value should be subtracted from the larger. The ...
- 123
1
vote
0
answers
23
views
What is syntax error of this [@[bar foo]] formula?
=IF([@[Amani''s Taxable Income (by bracket)]]=0,0,IF(([@[Amani''s Taxable Income (by bracket)]]+[@[If taxable income is over:]]+1)>=[@[but not over:]],D8,[@[Tax rate]]))
The system said there was a ...
- 11
0
votes
0
answers
16
views
Modified date in File Explorer CHANGES even if I am just HOVERING over the date and have not opened the document
I can see there are solutions to fix, but I cannot go and change hundreds of documents manually. I never experienced this problem in nearly 30 years of using Word and Excel. It started last fall or ...
- 1
2
votes
1
answer
143
views
How to add a variable number of leading zeroes in Excel?
I have a spreadsheet with 10,000 rows, and in one column, I have an incrementing value:
1
2
3
...
10000
Now I want to format this so that the cell with the value is always 32 characters long by ...
- 159
0
votes
0
answers
19
views
sampling 8 different items from a population of 13 different items iteratively until each of the 13 items have been sampled a least once [closed]
The purpose is to determine how many sampling cycle (with probability 99.9%) is necessary in order to obtain that each of the 13 items in the population have been sampled at least once. I have an R ...
- 1
0
votes
0
answers
15
views
How to idenitfy cells
Hi good internet people,
I wonder if someone might be able to help me figure this out. My excel knowledge is very basic.
Using an end date (Fully Grown) and subtracting a caluculated number of days I ...
- 11
0
votes
0
answers
17
views
Copying values from one column based on the values in another column in Excel
I am trying to something that I feel should be simple VLOOKUP or XLOOKUP and have tried looking it up online with no success.
I have the following dataset:
Group
Code_Present
Code_Missing
Description
...
- 1
0
votes
0
answers
23
views
Excel - Get the number of working hours which counts as night hours
I have an excel sheet where I enter the start and finishing times of each shift. But during night shift you will earn 25% more, so I need to calculate the number of working hours which counts as night ...
1
vote
0
answers
11
views
Change PivotChart source from one PivotTable to another PivotTable
I have a PivotChart in Excel 2016 that is currently based on PivotTable-A.
PivotTables A and B are identical, other than the fact that they have different data sources:
A's data source is an ...
- 145
0
votes
0
answers
19
views
Vertically separating excel sheet
Is there a way to have data on the left side of a sheet and a chart on the right that is always visible? ie.. the chart is 'pinned' so that while you're scrolling through the data you can always see ...
- 413
0
votes
0
answers
19
views
Calculating Time of Payback to show in Years, Months and Days remaining
I have a solar system and am looking to calculate the projected time to pay back the initial cost from the monthly savings.
I.E. I have paid back 0.15% in 1 month, how do I show the years, months and ...
0
votes
0
answers
8
views
Excel style table in Google Sheets [migrated]
In Excel, I can create a table (n.b., not a Pivot Table) through Insert > Table with headers where I can sort and filter the table among other functions.
Is there a similar functionality in Google ...
- 123
2
votes
0
answers
22
views
Change PivotTable source from external data source connection to table/range
In Excel 2016, I have several PivotTables (and corresponding PivotCharts) that use external data sources. The data sources were created via Microsoft Query (an Excel mechanism) and have complex SQL ...
- 145
-1
votes
1
answer
41
views
How do I find out if two columns are unique?
I have an Excel spreadsheet.
The columns in my sheet are
CaseID,EmployeeNumber,FirstName,LastName,Status
The case ID are used multiple times so it is not unique.
I wanted to find out how many records ...
- 409
0
votes
0
answers
19
views
Is there a way to drag a SUMIF/(S) formula down vertically but pull information horizontally, and vice versa?
Essentially, I am trying to fill information into Sheet A (SA) using =SUMIFS but the information that it is pulling into the actual formula comes from Sheet B (SB) and is organised in horizontal form.
...
- 1
0
votes
1
answer
17
views
Can I display the "Refers to" portion of a named range as text?
I am an educator writing an exam on basic excel usage. I ask students to set print area. I would like to have a single cell that displays the cell range they have selected as their print area.
Print ...
0
votes
0
answers
14
views
How can I tab in and out of a combobox in Excel?
I want to use the tab key for expediency and type a time value to look like the image below with a combobox, so an hour can be typed, [tab key] a minute can be typed [tab key] and a default value of ...
- 101
0
votes
0
answers
9
views
How can I use text-to-speech in a Microsoft Excel macro on an Apple Mac?
My understanding is that Microsoft Excel for Windows has a 'Speak Cell' button in the Quick Access Toolbar. However, the Mac version of Excel doesn't have this. Instead, it has a 'Speak Selection' ...
0
votes
1
answer
24
views
Print Excel Spreadsheet using Template?
So Let's say I have a spreadsheet
No
Name
Class
Gender
1
Jack
Science
Male
2
Rose
Science
Female
3
Kevin
Social
Male
4
Olive
Social
Female
and I have template
No
Name
Gender
1
2
3
4
How to ...
- 1
0
votes
1
answer
42
views
Excel: Merging rows into one row based on three common column values and arranging other values
I have this generated data to excel and want it to sort it like in the image below:
I will be grateful if anyone will help me find a solution.
I'm new to excel.
- 11
1
vote
1
answer
24
views
Excel: How to highlight value if greater than or less than previous column/row's last non-blank value
I have a vehicle data spreadsheet with 3 columns to record the Odometer.
Odometer during repair service or fueling
Odometer at the Beginning of a trip
Odometer at the End of a trip
There are gaps ...
- 13
0
votes
1
answer
31
views
Excel MATCH function returns #N/A when there is a match
In an Excel spread sheet, I have a row containing all dates of the year, and also a list of UK bank holidays. I wish to use conditional formatting to highlight dates that fall on a bank holiday.
When ...
- 1
-1
votes
0
answers
23
views
Is there a better alternative to mail merge for sending out emails containing excel tables?
I need to send weekly scorecards containing data that is stored in excel tables. The tables are currently created in word with a .csv file as the source. It is a lengthy process to combine the data ...
0
votes
1
answer
32
views
Why does _xlfn. appear before formulas in excel 2021 for functions that worked perfect until feb 1st 2023
Since today I suddenly have _xlfn. appearing for certain functions and I get the #name error. It seems to have to do with using new functions in older excel versions.
I just use Office 2021 (Version ...
- 1
0
votes
0
answers
16
views
Pivot graph show only data based on cell colour
I want to set up a pivot graph who only shows values based on the fill colour of the cell.
The graph should should the amount of cells with (e.g.) green, yellow, and red cells.
Can someone advise on ...
0
votes
1
answer
18
views
Text comparison in Excel - What formula should I be using and how to do it?
I have 2 sheets, the first sheet contains a coulm of Emails and the second sheet contains 2 columns ( Some Emails & Status). How to make excel get the status of each email from sheet 2 into sheet ...
- 1
-1
votes
0
answers
11
views
Can Excel Desktop external reference Edit-Protected Workbook, (without Enabling Editing for security reason)?
Because Office 365 Enterprise Apps have yet attained sufficient quality when getting Table from Image, I found a online OCR Service who provide much accurate Table from Image but unfortunately in ...
0
votes
1
answer
29
views
Excel Finding a Range
I feel silly, because this should be easy, but I can't figure this out.
I have a series of columns of data. It always starts at row number 11, and is always in columns E,F,G,H,I, and K. The problem is ...
- 119
0
votes
0
answers
22
views
Excel Drop down filter - How do I add a option to unselect filter and display all data?
Don't know the exact wording, so apologises if title seems confusing.
In short I'm doing up a small data input table for my local swimming club whereby they can enter in the raw data of the competing ...
- 101
0
votes
0
answers
31
views
How can get my 2023 dates to stop acting like 2022 dates in Excel (Pivot Graph)?
I have my 2022 dec dates as well as my 2023 dates in my table. When I plot it on a Pivot graph, the jan 2023 dates acts like 2022 dates and it comes before my dec 2022 dates. Is there a way I can fix ...
- 101
1
vote
1
answer
26
views
Multiple If (and (isnumber (search.... First clause works but subsequent clause' fails
I am using O365 and am having trouble with this formula looking at two cells and returning the appropriate value based on those cell's values.
I have two columns: Vendors and Amount. Vendors will ...
- 13
0
votes
2
answers
46
views
I have two cells each with comma delimited values, I want to combine these into one cell
I have two excel cells(A1 and B1) each contains comma delimited values in, I want to combine these values into one cell (C1), so that the first value from cell A1 is "linked" to the first ...
- 1
0
votes
0
answers
12
views
Complex Excel Chart [closed]
I am trying to think through how I can chart complex prescription data in Excel. I am trying to understand the amount of each drug that was prescribed over time. I have the a spreadsheet that contains ...
- 1
0
votes
0
answers
9
views
hi, im trying to create a pivot table that has documents received in a particular week and documents replied in that particular week. data source
im trying to create a pivot table that has documents received in a particular week and documents replied in that particular week. In data source, documents replied column has some empty cells since ...
- 1
0
votes
1
answer
27
views
Unexplicable #NUM Error in Excel
Attempting to create a correlation matrix in excel, I made the following formula:
=CORREL(OFFSET(A2:A1001,0,ROW()-ROW(G2)),OFFSET(A2:A1001,0,COLUMN()-COLUMN(G2)))
ROW()-ROW(G2) and COLUMN()-COLUMN(G2)...
- 273
0
votes
0
answers
16
views
VBA - paste multiple Excel ranges into Outlook as images
Trying to create an Outlook email that has Excel ranges pasted as jpg in the email, along with text before and after. Something like:
Text
Image 1
Image 2
More Text
However, I cannot figure out how to ...
- 1
0
votes
1
answer
22
views
How do I create a calculated field in a pivot table that simply divides two other columns in the pivot table?
I have these data in Excel:
I created a pivot table that is supposed to calculate the percent of each state's counties that qualify for a certain government program (this is artificial data, though).
...
- 764
0
votes
1
answer
16
views
Overlaying different dates with corresponding data points in the same Excel graph automatically?
I have a dynamic set of data that is filtered in from a table, with different arrays of data assigned to potentially overlapping dates, shown here Data Input from Filter Formula (the dates and data ...
1
vote
0
answers
13
views
Excel line plotted graph changes when horizontal axis is changed to Date
I have always experienced a frustrating problem when plotting line graphs. The plotted lines are smooth as they should be, but I would still need to tweak the horizontal axis to Date in order to show ...
- 11
0
votes
1
answer
14
views
Scatter graph with desired series name
I have the above table, but however I try I cannot get the evidence gap to appear as individual series.
In this example there should be a total of 4 points with the legend of the points being Test, ...
- 1
0
votes
1
answer
12
views
Percentage complete cell based upon highlighted cells in a range
I have a table that I want to get a percentage from but I want it to be a rolling percentage so that when I highlight a cell the percentage changes. There's no values in the cells that I want to ...
1
vote
2
answers
20
views
Mass edit of Excel conditional format structure with formulas. Used the year 2022 in the formulas and need to change for 2023
I created a 17 tab worksheet for company locations to track monthly performance metrics against a budget. I used a reference cell as a condition to control conditional formatting of cells as well as ...
0
votes
1
answer
57
views
XLOOKUP with formula filled cells doesn't work
I have a table of which every value is a formula.
I have the following XLOOKUP: =XLOOKUP(G3,C3:C28,E3:E28,"").
The C column are percentages calculated by formulas. This column is designated ...
