# Questions tagged [microsoft-excel-2013]

The 2013 version of Microsoft's spreadsheet application.

1,582
questions

**0**

votes

**2**answers

57 views

### How to return column header if a cell contain certain value based on their nth occurrence

I have a table with delivery dates and number of package delivered to a person on a given date. Each person received package with different number of times.
I want to have another table to show when ...

**0**

votes

**0**answers

31 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 ...

**0**

votes

**1**answer

23 views

### Rows to a Pie chart or a table

Sorry if a similar question is posted earlier.
I have a survey output which has rows like shown below
Agree
Disagree
Strongly Agree
Agree
5.Agree
Disagree
7 . Not Applicable
Agree
...
..
...
I ...

**0**

votes

**1**answer

64 views

### How to make data validation with unique formulas in Excel?

I am trying to keep the formulas of a worksheet intact, making sure that they will not be removed by a mistake. Using data validation for it.
This is what I am trying:
It works, but the cell content ...

**0**

votes

**0**answers

31 views

### Excel Bar chart hide #N/A rows without VBA

In Excel, I have two columns:
When I plot a bar chart, I get the following:
How can I hide the #N/A values?
The #N/A are generated using NA()
I've created a Name in Name Manager for column T:
=...

**0**

votes

**0**answers

15 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 ...

**0**

votes

**1**answer

32 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 ...

**1**

vote

**2**answers

69 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
...

**0**

votes

**1**answer

31 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)
...

**0**

votes

**0**answers

202 views

### Remove duplicated based on multiple columns in excel

How can I check data duplicity based on multiple columns and remove duplicate based on another column?
I have a table like:
S.No.....Name...........Date....................Status
1...........Tom........

**1**

vote

**2**answers

42 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 ...

**1**

vote

**1**answer

17 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.

**1**

vote

**2**answers

164 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 ...

**0**

votes

**1**answer

71 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 ...

**0**

votes

**1**answer

134 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

46 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

23 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.
...

**0**

votes

**0**answers

31 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 ...

**1**

vote

**1**answer

142 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 ...

**0**

votes

**1**answer

32 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 ...

**0**

votes

**1**answer

60 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. ...

**0**

votes

**1**answer

97 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 ...

**0**

votes

**1**answer

50 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 ...

**0**

votes

**0**answers

17 views

### Add new rows and Keep the old data in a summary table when source data is changed

I have a source data which is exported from SAP. From the source data, using formulas, the data is summarized as a table in a separate worksheet. (like top 30 items with high cost). Next to the table, ...

**0**

votes

**1**answer

53 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

887 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

**0**answers

13 views

### Prompting comment box

Hi I would like the users of a sheet to put in a comment if they enter a certain value into a cell, how do I automatically bring up the insert comment function by cell value. and then confirm it has ...

**0**

votes

**1**answer

30 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**

votes

**1**answer

23 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 ...

**0**

votes

**2**answers

85 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 ...

**0**

votes

**1**answer

48 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 l@d.com 50230
And ...

**0**

votes

**1**answer

64 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 ...

**0**

votes

**0**answers

22 views

### Create several rows from a single row's data [duplicate]

I basically have something like this:
ID CreationDate AcceptedDate DeliveredDate
1 Jan-1 Jan-10 Jan-20
2 Jan-2 Jan-10 Jan-21
My goal is to create a pivot table ...

**0**

votes

**0**answers

19 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 ...

**0**

votes

**1**answer

42 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 ...

**0**

votes

**0**answers

15 views

### Why do I get an error loading an Excel file in weka? [duplicate]

I am using weka 3.9.3 and excel 2013 , When I load an Excel file as a (.csv) format, I got a Java error ( java.io.IOException: wrong number of values. Read 9, expected 8, Line 2 problem encountered ...

**0**

votes

**0**answers

112 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 ...

**0**

votes

**0**answers

18 views

### Border on a cell only to appear if another cell complete

I only want a border to appear on a cell if data in another cell is completed. If the other cell is empty, i do not want a border to appear on the cell

**1**

vote

**1**answer

78 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 ...

**0**

votes

**0**answers

11 views

### How to add an individual standard deviation for each bar on a bar graph with 3 series, each with two columns

So I want to do something similar adding individual standard deviations to each column. Adding the standard deviation through the normal pathway one single STD appears all over the blue columns which ...

**0**

votes

**1**answer

39 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 ...

**0**

votes

**1**answer

1k 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").
...

**0**

votes

**1**answer

97 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 ...

**0**

votes

**0**answers

34 views

### VBA macro distributed with .xlsm causes Excel to crash on Windows 10 but works fine on Windows 7

So I have this ~150 lines of macro included in spreadsheet that I want to distribute to other users (each will have his own copy, no shared access).
Macro works fine when:
- launched in my developer ...

**0**

votes

**0**answers

109 views

### Not readable greek characters when converting xlsx to csv

My system: I run a fresh Windows 10 Pro N x64 installation on my pc, with MS-Office 2013 Professional Plus 2013 version.
What I want to do: I have some huge .xlsx files with greek text characters ...

**0**

votes

**2**answers

79 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 ...

**1**

vote

**2**answers

27 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 ...

**0**

votes

**0**answers

43 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 ...

**0**

votes

**0**answers

131 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 ...

**0**

votes

**0**answers

375 views

### Windows 10: How can I change the default font & size for Comments in Excel 2013

This used to be possible using a Registry entry in Win8.1, although I can't recall the path.
Also, it appears to have been possible once in Win10:
https://answers.microsoft.com/en-us/msoffice/forum/...