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.

Filter by
Sorted by
Tagged with
0
votes
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
4answers
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
0answers
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
0answers
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
0answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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.

1
2 3 4 5
24