All Questions
Tagged with microsoft-excel-2016 microsoft-excel-2013
72
questions
0
votes
0
answers
96
views
Microsoft Excel not recognizing cell data as date
I'm working with a set of dates that need to be utilized in an if function. In the event the date is more than 365 days from today, it is false and vice versa for under 365 days. My problem stems from ...
- 1
2
votes
1
answer
239
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,342
0
votes
1
answer
56
views
Format of Excel drop down list
When choosing from a drop down list in Excel, is it possible for the cells to change the format when you choose a specific subject?
Each subject has a different format for the cells.
- 1
0
votes
2
answers
221
views
Index Function with error "You've entered too many arguments for this function"
=index(_70Rain,1+int((row(b4)-1/columns(_70Rain)),mod(row(b4)-1+columns(_70Rain),columns(_70Rain))+1)
I just followed the code given in https://www.extendoffice.com/documents/excel/1172-excel-...
0
votes
1
answer
483
views
In Excel my formula is skipping the first two cells of a table
Hi I have an Excel Spreadsheet and I'm trying to convert a table with multiple rows and columns into just one column for sorting and filtering. But the issue is that it doesn't start at cell A2 where ...
0
votes
2
answers
847
views
Excel Custom Ribbon Separators
I was looking for a way to insert vertical separators into a custom ribbon in Excel 2010, which seems to be problematic for everyone. I've searched the planet and been told it can't be done!
- 1
1
vote
2
answers
702
views
getting Max/Min value in range based on a cell's condition
Greetings to all Excel gurus..
I've got the dataset shown in the image attachedenter image description here..
I'd like to show the MIN and MAX value of "Price/Oz" based on the condition if ...
- 13
0
votes
1
answer
364
views
Excel: Take on the value of a formula cell when other cell is equal to x?
I know that title isn’t great but essentially:
Let’s say
A1 = some number
And
A2 = some formula involving A1
I want to store values of A2 in a column for different values of A1 so like a function ...
- 1
0
votes
1
answer
80
views
Excel Hyperlink within the same sheet
I have an excel workbook with 2 sheets. Sheet1 and Sheet2. I want to stay on sheet1 and create a hyperlink to sheet2 but I want the information that is available in sheet2 to be displayed in sheet1 ...
0
votes
1
answer
615
views
Highlight all the cells if the keyword in the cell matches to any value of a list
For one of my excel sheet, it is required to highlight the cells that has specific keyword in it.
To solve this, i have created a list called my_list which contains value1, value2 & value3.
I have ...
0
votes
1
answer
6k
views
Excel: Drop down with month instances
Is there a possibility to have a drop down with months inside, when choosing one month to show me all instances that are within that month?
Something similar has Excel format Tables, when you choose ...
- 121
0
votes
2
answers
258
views
Excel: Return all matches in columns using one criteria (from drop down)
I am having two tables, one table is main data, separated into many columns and blocks per 10 rows in one, this is fixed. In Column A are unique numbers that will be used in drop down.
Table 1
On ...
- 121
0
votes
1
answer
818
views
Conditional formatting for specific group of values/rows based on cell value(s)
I am having some data table that is separated in lets say "blocks" of rows. As you can see on the image, starting from Column D (A1) there are some values in first row, and then 5 rows empty ...
- 121
0
votes
0
answers
84
views
Checking Whether Two Columns Match any Numbers from a Different Column
I was wondering if anyone knew the best way to use one column as a sort of "criteria" column by which two other columns are searched for any numbers that match that criteria column. However, ...
- 1
0
votes
1
answer
444
views
Excel - count unique days in a start-end date range
Happy new year!
I have an Excel filled with start-end dates (A2:B7).
My interest is to try to get unique days in an interval.
I've made a research but I can't seem to find any solution to this, most ...
- 3
0
votes
1
answer
223
views
Excel Pivot Slicer & Unique List in one Column
What I wanted to achieve is to get Unique values list (Y1,Y2,Y3,Y4) in one column (same as on picture 2) so that I can have it for Slice and Dice when using Pivot Tables/Charts. I wanna have "Y's&...
- 121
1
vote
1
answer
280
views
Indirect Function in Excel
How to use Indirect Function to get data from another sheet from a single cell?
It's very confusing whether to use apostrophe or not. If someone could explain it with the Concept it would be of great ...
- 11
0
votes
1
answer
38
views
Transpose some columns of an Excel table
I am not able to achieve going from this format:
To this format:
I could transpose manually, but there are hundreds of items.
- 103
2
votes
1
answer
488
views
Update an excel list dynamically without using scripts
I'm a bit stuck and could use some help.
I am trying to keep a list in excel updated dynamically, based on conditions in other cells. Something along the lines of (IF both cell values in A1 and A2 ...
- 25
-1
votes
2
answers
172
views
Excel: How to Transpose a column of data into a row using a formula and not using copy and paste transpose
Within the screenshot, I would like to Transpose a column of data (Column A) into a row (Row 7) using a formula, cell referencing and not using copy and paste transpose. Would someone be able to help?
0
votes
0
answers
111
views
Excel: Dynamic change cell with Date/Time when any other cell is changed in the worksheet
I am trying to decypher a formula which will stamp auto Date/Time or some text when I change any random cell (more instances) inside some certain range (for instance A1:K45).
What I did is for one ...
- 121
0
votes
0
answers
39
views
How to replace formulas into values but with possibilty to remain referenced to its original source (dynamically)
I would like to have the possibility to convert some formula (in my case CONCAT) into values, but with the possibility to select those values and not to have formula shown, and also to be able to ...
- 121
1
vote
2
answers
438
views
Q: Usage of Excel function called as EFFECT()
I am having a question regarding the usage of the EFFECT() function in Microsoft Excel.
I know the basic concept of the function, and I have no issue with it when there are pre-defined compounding ...
- 13
1
vote
1
answer
137
views
Reduce the list of file types in Excel
In MS Excel 2013 or 2016, is there a way to reduce the choices in the file type selection list?
The amount of choices when choosing "Save as..." is overwhelming for some users.
- 6,064
1
vote
2
answers
10k
views
Automatically move to the next cell in a column only if criteria is met if not check the criteria in the next row and keep doing the same
Sheet 1 - I have 3 columns (First Name, Last Name & Status)
Sheet 2 - I want the first cell in A column to only populate the First Name, if the status says "RESPONDED" in the status column and ...
- 15
0
votes
1
answer
306
views
Excel Formulas for Assessing Comissions (Additions) and Omissions (Deletions) in Qualitative Data
I was hoping someone might be able to figure out some formulas (or VBA scripts) for me to help me calculate qualitative analyses. Specifically, I have two columns that look like this:
Participant IDs ...
0
votes
1
answer
824
views
How to generate dense rank in excel
I want to generate Rank similar function works like DENSE_RANK() in database product (such as SQL Server).
Here is sample data :
Col1
1
2
3
4
4
So, i want to be generate following sequence.
...
- 101
-1
votes
1
answer
6k
views
How to change text color in excel using custom formatting in formula
I am combining few cells to make a report which needs to be sent my team daily. The cells has both positive and negative values and I need to show negative in RED color.
I tried below formula but it ...
2
votes
1
answer
127
views
Shortening long sentence based on lookup-value
Need help: As stated in image below that I need the result in E3 to be "Washington" because based on lookup value in B6:B9 there is a matched-word partially (which is 'Washington') from D3's long ...
- 85
1
vote
2
answers
1k
views
Bypassing one of the SUMIFS criteria+criterion if the particular cell is blank
Need help:
From the table image below, the result I want in cell M5 using SUMIFS formula is that if cell K5 (validation filter) is blank or "" then the formula of D5:D6,K5 should just bypass.
So the ...
- 85
0
votes
1
answer
42
views
Return formula if the range is not ""
I wanted to have formula in G3 =
From range J3:L3 find the first value which value is NOT ""
so since from range J3:L3 the first non "" value is the one in K3 (the value is "GDNdynamic") so then the ...
- 85
0
votes
2
answers
784
views
How to copy formulas when there are empty rows in between without losing the sequence of linked sheet?
I have an excel file which has two sheets.
Summary
RawData
My Summary sheet has data like this where is there is a gap of few rows for every next data row
Now the problem is if I have a sheet with ...
- 11
0
votes
2
answers
524
views
How to extract address into cells from excel?
I have addresses in one cell and I want to extract them in different cells on the same row. Some cells have four lines of address and some have three. I am able to easily split using text to column ...
- 101
2
votes
1
answer
374
views
Excel: How to use =SUM() with two strings for range?
I need to pass in two strings indicating the two locations of the range. However, SUM wouldn't read the string, but it reads the value of what's supposed to be the indicator of a location.
What I ...
- 169
1
vote
1
answer
24k
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
0
votes
3
answers
9k
views
Extract certain words from strings of text in Excel cells
I have a column of cells filled with a lot of text. I only need one word from each cell. If you look closely you will see that the latter part of each cell says either High, Medium, or Low.
I want to ...
- 15
3
votes
1
answer
2k
views
Why does Windows Defender slow my Excel file saving, even if the xlsm file type is excluded?
I'm experiencing a strange issue with Microsoft Excel (Office 365 MSO 16.0.11126.20234 32B) and Windows Defender running on Window 10 Pro (1809 Build 17763.253 64B) . When I save a macro-enabled Excel ...
- 131
0
votes
1
answer
111
views
Complicated 'IF' function in Excel
In Sheet 1, I have the following:
If the Due Date is after the Date Submitted, then I want the value in column D of this sheet to be the Detail Status, which is on Sheet 2,
UNLESS the Detail Status ...
- 15
0
votes
1
answer
52
views
Select user id for every date in excel
I want to select user id for every date in excel. Tried doing this =IF(A2=A1,(IF(B2>=B1,E1+1,0))) but not getting the exact expected result
Input
user_id date
10003 12/19/18
10003 12/19/18
...
- 5
0
votes
1
answer
39
views
select previous row of particular event in excel
I am current working on a problem.. where i need previous event of applaunch in excel
Input
notification_dismiss
notification_dismiss
notification_dismiss
AppLaunch
...
- 5
0
votes
3
answers
72
views
Select 1st day of each user id in excel
Trying to figure out away to get the expected output from the id and date columns. How do i select 1st days of each user id .. the formula i tried is this IF(A2=A1,(IF(B2>B1,FALSE))) but not getting ...
- 5
1
vote
1
answer
2k
views
Cell conditional formatting, based on the format of the same row's cells of another column
I've highlighted my duplicate text in column B, using Conditional Formatting. No I want to highlight the same field on column A (and afterwards delete all non highlighted texts), but I am not sure how ...
- 19
0
votes
1
answer
94
views
Excel: COUNTIFS or Nested IF counting sequential columns
this is my first time posting here so please be a little patient with me. I'm trying to write a formula that will tracked missed scan events (for a package tracking database).
I have several date/...
0
votes
1
answer
76
views
Is there a way to access the built-in excel form via a shortcut?
Is there a way to access the built-in excel form via a shortcut? Macros/VBA and addins are disabled. Users are running either Excel 2013 or Excel 2016.
Search has yielded only custom toolbar ...
- 1
4
votes
1
answer
243
views
Excel 2013/2016 not updating values while workbook is minimized
I have the following setup:
1 Workbook named Workbook1 with the value 123 in A1
1 Workbook named Workbook2 with value =[Workbook1.xlsx]Sheet1!$A$1 in A1
Now, when I go and change the value of ...
- 133
3
votes
4
answers
135
views
Add only working days in grid style using Microsoft Excel
I like to have a calendar date view of the five working days in Excel.
I manually built a first row with dates from 30th July 2018 to 3rd Aug 2018 (Mon to Fri, which are working days) and a second ...
- 51
1
vote
1
answer
426
views
Assign Nested Sequential unique numbers for items in Excel Table
I'm trying to generate a unique ID number for a items in a table in excel.
For example: 1.2.4
For each column the number is uniquely numbered based on the text.
For example in the table below would ...
1
vote
2
answers
241
views
Find and get specific word by punctuation, but then remove punctuation from result
I tried everything to come up with a solution on my own to this problem, but I’m stumped and I could use some help. Any feedback you guys could give me would be great.
I have a dataset that pretty ...
0
votes
1
answer
676
views
How to sort duplicate rows of values into unique rows and multiple columns
Say I have a sample table like this:
And I want to make it like this:
Basically, it is making values in column A unique by moving all duplicate values of each unique row into the next column over. ...
- 119
-1
votes
1
answer
158
views
how to rank values in column A in column B with id?
how to rank values in column A in column B with ID with formula as shown in the picture ?
- 71