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,927
questions
1
vote
1
answer
50
views
Find Largest Values in Column and Count in Excel
I'm trying to find the largest values of the column Values which are marked by the number 2 in the column Conditions, and then, count the number of 2's in the column Conditions. The result should look ...
- 113
1
vote
1
answer
50
views
Unsure of a way to filter the data I am trying to analyze, don't know if it is possible in excel
I am trying to set some data up for further analysis. I have 3 columns in my data set. The first column indicates a project, the second column indicates a task within that project, and the third ...
- 17
1
vote
1
answer
32
views
A Macro for outputting HTML into cell in Excel
I have a rather extensive spreadsheet that includes specific fields that will need to be input to an HTML code for a website. I'm wondering if there is a Macro and/or formula that would generate this ...
- 25
1
vote
2
answers
568
views
Working with Excel 2016 and the length of an audio file eg, 0.004293981 = 0:06:11 (h:mm:ss)
I am working on a spreadsheet that is being used to catalogue a very large number (10,000+) of audio files which can be Wav, Flac, mp3, Ape and ._Pro Tools formats.
The data is supplied to me as a ...
- 77
1
vote
1
answer
91
views
How: Convert separate Date & Time text strings together into a single unified Date Time field?
How: Convert separate Date & Time text strings together into a single unified Date Time field?
Taking the following text pasted into an Excel file from a web page:
Column A
What formula would be ...
- 981
1
vote
3
answers
386
views
IFS function in excel not able to work
I am not sure what is the problem, either is syntax error, or data error.
The data in 1st column F is all text. Some are in millions (with the word 'M' at the end) and some in thousands ((with the ...
- 21
1
vote
1
answer
804
views
Reference result from Excel Filter function to be used in drop-down
I use the Filter function in Excel and successfully get a resulting list of values:
Now I want to reference that list to be used as the content of a drop down list - something like this:
As the ...
- 153
1
vote
1
answer
639
views
Asking Excel To pick the value from the first row
Lets say you have two rows in excel
S1 S2 S3 S4 S5 S6
ROW1 1 2 3 4 5 6
ROW2 7 8 9 10 11 12
I wish to have excel go through the numbers in the second row and give the name from the ...
- 125
1
vote
1
answer
3k
views
How to get Excel to use digit grouping separator (thousand separator) universally?
Digit grouping separator is already set in my regional settings
But excel doesn't show numbers like that. How can I get excel to do that everywhere including the average number in status bar.
I don'...
- 423
1
vote
1
answer
855
views
Is there a way to filter-out the last item in an Excel PivotTable field using the context menu filter when grand totals are not visible in the table?
In a pivot table, you can right-click an item in a field, choose "Filter">"Hide Selected items" and that specific item, or selected items, will be filtered out of the view.
I find this very useful, ...
- 6,840
1
vote
1
answer
720
views
Understanding the logic of an Excel INDEX formula which incorporates the MATCH function
I'm doing an online Excel course and I'm trying to wrap my head around the internal logic of the formulas I'm being taught. So far, so good, for the most part. However, I'm trying to figure out the ...
- 115
1
vote
1
answer
309
views
Date format to string in ddmmyyyy
So I am working on an existing workbook so I cannot change the layout unfortunately. In the first sheet, a function automatically returns today's date in the "yyyymmdd" format. On another sheet, that ...
- 174
1
vote
2
answers
539
views
Excel Pivot Table Formula
I have an excel sheet that contains data like that shown below. I have the pivot table that shows departmental time and percent and an external value of total cost.
How do I create a formula that ...
- 113
1
vote
1
answer
32
views
Refer to Table Column by Name rather than Table Column Address
I have 2 sheets.
Sheet 1 is a formatted as a table and is where the user pastes data in.
Sheet 2 takes part of that table data and prepares it for pivot table analysis.
The problem is that the input ...
- 11
1
vote
2
answers
77
views
Fetching a value displayed in a table based on 3 criteria
I'm struggling to figure out a formula to fetch a value in one sheet based on 3 criteria in another. This is my issue:
Basically, I need a value in the cell "action extrapolation" based on a the ...
- 133
1
vote
3
answers
871
views
Counting unique values against a subset of a list
I'm trying to count how many unique supervisors a particulate employee has. (See an example data set below.)
I'm able to do this in a round about way, but I'm wondering if any of you experts out ...
- 113
1
vote
1
answer
61
views
Excel - lookup by multiple criteria
Based on the attached screenshot, I have a sample database on the right side.
I would like to input random models, capacity, and grade on the right side, and receive an output of pricing.
How would I ...
1
vote
1
answer
2k
views
Excel 365 : How to prevent excel from locking file while editing
I just recently changed to using office 365 and am having issue with excel in particular.
I often have to use excel to modify certain xlsx files and upload it to our company website which then gives ...
- 113
1
vote
1
answer
176
views
Is there a good way to use Excel CELL() formula without prior knowledge of the locale the file will be used in?
The Excel CELL() function takes a required parameter info_type and an optional parameter reference.
The parameter info_type takes a localized string to determine the type of information to return.
...
- 160
1
vote
1
answer
892
views
Non linear chart Y-axis EXCEL
Is it possible to create a chart in Excel in which the Y-axis goes up by order of magnitude rather than linear.
so rising like
1,10,100,1000....
Or even doubling
1,2,4,8,16,32.....
Rather than
...
- 7,435
1
vote
1
answer
157
views
Why is my Excel macro running so slowly?
My VBA code in excel is running very slowly. How could I make this macro code to run faster?
Sub Test()
Dim i As Long
For i = 1 To Rows.Count
Statusupdate = Cells(i, 1).Value
If Status = "Started" ...
- 13
1
vote
2
answers
50
views
Excel formula issue for entering "0" with empty cells
I am trying to create a production report for my work and all of the tasks that are done are counted for each employee. I have my data, my pivot, and my main dash that points all my data to go where I ...
- 11
1
vote
1
answer
38
views
Problems with the order of position of values from largest to smallest, any solution?
Would you have any chance of getting the rank position like this as shown in the image? obeying the order from largest to smallest and repeating positions if there are repeated values?
- 287
1
vote
1
answer
94
views
Sum the time based on criteria multiple in and out
enter image description herethis is my first post requesting how to find net working hours for an employee based on their in and out on a multiple times per day. Employees start their shifts in ...
- 13
1
vote
1
answer
47
views
Use the handle to propagate two-by-two cell references in Excel
In Excel for macOS, I'm trying to use the cell bottom-right handle to apply a formula with cell reference. This usually works fine, but in this case I want to move the cell references on a two by two ...
- 111
1
vote
1
answer
512
views
Geography, Stocks - Excel's rich data types / linked data types
In Excel 365 (the full online version), I see there is such functionality:
Now, I find articles implying that such feature exists in Excel's Desktop version (at least Excel 2019) too:
https://...
- 2,497
1
vote
1
answer
409
views
excel spreadsheet sum by group
I'm trying to determine winning percentage.
problem is, 1 transaction may have any number of buys or sells til next transaction(sell).
example.
not sure to use sumifs, or group, i'm lost,
thanks ...
- 11
1
vote
1
answer
816
views
Excel: Hide data until date is entered
I want to prevent a user from seeing the data in cell A1 until they enter the date in cell D1. Once they enter the date in cell D1, the data in A1 will appear for them.
- 11
1
vote
4
answers
9k
views
Excel: Automatically extending conditional formatting to a new row added above data
I have a bunch of values across multiple rows and columns. They are structured so that each row shows the data for one particular month and the most recent values are up top. Consequently, when I ...
- 189
1
vote
1
answer
256
views
Excel: 2-way linkage for cells in separate workbooks
I am brand new, and not sure if I'm asking this correctly. I have an excel spreadsheet that I want to share with a client so we can both edit and update its information. However, I only want to share ...
- 11
1
vote
1
answer
127
views
Microsoft excel open high low close chart creation?
I have a bunch of raw data that is ordered in a column with the first cell being the open, and the last cell being the close. is there a set of formulas I can use where it can sort the open high low ...
1
vote
1
answer
55
views
Is there an easy way of reformatting this in excel?
I'm trying to copy a table from a pdf into excel but the formatting is lost during the copy and paste.
The original table looks like:
a b c
d e f
And it pastes to:
a
b
c
d
e
f
Is there a ...
- 323
1
vote
1
answer
100
views
Calculate a number of rows as a single event Excel
I am using Microsoft Excel 2016 and I have a spreadsheet with multiple datasets that calculate a number of different things. For each site, we receive data for incremental reading throughout the whole ...
- 113
1
vote
1
answer
70
views
Excel xlDown selects two blocks of data
I have the following dataset
100
100
200
200
300
400
I am using this code
Dim rngToSum As Range
Dim TopCell As Range
Dim BottomCell As Range
Set rngToSum = Intersect(...
- 405
1
vote
1
answer
51
views
How to turn a recorded Macro to an automated Macro in VBA?
I have recorded the following macro to sort data in the range "B3:U47" based on the values of the column "C3:C47".
Sub Sorting_Finance()
Range("B3:U47").Select
ActiveWorkbook.Worksheets("...
- 11
1
vote
1
answer
353
views
How do I remove "Book1.xlsx is protected" when opening a file
I have a file for which I get asked for a password when I try to open it. I know this password:
I want to remove the protection from the workbook, and assumed I could do this by going to File > Info >...
- 958
1
vote
1
answer
64
views
Custom data validation
Using the custom data validation rule, is it possible to only allow values that follow the below criteria:
Length = 7
First 3 digits = "ISO"
Digits 4,5 & 6 must be numbers
I currently allow any ...
- 7,435
1
vote
1
answer
334
views
Grouping raw data into columns of data Excel
So I have a bunch of raw data that is basically categorized into three categories (ignore three constraint, let's just say n categories). I want to split this into multiple columns where each column ...
- 113
1
vote
1
answer
60
views
How do I structure the following formula correctly?
In worksheet 2, I want to use a value from a certain cell in worksheet 1, if
the text in a cell in worksheet 2 contains a specific word. How do I do
that?
IF (text in Worksheet 2, Cell C5 contains “...
- 11
1
vote
1
answer
147
views
Chrw() Anomally: Invisible Values
I found an anomaly that I don't understand. I generated a list of special characters (into a column of cells) using the Chrw() function and the list was created just fine. However some of the cells ...
- 113
1
vote
1
answer
652
views
Formula to use as alternative date if cell is blank
I am trying to calculate employee service awards using a Date Joined column, and some staff have an alternative Date Joined due to a break in service.
Example
Date Joined=C17
Alternative Date ...
- 11
1
vote
1
answer
64
views
How to merge consecutive lines to reflect data from the same record?
I was hoping someone can help on Notepad++ or Excel/VBA.
I have the data below (sorry I could not attach the file) but if the example data is copied in Notepad++ it should have 12 lines of data.
...
- 11
1
vote
2
answers
123
views
Conditional format cells of the form x ± y
I have a 1 million numbers as cells that are in the form "3.1415926 ± 2.731828." and 314±27 and 314 ± 27.32
I want to conditional format or in general format those cells so that they only have 4 ...
user1110333
1
vote
2
answers
2k
views
MS Excel - Separate text based on keyword
I need to separate text based on a keyword to different columns. the original text is in one column but I want to split the intended subtext into different columns. The problem in this case is I get ...
1
vote
1
answer
104
views
How can i modify Unique ID in Excel?
I have one unique ID column which I want to update to my New ID column which is just E inplace of 10.
| ID | New ID |
|:-----:|:------:|
| 10022 | EO22 |
| 10157 | E157 |
| 10015 | E015 |
| ...
- 171
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
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
1
vote
2
answers
792
views
Countif Date Array Formula
I'm trying to build a sheet for tracking compliance with training. I'm needing to build some summary fields, which show how many procedures a person is current on, how many they are out of date with, ...
- 113
1
vote
2
answers
231
views
Sum multiple rows on single condition
Suppose I have the following table in sheet month:
And the following table in sheet __data:
In sheet __data I'd like to sum all the values from column P,R,T,V and X, for the given category name.
...
- 387
1
vote
2
answers
171
views
Excel: Getting sum total based on date input
Thanks for the previous help, now I have another issue that I cant figure out.
If you look at the image, I have the data spread out in groups.
I tried "+" and "&" to add additional line to ...
- 31