Questions tagged [microsoft-excel-2013]
The 2013 version of Microsoft's spreadsheet application.
1,614
questions
0
votes
0
answers
199
views
Last letter missing from Excel Menu texts
I have a bizarre bug in Excel 2013. Every time I open a menu of any kind, the last letter or character of the menu's text is missing.
While the bug is not a problem in any way, I'm insanely curious ...
- 149
0
votes
1
answer
50
views
Counting rows in a sheet which match conditions across columns
Let's say I've got an Excel sheet, the first few rows of which looks like this:
In another sheet in this notebook, I want to count the number of instances where pep is YES or NO. This is easily done ...
- 103
1
vote
2
answers
6k
views
Excel MEDIAN formula for values based on date
I got stuck with a formula again if you can help :)
I need to find the MEDIAN for a range of dates that have a criteria, for example:
code date time
5X 08 Feb 5115
5P 08 Feb 5225
...
- 29
0
votes
1
answer
43
views
Trying to find the largest value from the given data, per day and then try and map the largest data with date and time onto a graph
Scenario:
A machine is processing a number of items randomly throughout the day, every day i.e. 54x items are produced at 17:00 and then 207x items are processed at 18:00 etc (Screenshot provided)
...
1
vote
2
answers
412
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
107
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,014
1
vote
2
answers
9k
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
151
views
Excel date error: Excel will not recognize as a date; using Date= function adds 2 years but only in some cases
I've been unable to resolve this and about to pull out my remaining hair. There are other similar posts such as this one which I wanted this to be a response to but I am unable to post there due to ...
- 633
0
votes
1
answer
294
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
93
views
Excel 2013 - How to remove numbers only from different positions and preceded by different symbols, letters
Is there any formula on Excel 2013 that can be used to remove digits from rows?
I want to leave only letters and symbols.
The formula needs to work for numbers that can occur at any position, ...
0
votes
1
answer
781
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
0
votes
0
answers
174
views
Excel Pivot table only counting first column [duplicate]
The Issue
I'm trying to count instances of a number in each column. For a simgle column of data, this works fine, but when I add more columns, the count always reflects the first column.
Attached is ...
- 121
1
vote
1
answer
2k
views
How to have unselectable cells and selectable uneditable cells?
In Excel cells are usually selectable and editable.
I can format some cells to be unlocked and then protect the worksheet with only the option to select unprotected cells.
That way the locked cells ...
- 163
0
votes
1
answer
411
views
Excel: Highlight rows where duplicate values exist in multiple columns including blanks
I have the following formula:
=IF(COUNTIFS($B$2:$B$84164,$B21,$E$2:$E$84164,$E21)>1,"Yes","No")
It works fine except for when column E is blank. It doesn't show it as a duplicate i.e. thinks it ...
- 493
0
votes
1
answer
63
views
Excel formula evaluating some rows correctly, but others incorrectly
The file is a CSV opened in Excel.
I've formatted all the columns to be general and have checked that calculations are set to automatic and have redone the formulas - double clicked to fill the rows. ...
- 1
0
votes
1
answer
295
views
I want to subtotal/running total in Excel
I have set of data, in xlsx format, an example in (pic1) that I want to subtotal/running total as in (pic2). It would be a great help if I achieve this automatically.
NOTE: the result could be in ...
- 5
0
votes
1
answer
135
views
I want reposition/reorganize a set of data in excel
I have set of data, in xlsx format, an example in (pic1), that I want to reposition/reorganize as in (pic2). Is there a formula that can help me achieve this?
Original data
How it should be ...
- 5
0
votes
1
answer
2k
views
Adding Values of a specific label/tag in a spreadsheet
I am designing a spreadsheet for an accounting framework where I record all the transactions. Say there are 3 categories of transactions: A, B, C.
I want to record the transaction, label my ...
-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 ...
0
votes
1
answer
555
views
Excel - Create a list of unique values with their max dates when a third value is true
Working in 2013. I am hoping to achieve this using only formulas. I am starting with:
Part Date Bool
1 2/14/2020 1
1 10/22/2019 0
1 7/27/2020 1
2 8/...
- 1
-1
votes
1
answer
41
views
Return multiple dates, if vald, in Excel 2013
I am using Excel 2013
I have 2 columns, A and B containing dates.
I wish to create a third column which should display, for every date in column A, at column C and same line, all dates from column B ...
- 21
1
vote
2
answers
6k
views
Datetime X-axis in Excel with proportional distances between value points
There are countless of Stack posts about how to create a datetime X-axis in Excel (change from Number to Text in the axis formatting) but this only solves the display of the text. I would like to have ...
- 595
0
votes
1
answer
109
views
Microsoft Excel - Is there any way to split rows in multiple columns by a certain number of rows?
So for example I'm working with data that pertains various information for customers throughout columns containing various bits about them.
Name | email| Phone
Guy [email protected] 50230
And ...
0
votes
1
answer
1k
views
Values not pulled by Power Query from specific column in Excel table
I have copies of a standard-formatted data table in multiple sheets in my Excel 2013 workbook that I am using Power Query add-in to pull data from and compile into a larger table. Each summary table ...
- 1
0
votes
0
answers
24
views
Comparing scores and checking the results correspond to the correct player
I am trying to create a league table that checks scores stored in a different table and can use the IF logic to give either a 1 or 0 under a specific heading (wins or losses).
The problem is that I ...
- 19
0
votes
2
answers
3k
views
How to filter a table onto new tab in excel?
Excel has been out of my scope for a long time, and this feels very simple. Looking for the formula to filter a list, but the results are on a new tab.
Closest thing I have found is this FILTER ...
- 121
0
votes
0
answers
450
views
How to load Excel file correctly in weka?
I am using microsoft excel 2013 ,and I have an Excel file as (.xlsx ) format ,then i saved it as (.csv) format .
when i load this file in weka 3.8.0 and saved it as (.Arff) , the attributes of data ...
1
vote
1
answer
3k
views
Sorting and Reordering columns using Excel Formula
I have a set of excel columns as follows (with n number of rows and columns)
I want it to be Sorted and reordered using the group, but I need it to be done using the formula, and not the simple ...
- 129
0
votes
1
answer
44
views
Excel 2013 - Vlookup Errors
I have a file with 4000+ rows of data. When doing Vlookup, some of the rows worked yet some didn't. It is the same formula that was applied across the rows. Tested with storing data in both tabs as ...
- 3
0
votes
1
answer
9k
views
Excel PivotTable: How do you calculate the difference between a two columns in a multileveled PivotTable?
So say I have a pretty simple PivotTable, with reach record having Month, Type, Lodging and Lodg. Tax.
My records are uniquely organized by Date and by Type (with Types being "Estimate" and "Real").
...
- 128
0
votes
1
answer
1k
views
How to automatically transfer data from one cell in one worksheet to another cell in another worksheet in the same workbook
When I select a particular cell in the 1st column the data from the cells in the 2nd and 3rd column of the same row as the selected cell should get transferred to a two particular cells in another ...
- 125
0
votes
2
answers
92
views
If I enter the letters TL in cell A8, is there a way to turn that cell and multiple others in that row blue?
If I enter the letters TL in cell A8, is there a way to turn that cell and multiple others in that row blue? Is there a way to have multiple letter/color variations?
I have 8 two letter combinations ...
- 11
1
vote
2
answers
79
views
How can I turn other cells red based on another cells data?
I have the cells in column D turning red if the date entered is either today's date, or within the next 7 days. I also need the cells in columns G-J to turn red if the dates entered into the cells in ...
- 11
0
votes
0
answers
138
views
Creating a pen and paper template from a spreadsheet
I work with teams that go to sites and audit compliance.
I have a spreadsheet with columns of data -
Functional Area - Policy Citation - Question # - Question Text
What I want to do is push/pull ...
- 13
0
votes
0
answers
890
views
Excel 2013 - Assign value to cell in Sheet2 based on a conditional in a cell in Sheet1
I would like to assign values to cells in one sheet based on the values of cells in another sheet. I have followed this answer, but it appears to work only if both the reference cell and the target ...
user1077898
0
votes
1
answer
43
views
Excel: Strange behavior related to INDIRECT pointed to itself
Using Excel 2013 ...
I was playing around with the INDIRECT function and found a very strange sequence of results. It occurs when I place the INDIRECT function in a cell (for example, A2) and ask for ...
- 155
2
votes
1
answer
122
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
0
answers
33
views
Can I create a "button" in excel that would bring up the filter menu for a column that the "button" isn't in?
I'm making a reference guide in excel that has a broad audience and needs to look generally polished and non-technical. Because of this, my idea for implementing sorting and filtering was to hide the ...
- 1
-1
votes
2
answers
924
views
How to sum selected column only
If i have a table like this
With excel formula, How do i sum only selected column for each row? For example, Day only (J Column) and Night (k Column) only?
Please don't suggest with plus (+). As the ...
- 13
1
vote
1
answer
30
views
Locating links within excel
I've tried each of the suggestions. I was able to find some of the items under Name Manager and removed those links. However, I still have a link to another excel file that is being prompted each ...
- 11
1
vote
1
answer
2k
views
Excel 2013 - formula to hide a series in a chart based on cell value
Is it possible to hide a series in a chart automatically depending on another cell's value.
Edit:
I am building a dashboard for social media analytics with charts for different metrics (impressions, ...
- 11
0
votes
1
answer
110
views
Excel opens a csv correctly, but cannot import it as text
I am trying to create an excel dashboard of sorts. I need to import an often changing csv file into excel 2013. The file is by default encoded in UTF-8. I cannot change this.
As many, many questions ...
- 3
0
votes
1
answer
39
views
Excel 2013 showing only certain dates in a European format
I have a CSV file where all dates are in a U.S. format (dd/mm/yyyy):
However, Excel shows some - but not all - of the cells in mm.dd.yyyy format:
This is on a server in Germany.
Does anyone know ...
0
votes
1
answer
40
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
39
views
Excel not auto-filling correctly
I have an excel document with formulas in it that are referencing items the same space away. For example:
=C9/C10
=P9/P10
=AC9/AC10
=AP9/AP10
=BC9/BC10
=BP9/BP10
But excel isn't picking up on this ...
-1
votes
1
answer
2k
views
Excel AutoFill Reference from another sheet
I'm working on an excel document where I'm referencing another sheet. The columns in the sheet that I'm working on contains the same formula only the letter changes.
=Sheet7!C$16/Sheet7!C$21
=Sheet7!...
0
votes
2
answers
730
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
1
answer
3k
views
Excel crashing when opening file
When trying to open an excel workbook I get the standard Microsoft Excel has stopped working... and it crashes.
If I open excel first (on a blank document) I can click on workbooks to open them or ...
- 1
0
votes
1
answer
41
views
Retrieving Formulas within Excel concatenate function
I want to parse an excel concatenate statement to find the result of the enclosed expression. Eg: =CONCAT("=1-2-3+7*8") should return 52, and
=CONCAT("=AVERAGE(A1,A2)") should return the average of ...
- 3