Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
2 answers
36 views

Calculate a moving average in excel where the lookback (window) duration is a variable that changes based on another cell's changing values

I am trying to calculate a moving average of a dataset where the moving window time duration is not fixed, but changes over time based on numbers in a different column. Using the screenshot I have ...
lvbx9's user avatar
  • 13
0 votes
0 answers
15 views

using Excel to store and sort Car show data, need to sort 33 classes into 5 groups

I am building an Excel database of a car show's registered users. I have several sheets sorted in different ways (for various purposes). For one page I will have the owners' names sorted ...
Rosie Walker's user avatar
1 vote
1 answer
24 views

Plot data set as an chart in Excel, and then add a second series showing the data's multiple standard deviation bands aligned to data values

I am working with a time series set of data which is the Rate of change between successive price changes in a historical section of a stock price. I have 59 observations. I have also calculated their ...
lvbx9's user avatar
  • 13
0 votes
1 answer
57 views

What is the formula for listing all non empty cells in a range of cells

I'm trying to gather all of the non empty cells in a n x n range of cells, then output them in a single column that I can drag the fill handle to automatically grab the next valid value. I'm running ...
Seth's user avatar
  • 3
0 votes
0 answers
63 views

Need Help Migrating A Drop Down List From One Workbook To Another

I need some help migrating a drop down list and the data selected from that list to another workbook. I have a list of people that need to be contacted each week, with an updated file that is sent to ...
Ok-Fly-9373's user avatar
0 votes
2 answers
27 views

How to Move the cursor one word to the left in ms excel?

In MS Word, Ctrl+left arrow helps use move to the previous word quickly. But the same shortcut in MS Excel takes us to the previous cell. I can't find any shortcuts for the same in the Microsoft's ...
VishwasR Vibhu's user avatar
0 votes
0 answers
28 views

Why don't values fit on the page in editing mode but do actually fit when printing?

In editing mode with the view set on "page break preview" some values don't fit on the page. However, if I go to print, they fit on the page. How does this work? It might not look like it, ...
excelQuestionPerson's user avatar
0 votes
0 answers
20 views

Refreshing data only after every time interval from live master sheet

I have a master sheet that receives live data. I want to link that data to another sheet, which I have already done. The data on the master sheet refreshes instantaneously (which is almost every ...
Shinjinee Maiti's user avatar
0 votes
0 answers
15 views

Excel Rank Formula

I am using the Excel formula: =IF(M2=0,"",IF(M2>0,SUMPRODUCT(($B$2:$B$162=B2)*($C$2:$C$162=C2),(M2<=$M$2:$M$162)/COUNTIF($M$2:$M$162,$M$2:$M$162)))) And I am getting some numbers with ...
Geo Jul's user avatar
1 vote
1 answer
60 views

Excel Pivot Table showing same count as the first column

I'm a beginner at Excel and am currently practicing pivot tables. The data that I'm dealing with is this table: In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" ...
Swetha Rao's user avatar
0 votes
0 answers
19 views

max if functions in Excel to return text from a different row matching the max if conditions

I have an Excel spreadsheet with a summary table that contains credit card charges information from different sheets. The sheets have columns A to F with 'Date', 'Cardholder Name', 'Amount', 'Result (...
user1896365's user avatar
0 votes
0 answers
38 views

How to remove reference to disabled 3rd party Excel Add-ins (NOT COM add-ins!) from menu

This is for Excel 2016. I did find this amongst a hundred other web results regarding the removal of COM add-ins, which I figured for myself, there's a handy remove button right in the menu: However, ...
ch4rl1e97's user avatar
  • 143
0 votes
0 answers
57 views

My windows excel icons are same for .xlsx and macro enabled .xlsm files

I'm running Microsoft Office Professional Plus 2016. The macro enabled .xlsm files, used to have an exclamation mark in their excel icon. For some time now, all type of excel files have the same ...
Sakis's user avatar
  • 43
0 votes
1 answer
119 views

Excel Reference next row in array when logic is false

I want tabs in my Excel sheet to reference fields in a different page if criteria is met; if the criteria is not met, I want to skip that row, and move to the next row where the criteria is met. My ...
ChalmersC's user avatar
-1 votes
1 answer
231 views

Excel help - extract all unique values from column a in multiple sheets

I have a sheet called output, and a sheet for each month a jan, feb etc. Without vba, is there a formula I can use to extract all information from column A in the month sheets, into column a in the ...
Chris's user avatar
  • 37
0 votes
2 answers
123 views

How to TEXTJOIN/CONCATENATE based on constraints for specific no of rows count

I need to concatenate certain attribute value based on the sequence number for specific material number and a formula to automate the process. I have columns as listed in the below screen shot and I ...
Vignesh K's user avatar
2 votes
3 answers
121 views

Weighted harmonic mean that ignores #DIV/0

I have this values: Dataset: I have a row in a sheet (AQ4:KK4) with 255 values. The dataset have some 0 values. Weights: I have a column in another sheet (L3:L257), also 255 values. Weights are never ...
Israel T.-A.'s user avatar
1 vote
1 answer
68 views

Coloring cells across multiple excel sheets in an xls file based on a condition

I am new to excel. I have an xls file with 10 sheets. In each sheet I have two tables of size 10 x 10, one starting at position A4 and the other starting at N4. All the entries are numbers. (Note that ...
wanderer's user avatar
  • 113
0 votes
1 answer
39 views

Copy data values for another column if matched

Sheet 1 has Month, Day and Year while Sheet 2 has Year and Day. If the data of Year in Sheet 1 and 2 are matched I want to copy the Day values from Sheet 1 to Sheet 2. Is it done by VLookup and how?
Mrc89's user avatar
  • 3
0 votes
1 answer
29 views

Row and column "projection" in excel

I have an excel sheet with exactly 1 filled cell per row and column like this: ------------- | 3 | | | ------------- | | | 1 | ------------- | | 2 | | ------------- for which I want to ...
Manfred Weis's user avatar
1 vote
2 answers
225 views

Excel: faster formula to find word that contains search string?

I have a bunch of text strings in Excel 2016. I am trying to search for various substrings. To be more helpful for reviewers, I want to return the word, delimited by spaces, which contains that ...
Michael Cooney's user avatar
0 votes
0 answers
54 views

How to remove labels that values are equal to zero. I have a chart that has a lot of labels, but only a few have values 0<. How to display only values

I have an excel tower graph that is linked to a table that has a lot of categories. Most of these categories contain values that are 0 but may be changed to a value greater than 0 in the future. Since ...
edson's user avatar
  • 1
0 votes
2 answers
289 views

How to group a column based on another column. Then write it into another column

How to group the animal name column based on the category column, and put it on the corresponding column (yellow area). For example in the figure, find the animal name (column A) with herbivore (...
firmln's user avatar
  • 1
1 vote
1 answer
285 views

Hyperlink does not change along with cell when filtered

I’m having a problem with hyperlink when using filters. So let’s say I have these table rows: A B C I have a hyperlink to cell C using the usual hyperlink option. But if I change the filter from “A to ...
Hải Nguyễn's user avatar
0 votes
0 answers
68 views

Trouble creating vertical trendline on clustered bar chart in Excel 2016

I have 2 columns of data in Excel 2016. Col. A is a list of states and Col. B is a percentage. I've successfully made a clustered bar chart (vertically oriented list of bars) with A as the Y axis and ...
user348514's user avatar
0 votes
1 answer
223 views

2 Dates in 1 Cell with Formula in Date Format

I'm working between two tabs in Excel. One tab is for data entry and the other pulls the data using formulas. I'm trying to create a formula where it pulls 2 dates into 1 cell. The formula currently ...
Ni.Sen's user avatar
  • 3
0 votes
0 answers
99 views

How to add Pivot Table field labels to Pivot Table

Is there a way to get the field labels to show up in the table next to the piece of data? Not having the labels here is forcing the user to have to understand what the data is supposed to be and is ...
Paul's user avatar
  • 159
0 votes
0 answers
92 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 ...
Log's user avatar
  • 1
0 votes
0 answers
103 views

Excel Formula to group similar values

I looking for help to solve my spreadsheet to return value of different cells being similar within say +/- 10%. Say that I have in column values 20, 20.5, 21, 21.5, 23, 23.6, 24,24.8, 25.4.. These ...
Andy C's user avatar
  • 1
0 votes
0 answers
35 views

Index/Match function output appears out of sync

I am attempting to use index/match in conjunction with an if/and statement that does the following. If column C = Secondary AND if column D = Information/Videos Take value of column B and output it. ...
Paul's user avatar
  • 159
1 vote
2 answers
60 views

Excel formula help - date formula needed

Im hoping someone can help or advise. I want a formula to count the number of days left in a month from the date of the first of that month. If that month has passed, I would like to it to stop at ...
Chris's user avatar
  • 37
0 votes
0 answers
63 views

Creating a technical excel data sheet to display efficient data

I am creating a table in excel that holds milestone achievement data with their respective amount. This table has multiple milestones that have only one amount, meaning that a set of milestone ...
ExcelNoob's user avatar
0 votes
1 answer
66 views

Excel Sum Table based of criteria missing from another table

I have 3 tables. Table1 is a list of items w/ a sum attached to them. Table 2 is a list of "official items" and a category. Table 3 is a category that needs a sum. i.e. Table 1 A. B Parrot....
plzwork's user avatar
1 vote
2 answers
799 views

Excel 2016 - How to count UNIQUE rows that are "assigned" to someone?

I am building a chart that contains the total amount of rows and accounts assigned to a user in Excel 2016. The first issue is easy and I resolved it with a COUNTIF function, however the second part ...
Anthony LoPrimo's user avatar
1 vote
1 answer
1k views

Excel formula to return column header values based on following rows

I am trying to come up with a formula that will return values of headers for each row that has one more more "x". (As seen in the "results" column below) Can this be done without ...
Chobani's user avatar
  • 11
0 votes
2 answers
108 views

Count number of distinct lists excluding zeros in two-dimensional array

In Excel 2019 I have a formula in a cell which when evaluated with F9 in the formula bar returns the following two-dimensional array (matrix): {0,0; 0,0; 0,0; 0,0; 0,0; 0,0; 44846,1; 44846,1; 44846,1} ...
alejnavab's user avatar
  • 147
0 votes
0 answers
102 views

Excel formula or VBA to create simple coversheet calendar from multiple worksheets

I have several worksheets that I want to create a simple calendar with. Every month I will be creating a new workbook. I don't even know where to start.... Detailed operating information is on each ...
Tina's user avatar
  • 25
0 votes
1 answer
30 views

Converting My Macro into Array?

I have done a Macro code where I was able to show rows which contains the word "Overdue" and hide the other rows which contain the word (Completed, Pending, in progress, Delayed". ...
MaRaM Alhashlamoun's user avatar
0 votes
1 answer
167 views

Mac Excel 2016 formula to add selected cells just by clicking on them

In a previous version of Excel, I could just click on each cell that I wanted to add and it would automatically put a + between each one. I can't figure out how to do that on this version. So if I ...
Mary's user avatar
  • 3
-2 votes
2 answers
10k views

Conditional Formatting with greater than & less than parameters based on date in cell

Trying to conditionally format a cell based on the date in the cell. My ultimate goal is to try and track warranty expiration dates on some of the product we maintain. Within 30 days of expiration ...
LonlyBoy's user avatar
0 votes
0 answers
102 views

The colours and fonts automatically changes on shared excel file

We use a shared document to import data. We have a lot of different colours and informations on it, which is really important it stays that way, it is updated 1000x per day between 30 people. However ...
lylou's user avatar
  • 1
0 votes
0 answers
185 views

Excel - Using VLOOKUP on grouped table records

I have an xlsx report from a finance tool which provides raw numbers but none of the formatting or formulae I wish to use to better read and analyse the numbers. The report is structured like so: ...
Luke's user avatar
  • 279
0 votes
0 answers
37 views

column to row but keep the same the previous row

I have multiple block of data as below and I want to transform it from column to row. Im new to excel so I hope you guys can help me with. e.g: input: A AA 1 2 3 4 5 B BB 1 2 3 4 5 C CC 1 2 3 4 5 ...
35mmWithMeow's user avatar
-2 votes
1 answer
113 views

How to match and merge lookup results in Excel?

I'm a total newbie with Excel and I'm stuck at doing this. I'm guessing there must be some work around "lookup" functions but I don't know which one and how to start. Let's say I have a ...
Chrisengie's user avatar
0 votes
1 answer
5k views

Chart axis labels — Use $1M (million) short form instead of full $1,000,000 number

I have a PivotChart in Excel 2016 that has dollar amounts as the x-axis. Currently, the labels are formatted as full numbers. Example: $1,000,000. Is there a way to format the labels by replacing the ...
User1974's user avatar
  • 147
0 votes
1 answer
118 views

Sum of days between dates in Excel

I have a column where its cells are formatted as "dd hh:mm:ss" When I'm going to do the totaling of the lines and the number of days "no" exceeds 31, the sum works perfectly, but ...
Clamarc's user avatar
  • 657
1 vote
1 answer
177 views

Drag to fill duplicates values

I want to create a sequence of dates that populate a column. A 2023-12-31 2023-12-30 2023-12-29 ... Format Cell is set to Date. Calculation Options is set to Automatic Enable fill handle and ...
oldboy's user avatar
  • 383
0 votes
1 answer
39 views

Excel list - how to select/copy only 5 values of each item?

I have a list containing 400 items with customer names as follows: ItemName CustName MobileNo I need to make a new list that only 5 customers is selected/copied for every item in a single go so ...
IPPB Akola's user avatar
2 votes
1 answer
221 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 ...
Sun's user avatar
  • 6,322
2 votes
0 answers
99 views

Change PivotChart source from one PivotTable (external data source) to another PivotTable (table/range)

I have a PivotChart in Excel 2016 that is currently based on PivotTable-A. PivotTables A and B are identical, other than the fact that they have different data sources: A's data source is an ...
User1974's user avatar
  • 147

1
2 3 4 5
29