# Questions tagged [microsoft-excel-2016]

The 2016 version of Microsoft's spreadsheet application. Unlike previous versions, this Office Suite is cross-platform, working on both Windows and OSX. Because of this, when using this tag, it may be applicable to also tag your question with the Operating System you're on.

1,155
questions

**0**

votes

**1**answer

20 views

### Excel: Is it possible to select multiple values in Drop-down list? (without VBA)

Is there any possibility to mark more values in drop-down list and to have them in one single row?
This is the way I found but still it is not inside a single row...
My DDT
And this is actually ...

**0**

votes

**1**answer

39 views

### Excel formula needed: counts days elapsed over varied row lengths and N/A cells

I am trying to create a formula that will tell me the number of weekdays that has elapsed between the date entered into column M and the last date to be entered into columns N-Q for each row. Ideally ...

**0**

votes

**1**answer

30 views

### Group/collapse rows in a true Excel table?

In Excel, we can create a table in a sheet (Overview of Excel tables):
Question:
Is it possible to group/collapse rows in an Excel table -- similar to Excel outlines?

**0**

votes

**0**answers

37 views

### All the excel sheets disappeared except the first one

I got stuck into a strange problem I never came across with MS Excel till date... I'm using latest version of MS office 365 ProPlus. The last time I updated my office was nearly 2 weeks back. I have ...

**0**

votes

**2**answers

114 views

### Check if parent row has corresponding values

I have a table in an Excel spreadsheet that has hierarchical rows:
(Hint: The data below can be copied from the EDIT-view in Stack Exchange and pasted into Excel. This method will result in proper
...

**0**

votes

**4**answers

55 views

### How to combine SUBTOTAL and SUMIFS in Excel?

I am struggling to combine my SUMIFS formula with SUBTOTAL. As you can see in the picture, my SUMIFS formula (highlighted in yellow, formulate pasted as text below it) sums up Column C when values in ...

**0**

votes

**0**answers

15 views

### Excel error: “No more new fonts may be applied”

I suppose I should start by asking whether anyone knows if Excel / Microsoft has a limit to how many different fonts can be used in a single document.
I keep a spreadsheet of all the fonts installed ...

**0**

votes

**0**answers

17 views

### How can I reference and display the contents of a cell in an Excel document with a Word document, keeping the reference path relative?

I wish to reference and display the contents of a single cell in an Excel document in a Word document, but keep the path to the Excel document from the Word document relative. The folder containing ...

**0**

votes

**0**answers

32 views

### Excel: how can I find the closest date in 3 distinct lists, where all 3 lists have the same date present

I'm trying to automate a reporting process where by we summarize data from 3 separate reports for the most recent Friday. Sometimes for whatever reason one or more of these 3 reports won't run; at ...

**0**

votes

**1**answer

26 views

### I saw a pdf which held a small (blank) excel table which had a cross in the corner. It seemed to be embedded in the pdf

When clicked it opened up the excel table in the pdf. How do you do that?

**7**

votes

**3**answers

540 views

### Left trim: Remove characters after last instance of a number

I have text in Excel that is a combination of numbers, letters, and symbols.
010-038-310-1500-93745
010-038-310-3000-xxxxx-xxxxxxxx-xxxxxxxx
010-038-???-????-?????-????????
010-038-190-3109-?????-...

**0**

votes

**1**answer

35 views

### Excel 2016 Chart > Date and Text on the same Axis

I received an interesting question yesterday during at work. Is it possible to have Date and Text values on the same Axis in Excel Chart, but that this "text" is actually formatted or somehow ...

**0**

votes

**1**answer

30 views

### How would I copy an Excel sheet that has formulas with “Table2[Date]..” to a new sheet?

I created a sheet that uses several tables. There are many formulas that reference different rows of different tables on the same sheet. The sheet was for the month of Feb. I want to copy the format ...

**-3**

votes

**1**answer

61 views

### Finding the year in another table in Excel when the value isn't the same

I have two tables. I would like to get the data from Blue to the Orange table with following steps:
The first column of Orange table has both positive and negative values as percentage, and I need to ...

**1**

vote

**1**answer

32 views

### Getting a named value in Excel - like a global variable

I often have a sheet in my Excel spreadsheets, that contain some global values I use all over. for example a VAT rate or a currency exchange rate.
I can access one of these "global values" something ...

**0**

votes

**1**answer

36 views

### copy formalas of multiple rows

I have worksheet with 100 rows (for example). I creating a new sheet to cover it to 400 rows.
The original looks like:
name1, value1, name2, value2
a, 1, b, 1
c, 1, d, 1
Want to make the new sheet ...

**0**

votes

**1**answer

20 views

### Statistic chart in Excel: why there is one bin for the “Overflow”?

I have this "Statistic Chart" plotted in Excel, in which I set 25 as the "Overflow bin". But why there is a designated bin at 25 (highlighted in red square)? How to remove this? I just want to show ...

**0**

votes

**1**answer

19 views

### sumifs 2 different tables

I have 2 different tables, one containing the world's population per country over some years and the second the C02 emitted by the certain of those countries. I am looking to have the ratio of C02/pop ...

**0**

votes

**1**answer

29 views

### How to plot multiple y values with a same x value?

How can I plot this set of data on Excel or OriginLab, possibly scatter plot, where a same x value corresponds to multiple y
x, y
1, 1
1, 2
1, 3
2, 2
2, 3
2, 4
I actually have hundreds of x ...

**0**

votes

**2**answers

133 views

### Split text at last instance of character (retain left hand side)

I have hierarchy path values in column A of an Excel spreadsheet:
FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM \ COMPRESSOR - DRYER
FLEET \ AIR SYSTEM \ VALVES
FLEET \ AIR SYSTEM \ RESERVOIRS
FLEET \ ...

**0**

votes

**1**answer

61 views

### Hierarchy path: Find orphans (children without parents)

I have hierarchy path values:
FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM \ COMPRESSOR - DRYER
FLEET \ AIR SYSTEM \ VALVES
FLEET \ AIR SYSTEM \ RESERVOIRS
FLEET \ AIR SYSTEM \ HOSES - LINES - ...

**1**

vote

**1**answer

132 views

### Extracting data from one Excel sheet to another, with no spaces in rows

I've got an Excel workbook with 3 sheets. One is MasterList. One is Assigned. One is Contacted.
In the MasterList, there's a dropdown next to a name. In that dropdown is "assigned" or "contacted." ...

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

**0**

votes

**0**answers

18 views

### How to extend 2 tables (in different sheets) at the same time in Excel? And how to create specific PDF?

I have an Excel with 2 sheets, in the first one I have a table with Products and Prices, in the second one I want to have the same products and the price equal to the first price plus 5%. I can do ...

**0**

votes

**1**answer

25 views

### Filling/Highlighting the cells according to the numeric value

I would like to highlight a series of cells based on the numeric value present in certain cell (please refer to attached SS for more details.
Sheet 1
Sheet 2
Basically, if the value in e.g. Sheet1!A4 ...

**0**

votes

**0**answers

3 views

### resetting histogram data columns to new axis dimension in Excel

In Excel 2016: After plotting data as a histogram I need to reformat the horizontal axis to a more suitable numeric range. The original columns (bins) remain in the old axis-scale position. How do I ...

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

**0**

votes

**0**answers

13 views

### How to pick out reallocated data and carry it to levels ahead

I am working on a set of company wise data where I am classifying by product type at 3 different levels.
My data has some "Unknown" entries at each level which need to be reallocated between my ...

**0**

votes

**0**answers

15 views

### Slicers are changing width on pivot refresh

I have a strange problem. There is a pivot table on separate sheet from the data(Data is a table) and there are about 6,7 slicers for that pivot. When I add new data to Data Table and do "Refresh All" ...

**1**

vote

**1**answer

15 views

### Applying 'weight' to pre-existing 'IF' statement in Excel

I have an excel worksheet that is being used as a 'skills assessment' form. The current set up features a formula that allows users to select a 'rating' from a drop-down menu and based on the ...

**0**

votes

**1**answer

29 views

### How to get the average with intervals?

So I have a dataset of monthly stock returns from January 2000 until today for six different stock indices. At the bottom of the page, I want to show the return across the year as a percentage. When I ...

**0**

votes

**1**answer

32 views

### Excel Validation: How to prevent duplicate entries globally within workbook?

I want to prevent duplicate code assignments within the same workbook but across several worksheets. The entries will be manually assigned and entered, and ideally I'd like to have, e.g., data ...

**0**

votes

**1**answer

16 views

### How- A cell value auto copy and paste in B cell at once and whenever the value change of A cell, the value of A added in B cell?

I want to make an auto calculator cell that means how many times, I am using that particular cell for value, it should be regularly calculated.

**0**

votes

**0**answers

11 views

### MS Office 2016 Excel Auto filling a drop down menu in one cell based on another cell drop down option selection

This looks simple, but couldn't figure out how to do it.
I have two drop down cells A1 and A2, both have same drop down options.
A1 options are "Yes", "No".
A2 options are "Yes", "No"
Question is: ...

**0**

votes

**0**answers

13 views

### Excel Rank by Percentile with Duplicate Row Values

I have a document that has this structure
Transaction# UserID UserAvgSpend
I need to add a 4th column based on UserAvgSpend that puts each user in a percentile bucket. Each transaction is unique, ...

**0**

votes

**0**answers

21 views

### Filter not showing entries in Excel

In the attached Excel sheet the first columns have filters that show the right options. The columns for the dates only show for the filter "Select All". Filter by color is also off. For example, if ...

**0**

votes

**2**answers

82 views

### SUMPRODUCT + INDIRECT reference, How to make it work if i'm adding rows?

I have a question/problem, I have an excel formula that looks something like this
=(SUMPRODUCT(((INDIRECT("'"&G9&"'!"&$A$9)=$I$6)*(INDIRECT("'"&G9&"'!"&$B$9)))))/1.16
It is ...

**0**

votes

**1**answer

10 views

### How to include more dates on my x axis

Iām having issue to include additional dates on my x axis.
Usually Iād select data, edit my horizontal axis label to include more dates. (2nd pic) But itās not showing up on my graph. (1st pic) I also ...

**0**

votes

**1**answer

71 views

### How to Copy Range of Filtered Data and Paste it to new work sheet in Excel VBA (without Using Clipboard)

I want to Copy ColumnA and B to New sheet Range A and B (Source Sheet Filter Applied in ColumnH)
This code is Recorded one, When i use this Random Error comes. Because i have 5 submacro and When i ...

**1**

vote

**0**answers

16 views

### How can i compare 2 columns row by row in excel and then extract unique information?

My Question(s):
1) Being able to compare Column A word in Column B and highlight the difference but doing this comparison row by row between the 2 columns.
Iam working with a large data set of 48k+ ...

**0**

votes

**1**answer

43 views

### Keep main file open when doing Save As in excel

Is there any way to keep main file open when doing Save as in excel. When you do Save as excel is switching to saved file and I need to manually open previous file, from one that I did Save as. Is ...

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

**1**

vote

**1**answer

49 views

### Dynamic Excel Formulas For Ranges

How can I make a formula that:
I can vertically drag down on Sheet2...
That TEXTJOINS two arrays (total of 28 vertical cells) of data on Sheet1...
Which after it TEXTJOINS those 28 cells, goes to ...

**0**

votes

**1**answer

24 views

### Dynamic Excel Formulas

How can I make a formula that I will vertically drag down on Sheet2 that references horizontal data on Sheet1 which changes to the next column after it gathers the data?
I need a formula in which ...

**0**

votes

**0**answers

16 views

### How to make a value field show the latest value in a pivot table

I have a table like this:
And would like a pivot table like this:
Except instead of showing the sum of amount group by Region in the value field, I want to show the latest value based on date. For ...

**0**

votes

**0**answers

33 views

### how can I average values in Excel software?

I have 30 data in and i want to calculate the average for every 2 data with excel. That is, the average of the first 2, then the second 2, etc. And to have the results together and without spaces.

**0**

votes

**0**answers

13 views

### Grouping and ungrouping resets filters

I have some data(it's not a table) that has filter and grouping applied to it. When I group and ungroup it; filters are reset. Is there any way to stop filters from resetting and still be able to ...

**0**

votes

**0**answers

60 views

### Can't Autofilter Over Pivot Tables in Excel 2019

I have a file with a pivot table created in Excel 2016 (rows 5ā7 in the image).
Above the pivot table there is a row of column headers (row 2 in the image). This row is -not- part of the pivot table....

**1**

vote

**0**answers

16 views

### How to group in Power Query such that group headings show above items in the rows

I need to convert information from a database into a time schedule by office. Dates as column headers.
Management wants the rows to be names of employees sorted by office.
How can I make a column ...

**0**

votes

**1**answer

16 views

### Counting unique text based on values from another column [closed]

Need help to create a formula to count the cells with unique text in column A that has a non-zero value in column B.