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,928
questions
1
vote
1
answer
513
views
VBA to rename sheet based on filename
I got a bunch of recorded files in ".Zip" format. My VBA opens all of them and substract the details as text. As there are many the Workbook creates sheets and keeps the count as this:
ActiveSheet....
- 13
2
votes
1
answer
1k
views
Auto-populate a new row that includes a sequential number
I have a two part question.
1) I would like to create a table that auto-populates a new row that includes a sequential ID number (which includes text), whenever a previous row is filled out. For ...
- 25
0
votes
1
answer
346
views
Value retrieval from the multiple selection dropdown in Excel
I am trying to make calculation from my dropdown menu. I have following dropdown in sheet 1 in my Excel.
## Category ##
### AAA ###
### BBB ###
### CCC ###
### DDD ###
In sheet 2, I have ...
0
votes
0
answers
172
views
Excel: Return Information (Line items) based on Categories
I am looking for some help to find out if below is possible or not. If possible how should I tackle this..
I would like to made an order form which searches a customers sales history from a pivot ...
0
votes
0
answers
68
views
Move the row if the next cell of the row is empty
How can I possibly move one (1) row if the next row has an empty value?
For example like this.
- 101
1
vote
1
answer
251
views
How to sort number of numeric columns in one go
I have 350 columns. Below is a subset of all the data.
I used "Sort Smallest to Largest" tool. It gives results only for one column. If not, it can sort the first column and other columns will ...
- 11
1
vote
0
answers
991
views
Why does a .csv file lose kilobytes when it is saved as the same file, just under a different name?
I had a .csv file called "removed.csv" whose size was 3,959kb, but when I saved it under a different name, "uncleaned_df.csv", its size suddenly became 3,784kb. I've verified that I haven't changed ...
- 11
0
votes
1
answer
121
views
Excel - Populate several cells from date span
Background: I am working on a form for work where project leaders enter the start and end date of planned maintenance work. If it is affecting the customer, we will need to post information to the ...
- 25
4
votes
4
answers
2k
views
How do I extract a value from a time formatted value in excel?
How do I extract a value from a time formatted value in excel?
On the values below
column1 - is in this format => hh:mm:ss.000
column2 - is the decimal value of the
column3 - is = to column2 but ...
- 179
0
votes
1
answer
2k
views
VBA Excel code to automatically format text (Credit Card Numbers) based on length of string?
Background:
Number formatted cells in Excel have digits beyond the 15th changed
to zeros (reason). This behavior affects the recording
of credit card numbers which can be longer than 15 digits. A ...
- 2,883
0
votes
4
answers
96
views
Displaying only categories where values are above certain level
I have an output where the amount is filtered descending from highest.
I just want to know if there's way to display rows with values of 100K and above, excluding the data that is lower than 100K.
...
- 45
-1
votes
1
answer
192
views
Export Outlook 2010 professional calendar to excel
My version of Outlook (Professional 2010) does not have an import/export choice. I can save it as an .ics . Can I turn that file into an excel list ?
2
votes
2
answers
3k
views
Excel - Nested waterfall plot
Is it possible to create a nested waterfall chart in Excel, having an aggregator below Jan-Feb-March that says winter, one for spring below April, May, June and so on so forth.
The image is just an ...
- 21
0
votes
1
answer
275
views
Formula to Count Groups in a Column
I am working in Excel, and, unfortunately, a strict requirement of this question is that the solution be done in Excel, preferably with formulas, but I'll try to convince others that VBA solutions are ...
- 23
1
vote
1
answer
23k
views
Unable to add comments in Microsoft Excel
I was able to add comments in one of my excel files, by right clicking the target cell and then selecting add comment. However suddenly add comment option disappeared from the right click menu and ...
- 251
1
vote
1
answer
181
views
Name the first item from a list that is not in another list
I am working with a list of articles in one spreadsheet files as the data source. The data is split into a source reference code in columns A to E, the title of the article in column F and the author ...
- 117
1
vote
1
answer
585
views
Excel 2010: How do I count each instance of every duplicate in a column?
I have a column filled with urls in the chronological order in which they were visited. I want to count instances of each duplicate and list the count alongside the url in a new column to the right, ...
- 31
2
votes
2
answers
2k
views
Linked pictures are duplicating themselves almost like a mouse pointer trail when I move the group they're in
My linked pictures are duplicating themselves almost like a mouse pointer trail when I move the group they're in. I have them grouped with some charts (15 on the sheet) I have a few pivot tables on ...
- 98
1
vote
0
answers
464
views
Sunburst chart in excel 365 [closed]
I'm missing the Series Options under "Data series" to resize the doughnut size hole. I tried add-ins and save as and hasn't changed.
- 11
1
vote
0
answers
380
views
How do I stop the following error from occuring? "A connection attempt failed" (see full error in body)
I am getting the following error on an excel workbook which has a data connection to a sql database.
A connection attempt failed because the connected party did not properly respond after a period ...
0
votes
0
answers
366
views
Scatter chart with corresponding XY pairs
I need to represent data of XY-positions of a series of objects and a test subject's estimations of the positions of those objects. I'd like to pair them in the chart so that you can easily see which ...
- 1
1
vote
1
answer
70
views
Values in a column to rows based on multiple criteria
I am trying to turn a the table on the left side to the one on the right.
How can it be achieved? I have tried This, but it did not work.
- 113
2
votes
0
answers
2k
views
Office 365 Excel crashes when copying worksheet to new workbook
I need to make reports by copying multiple worksheets to different files using Office 365 Business Excel. I can't use Office 2010 or 2013 because of organisation's policies.
Configuration:
Windows ...
- 21
1
vote
2
answers
139
views
How do I count and display how many kinds of values I have in a column?
I don’t know how to phrase this in spreadsheet-speak, so here's my example: I have a list of bands (A) with columns for their genre (B), # band members (C), and # instruments (D). I want to know how ...
- 13
-1
votes
1
answer
531
views
Finding Partial Match with a List in Excel
I got two columns of data now.
//A list of name of authors in Column A:
A1: Harry LT, Vera CC, John MF
A2: Ken UY
A3: Anna J, Ben KF
// I got another HUGE list of names like this in Column B:
B1:...
- 1
3
votes
1
answer
578
views
How do i paste data (which has no hidden cells) into a worksheet which has hidden columns
Is it possible to prevent data being pasted into the hidden columns in one copy/paste movement rather than having to copy/paste multiple times?
- 31
-1
votes
1
answer
157
views
Getting list of all values that match criteria from a timeseries
I have a timeseries that I would like to analyse. I would like to extract some data that matches certain criteria. However, I also need to ensure that formula looks at the latest data (i.e. the latest ...
- 101
0
votes
1
answer
36
views
Looking a value with rounding up step in Excel
given the below table :
In the fourth column look up value the value 880.
Now, I want Excel to return the value 1000 because it’s the lowest value in column 4, which happens to be higher than 880.
...
0
votes
1
answer
113
views
Row data distributed into multiple rows on a separate sheet
I have a fuel consumption log, with vehicle id's in column 'C' and fuel consumed and distance traveled for different dates as the headers.
example
I have made multiple sheets based on the vehicle id'...
- 11
9
votes
3
answers
5k
views
Calculating the number of days between two dates in Excel
I consider 7 days to be a week,
so if I look at 1 January 2019 - 7 January 2019,
I would say that is 7 days.
But often I seen when people are calculating the number of days between two dates or age ...
- 405
0
votes
2
answers
718
views
How do I compare two columns for matching and non matching values and get it in the format that I want?
How do I find matching and non matching values between two columns and format it in the below manner?
Input:
| expected | match | forward | backward | actual |
|----------|-------|---------|---------...
0
votes
1
answer
519
views
Find sum of totals made on a given day
I have this simple table of data in Excel and I need a formula to find the total amount for today. So it would be a sum of all amounts that fall between 00:00 and 23:59 for the date on TODAY(). How ...
- 715
0
votes
1
answer
24
views
How to modify the contents of a range in MATCH()?
I am performing a trim + clean + uppercase in order to match IDs more easily:
=IFNA(MATCH(UPPER(TRIM(CLEAN(A1))), datos!B2:datos!B999, 0), -1)+1
This returns the row number, or a 0 and it works just ...
- 113
0
votes
1
answer
1k
views
Running multiple macros on different sheets with one button
I have one with 2 macros:
The first one deletes any data on sheet2 column1
The second sorts two columns on sheet1 and puts data on sheet2 column1.
I would like to run them both with one click. How ...
- 3
1
vote
1
answer
800
views
Determine number of non-Sundays in a part of a month
Calculate number of Sundays in the month asks
how many Sundays there are to the end of the month.
Two users proposed very similar answers that I liked and worked very well to determine how many ...
- 13
0
votes
1
answer
441
views
Excel Vlookup return value from reference_row-1 instead of reference row
As subject,
I'm doing vlookup between 2 excel file, The formula return value from reference_row-1 ("Room") instead of reference_row (DPS DPC)
Why this happened and how could I fix it?
- 1,410
3
votes
4
answers
12k
views
apply a formula to a very long column excel WITHOUT dragging
I would like to apply the formula n/log(n) to a very large number of inputs in excel. specifically across a column with 10,000 numbers in it. What is the most efficient way to do this without dragging?...
- 39
1
vote
0
answers
66
views
Define Name with Global Scope but Local Reference
I have an Excel project with multiple datasheets. I want to define a name for a cell that can be used in all sheets within the project, but keep the referenced cell local.
For example, say in ...
- 111
0
votes
1
answer
299
views
How to apply alternating-color table styles over cell styles in Excel
I don't like the fact that Excel cells have no-fill by default. So I have edited the "Normal" cell styles to have white background.
White background color cell styles
However, after doing this ...
- 267
0
votes
1
answer
616
views
I need to pull non matching numbers from 2 columns excel into new sheet
I have 2 large columns (139456 records) per column. I need to extract the non matching records to a new sheet "call sheet".
I will be adding to bottom of both list as time goes on. I would like to ...
- 3
0
votes
1
answer
88
views
Excel VBA for pulling apart text from a few cells and creating multiple cells of data
I would like someone to suggest the coding in VBA required to convert this data. Essentially B2 and B3 there will be multiple cells like there, even with more ";" and numbers after. The T and I won't ...
- 7
0
votes
2
answers
12k
views
Filter a table using information in cells outside of the table
I have a table that pulls in information from multiple different sources throughout my workbook. There are 4 categories that the table will be filtered on regularly and used by users that are not ...
- 13
1
vote
2
answers
19k
views
Condition formatting based on TRUE/FALSE output of another cell
I have 2 tables:
containing values
containing TRUE/FALSE outputs based on a formula that depends on the values of Table 1
I would like to apply condition formatting so that Table 1 cells are color ...
- 11
1
vote
2
answers
53
views
Instantiating an Excel Table
I have an Excel Table which I want to use as a source for other tasks. It contains a list of names and ids in my case, and some other data. This table is slowly growing and everything is there to stay....
- 135
2
votes
3
answers
108
views
What does TEXT formula, format as "?" do?
What does formatting text as "?" actually do? All I know is that it will give a blank cell appearance when the evaluated value is 0.
=TEXT(IF(condition,1,0),"?")
- 525
-1
votes
2
answers
3k
views
Excel over 100% then = 100%
I am calculating percentage and currently I have cells with the formula
=IF($H4="3PL",L4/Data!B$24,L4/Data!B$21)
I need to add a second piece: If the answer of above is greater than 100% make it ...
0
votes
1
answer
1k
views
Use Drop Down Menu to change cells next to the drop down list
I am very new at vba and am interested in creating a program that does the following.
When a item is selected from a drop down menu it will paste certain text into the cells that are next to the drop ...
0
votes
3
answers
104
views
How to variably choose a cell reference?
I want to reference a cell in an Excel formula. However, I want the row number to be based on a cell value in another cell. For example, I don't just want to reference cell R2, I want to say, ...
1
vote
2
answers
6k
views
Create new table without any formatting
I have an issue with creating table from file that I download from server/database. It's an older .xls file. In that file there is a list with 67 columns and arbitrary number of rows (it depends on ...
- 489
0
votes
1
answer
25
views
How to reference a named call from the looking up other cells
I have a named cell in my "Menu" tab called "OrangeJuice.Cost" which references "Menu!$D$2". On a new tab I have the String "OrangeJuice" in cell A1. I want to use this cell to construct the named ...
- 103