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. Also add the the specific version tag (e.g. [microsoft-excel-2019] or [microsoft-excel-365]) as features and functions may vary between them.
25,934
questions
1
vote
0
answers
77
views
Excel Pivot Table - Calculate % of Row Total Without Showing All Columns
Is there any way to show % of a row, without displaying all the columns of a pivot table? I know how to show % of a row from count using Show Values As -> % of Parent Row Total, but I don't want to ...
- 41
1
vote
0
answers
197
views
Excel Pivot Table, calculate count of column fields as percentage of total
Excel Pivot Table, is there any way to create a column that calculates the number of times a value shows up in a specific column relative to the total number of fields in that column as a percentage ...
- 11
0
votes
0
answers
34
views
COUNTIFS value doesn't changing
I have two Columns both are categorical columns. Like Age_group and Engagement_category. And I want to get count no. of each engagement_category in each Age_group.
Engagement_category
Age_group
...
- 171
3
votes
2
answers
87
views
I want to count IF both condition are true Logically count by AND excel
I have two Columns both are categorical columns. Like Age_group and Engagement_category.
And I want to get count no. of each engagement_category in each Age_group.
| Engagement_category | Age_group |
...
- 171
2
votes
1
answer
450
views
Condition in excel that would allow me to subtract the last populated cell from the first
Lets say I have 10 cells A1-A10 some cells may be blank how do I construct a formula with the condition that always takes A1 - the last cell populated with a value. Ex cells A1 A3 and A7 have values ...
- 23
1
vote
1
answer
653
views
Return a Value from Column B if the search Value is found in any Column through Z-AG
Here is the scenario that I have been trying to puzzle out.
In Sheet1 Column B, I have a list of SKUs, in Sheet2 Columns Y-AG I have multiple lists of SKUs that could match what is in Sheet1 Column B....
- 19
0
votes
2
answers
45
views
Excel if functions or match
Best formula/function for excel matching property addresses.
I am comparing the property address with mailing address
In column A I simply want a true/false value.
https://1drv.ms/x/s!...
1
vote
0
answers
53
views
Conditional Formatting consistency in modified Tables
QUESTION:
how can I keep conditional formatting consistent and easy to manage in a table (ListObject) that is constantly sorted, filtered, and modified by adding new rows (either manually or via VBA)...
- 151
2
votes
0
answers
75
views
Matrix from 3 columns with values (not sum)
I'm want to show values in a matrix instead of columns, but can not figure out if this is possible in Excel...
Have 3 columns with values and want a matrix where there might several values in one ...
- 21
0
votes
1
answer
4k
views
How to convert Goal Seek to formula?
I have the following formula for working out the profit margin:
(Revenue - Cost) / Revenue
I am now required to work backwards starting from Profit Margin.
I have a column for Revenue change % where ...
- 51
0
votes
1
answer
273
views
Formula to get the maximum of an array of cells with a custom list on Excel
I have a worksheet like this, with some cells having a value and being merged with the next one.
| A | B | C | D | E | F |
---+-----+-----+-----+-----+-----+-----+
01 | ??? | | ...
- 13
3
votes
2
answers
306
views
Change multiple full-stop separated sentences in a sentence to Sentence case
I want to convert multiple texts in a sentence to sentence case that includes a full-stop. For example,
this is a Sentence case. this is also a sentence case.
should be
This is a sentence ...
- 53
0
votes
0
answers
717
views
Cells show number as text despite number format
I have a range of cells I apply a number format to using the EPPlus Excel library for .NET. I apply the format as follows:
ws.Cells[$"D{startAt + 3}:E{endAt - 1}"].Style.Numberformat.Format = "#,##0....
- 2,621
0
votes
1
answer
2k
views
Excel Conditional Formatting based on multiple columns
I have this data as example
Excel Example image
How to do conditional formatting so that if I put another data on the same date and another city (for example on 30/10/2019 to Paris) and then I ...
1
vote
0
answers
160
views
Secondary horizontal axis: Wavelength (nm) and Energy(eV) on the same plot in Excel?
Is it possible to create in Excel a dual-horizontal axis plot, showing on the top horizontal axis the Energy and on the bottom the Wavelength (both on x-axis)?
The energy is related to wavelength ...
- 11
1
vote
1
answer
95
views
SUMIF function using LARGE() as criteria
Could you help me creating a formula that makes it unnecessary to create the column D and gets the result using only one cell. I guess you would have to use an array formula but I can't figure it out.
...
- 11
1
vote
0
answers
33
views
Excel VBA - Count colors that are numbers
I did conditional formatting in cells that contain numbers. Those numbers are the ones that have different colors. I need to count the number of cells that have red numbers, green numbers and blue ...
0
votes
1
answer
58
views
Excel custom cell formatting: unexpected behavior?
I've just noticed that when trying to use a custom formatting for, say, 16-digit credit card number, something really strange occurs:
Screen Recording
Can someone explain what's happening and why it ...
- 1,081
0
votes
1
answer
57
views
Excel custom cell formatting: formatting code for alphanumerical entry with spaces?
I'd like to apply a custom cell formatting that takes an alphanumerical entry and outputs with predetermined visual spacing (no spaces in actual data). The purely numerical equivalent of what I'm ...
- 1,081
0
votes
1
answer
1k
views
How to add new CSV columns in Power Query?
When you edit your query of a loaded CSV file, it only shows the columns you chose when you first created the query.
What if new versions of this CSV file have new columns that didn't exist when you ...
- 946
0
votes
3
answers
2k
views
Extracting units of measurement from excel string
I have some values in an excel string which contain various combination of characters alongwith unit of measurement. I want to extract only the units of measurement from the string. And if there is ...
- 53
0
votes
1
answer
35
views
Dynamically select from multiple lists of valid data based on user input
I'm creating a mock up of a user input form and I need to create dynamic/dependent data validation. The logic is quite involved and I've exceeded the 255 character limit on the data validation source ...
1
vote
0
answers
173
views
Excel - Manual Column(s) Beside Imported Data
In excel, how do you properly mix 'manual' data and imported data in a sheet / data range?
Requirement
For context, my particular use case is a sort of bare-bones data "triage" tool in excel.
Ideal ...
- 11
1
vote
0
answers
40
views
Detect presence of coloured text in a cell in excel
A client provide me with a set of data updates to a system that I support. The way they done this is by providing an excel spreadsheet with ALL data where some updates have been made. They further ...
- 472
2
votes
2
answers
2k
views
Make Excel charts change x-value range
I am trying to create a chart the changes the x-value range according to values input into two independent cells, as in this screenshot:
I would like the graph to only show data for the values ...
- 23
0
votes
1
answer
33
views
Excel PowerQuery - Dimension to Column
I've got following 2 Tables:
Date Name Value
1.1.19 A 11
1.1.19 B 54
1.1.19 C 234
2.1.19 A 346
2.1.19 B 654
3.1.19 A 156
3.1.19 B 6432
3.1.19 C ...
- 161
0
votes
3
answers
3k
views
Query on excel for matching the names and numbers
I have Employee name In Column A and employee id in column B, In column C i have employees id's which are there in column A.
I need to run a formula to match the employee id from column C to column ...
1
vote
1
answer
494
views
Excel 2010 autofill option by entering last four digits of 10 digit number how to
I'm having list of 1000 numbers (10 digits) in each rows, need to get(retrieve) that particular number from that data by entering last four digits alone..
Just I have to retrieve the 10 digits by ...
- 11
1
vote
0
answers
23
views
Hide / Unhide VBA Macro [duplicate]
I have a macro to hide a few columns that shows additional information however i can't get it to unhide the same data. I know it's simple I am just really unfamiliar with Macros. Any help would be ...
- 87
0
votes
1
answer
883
views
VBA Hide Rows that contain N/A
I have an excel file that has dynamic data that changes daily. For example, a user will populate data on the data tab and the percentages are calculated using an "if Command" if there isnt any data N/...
- 87
1
vote
1
answer
1k
views
Excel - Using INDEX MATCH MATCH with a diagonal range/array
I am trying to use INDEX MATCH MATCH with a diagonal range of headers.
So I can return the value at B5 (when I realised that INDEX actually refers to the cell in the order ROW,COLUMN (5B) instead of ...
- 149
0
votes
0
answers
141
views
Creating an advanced dynamic formula calculator in excel
Is it possible to create a dynamic formula calculator in excel for a formula like this: https://stats.stackexchange.com/questions/43159/how-to-calculate-pooled-variance-of-two-or-more-groups-given-...
0
votes
0
answers
33
views
Filter and summarize data based on more than one critera
I want to filter and summarize a list based on more than one criteria.
This data
Name Sport Victories
Daniel Tennis 1
Esther Tennis 1
Daniel Archery 3
Mary Tennis 2
Mary Tennis 1
...
- 101
0
votes
2
answers
2k
views
How do I summarize a value according to multiple columns in Excel?
Summing a column according to one other column is quite easy with a Pivot table. However, it doesn't seem to be possible with multiple columns.
Similar to R's group by, I want to summarize one value ...
- 123
0
votes
0
answers
229
views
Converting from xlsx to csv in excel changes date data
I need to convert to csv so that I can plot with R Studio, but when I save as a .csv, the date data changes and I don't mean the format.
For instance, if the date is 2019-05-02 01:30PM in the ...
1
vote
1
answer
893
views
convert sql-excel date to recognisable format
I have a large csv file(1.1G) exported from SQL Server. I would like to do data wrangling in python, but I had a few problems doing this. The original csv file dates look like this 00:07.5, 00:08.3, ...
- 115
0
votes
2
answers
1k
views
How to add previously deleted CSV columns in Power Query?
After you use Power Query to load a CSV file, it only shows the columns you chose when you first created the query.
What if you suddenly want to start using a column you previously deleted in the ...
- 946
0
votes
1
answer
48
views
I want to pick second latest value with reference to other cell in MS Excel
I am currently using a formula which helps me in to pick the latest entry from column "C" by my given search word from (Column A) the bulk list of data with respect to time given in Column "D".
...
0
votes
1
answer
964
views
how can i separate the last 4 numbers in a cell or the last eight if it has dash? also extract the middle part of the sentence?
These are 3 different items in 3 separate rows. Would like the results to return in the column next to it. the goal is to extract the years, ex:
2012-2014
2012-2013
2019
Below is the data km ...
0
votes
1
answer
889
views
Excel Losing Text Formatting
I have a text box that is linked to a cell, however, when I change the link to another cell the formatting of the text box reverts to a basic font, there a way to prevent this from happening?
- 405
0
votes
1
answer
41
views
Excel Search Two Columns With Two Conditions
I need to search a date and a value (name) and the return the first value of the first entry and past it to another worksheet, and then a second value on the last entry for another cell in the same ...
- 3
0
votes
1
answer
1k
views
How to append tables in Power Query while merging similar columns which have different names, types and order?
I have 2 tables with similar values but different names, types and order of the columns.
What's the best way to merge them into a single table? I believe the 2 options are Append or Merge with Full ...
- 946
0
votes
0
answers
922
views
Can Power Query prompt for a file name when loading a text file?
Power Query can be used to pull text files into Excel.
But does it
have an equivalent to Excel's External Data's "Prompt for file name on refresh"?
That is, to assume the pulled text file has a ...
- 946
1
vote
1
answer
71
views
Append character to beginning of each new line in Apple Numbers
Ive got cells with the following data, each ingredient for a recipe is in a new line in the same cell. I need to add a pipe | at the beginning of each line but not the first. Is it possible to do this ...
- 21
1
vote
2
answers
239
views
Excel cell custom format
I cannot find the way, how to create custom cell format in Excel.
For example, I have value 3.5555, which is displayed as 3.6.
I would like to get displayed 35 555.
If I insert number 5 ... I would ...
- 19
1
vote
1
answer
2k
views
Using “ctrl” + “shift” + “arrow” with merged cells in Excel
I am trying to use the shortcut ctrl + shift + arrow, such as the down arrow, to select an entire column of data.
The issue is that some of the rows in that column have been merged or split. For ...
- 11
0
votes
0
answers
61
views
VBA: Copy first value of each month
I have a data set and from the daily data have managed to compute the average for each month.
My data looks like this: column B (and A) are dates, column C is the daily temperature and column E is ...
- 141
1
vote
2
answers
382
views
Comparing duplicates between two data sets and using a mathematical function in Excel
I am currently struggling with the following problem:
As you can see in the picture, I have two datasets.
I'd like to scan and compare column A and D, and look for duplicates. If there are ...
- 11
2
votes
2
answers
180
views
Excel - Make a rule to force a three numbers display, "null" replaced by 0
I want to make a rule into a numeric column in Excel.
I have like 800 cells in that column. Each are numeroted from 1 to 800.
My goal is to have a rule that make this change :
from --1 / --2 / -12 ...
- 137
1
vote
1
answer
349
views
Creating 0 entries for missing values in a date sequence for a unique set of columns in Excel
I have been struggling to fix a database that contains market size information. Because it is internal data I can't share the original file. The original file is just the example but much much bigger.
...