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
1
vote
1answer
74 views

Alternative for a filter formula

I used a filter formula to find all data from another sheet and column, that start with "US". In the column, I have about 5000 rows that have different data with different markets. And the ...
0
votes
1answer
17 views

VBA - How to Delete Hidden Rows Efficiently [closed]

I run a list every day that has varying amount of rows, with different values/texts in them. On average, the list is 1000's of rows long, but I filter it down to about 50. Is there a way to ...
2
votes
1answer
26 views

How can I use Excel Formulas to return multiple values using nested IF statements if I need one of the returnable values to be blank?

This is Excel 2016. I am using a workbook to manage some personal workflow tracking and I have toggles that mark H column cells as "True", "False" or having a zero character string ...
0
votes
1answer
38 views

VBA - Setting Side Borders in Varying Columns and Rows

Everyday I run a list, it is always different. It will have different amounts of rows with different values in them every day. I have coded it so I have horizontal borders exactly as I want, but now ...
0
votes
3answers
45 views

Find all missing dates between two dates in excel 2016

I have over 500,000 rows of data in a sheet. In column D of that sheet, each row has a date between Sep 18 2016, and May 31, 2021. I want to use an array formula to list all the dates between those ...
1
vote
2answers
93 views

VBA Excel - Checking Text Similarity in Same Column

Image 1 Image 2 I am trying to check for text similarity for the text in the "Line-Side Loc" in Column J. If the text in column J isn't the same, I would like a thick bottom border applied ...
0
votes
1answer
51 views

Excel CTRL-V not working properly

Edit This can be worked around by cutting the text from source cell in the cell-edit mode but it's a bit cumbersome. The paste-in-cell-edit fails even if the target cell is empty. Excel 2016 + Win 10 ...
0
votes
2answers
64 views

Excel - Combining multiple cells in a single row

I have data disposed in this manner: Jonh Seedman New York, New York 01/01/1900 5th Ave +1 NXX-NXX-XXXX Homer Simpson Springfield, Illinois....
0
votes
1answer
27 views

Excel file opens file on second double click only

When I double click an XLSX file in Explorer, Excel starts, but does not open the file. When I double click the same file again while Excel is already open, the file is opened. However, if I close ...
-1
votes
1answer
44 views

Unable to see the cells in the Excel workbook

I am unable to understand this worksheet which was written by someone else. There are certain columns which are not visible unless activated. This activation occurs when I change a value in particular ...
1
vote
0answers
14 views

Issue with mail merge rejecting high-content rows

I have a mail merge creating word documents from a (macro-enabled) workbook of 77 columns (two sheets - one for dropping raw data into and another that does some cleanup before the mail merge). I ...
2
votes
1answer
33 views

Excel graph not showing some x value labels

My x-axis for an Excel graph is missing some labels and I cannot figure out why. More specifically, my x-axis is supposed to look like this (I will bold some of them so it's easier to read): 50, 100, ...
0
votes
2answers
50 views

Excel - Conditional format cells based on a column and duplicate and another column's value

How would i conditional format a column for duplicate values if the cell is a duplicate of another value of same column AND another column contains the same value as the duplicate, without adding a ...
0
votes
1answer
50 views

How do I separate these rows from the second column?

I have a large table that, after some filtering for duplicate, looks like the below and I need the "TAGS" to be in the column adjacent to the "SERIALS". I've tried a Pivot Table ...
0
votes
1answer
50 views

Excel: insert rows in multiple tables (not plain cellsheets) simultaneously, sans macro

Hello to SU community, I have an Excel file with 4 tabs: RC, PC, TI, Contacts. The 2nd and 3rd are locked so as to avoid any accidental modification. Tabs RC and PC each contain one table, and the ...
1
vote
1answer
22 views

LARGE function throws error

I would like to compute average scores over top k exams. That's why I need to get top k scores. There is a large function in excel, but I am not even able to execute it in a simple list in excel ...
0
votes
1answer
78 views

How to create “workaround” MAXIFS and MINIFS formulas in Excel?

I am using the following formula to calculate the average daily temperature value in column B (see image below) for each day over a 15-year meteorological record (temperature values are in column B ...
0
votes
0answers
46 views

Show window contents while dragging is not working in MS Excel

While dragging the horizontal or vertical bar, the contents do not move on Microsoft Excel. But the window contents move while doing the same process on a browser or any other software. I have not ...
0
votes
2answers
54 views

How to highlight trends in time records? (system errors)

I have a list of system error timestamps. I want to graph the time from the timestamps to see if there are any trends -- i.e. approximate times of day when the system often goes down. Question: Is ...
0
votes
0answers
12 views

VBA Screen Update in Excel 2016 freezes Excel

I have a macro to sort then pull data from 1 spread sheet to another. I then have another macro to further filter the data. When I run the the first macro to pull the data to my working spreadsheet it ...
0
votes
1answer
36 views

Convert table image to excel using excel

I have the the table below in the image png file format. Would it be possible to convert this to Excel table using Excel?
0
votes
0answers
27 views

ios- excel formulas from 2019 to 2016 [duplicate]

I was trying to write a "MAXIFS" formula on my excel 2019, but when I opened the file on a computer with excel 2016 it didnt work. And i read that for 2016's version, i should use the "...
2
votes
1answer
20 views

How to Change Orientation of Multi-Level Labels in a Vertical Excel Chart?

I have the following chart and I am not able to edit the first level of labels (membership, chapters, etc). When I go to Format Axis -> Alignment I can only change the orientation of the second ...
1
vote
0answers
34 views

Find Replace from excel to word template macro

I want to use excel to find (ColA) data and replace it with (ColB) Data in to a premade word template I have the macro that already opens up the word template with what i want to find/replace I am ...
2
votes
2answers
746 views

In Excel, how can I round or display numbers to 2 significant figures (the two leading non-zero digits of a decimal number)?

In Excel 2016 I need to show zeros out to the leading non-zero digits and then just the two digits after that. For example: 0.00002423 should read 0.000024, 0.242 should read 0.24, 0.0032255 ...
0
votes
0answers
13 views

Excel file saved many times but version history is blank

I have been editing an Excel workbook for 6 days. The workbook is saved in sharepoint and also marked to always be kept on my laptop. I have manually saved this .xlsx file dozens of times in the last ...
2
votes
0answers
38 views

Excel rows of data suddenly disappeared after undoing deletion of unrelated rows. Version history suddenly blank. Get my data back?

I have been editing an Excel workbook for 6 days. The workbook is saved in sharepoint and also marked to always be kept on my laptop. I have saved this .xlsx file dozens of times in the last few days. ...
0
votes
1answer
41 views

How can I delete a column in sheet 2 based on sheet 1?

I have two different sheets: "Input"(sheet2) and "Instruction"(Sheet1) I want to delete the whole column in "Input" sheet corresponding to the value in the cell(row 34, ...
0
votes
0answers
33 views

Excel 2016: How to find a specific name in sheet “A” and and get the adjoining cell's value to a cell in sheet “B”?

Excel 2016: How to find a specific name in sheet "ASPI" and and get the adjoining cell's value to a cell in sheet "Watchlist"? I have currently used the method of this formula to ...
0
votes
2answers
40 views

Division calculation within pivot table values in excel

Please see example below. The row headings (redacted) are defect types of a product. The column headings are the name of the products (redacted) and just below are the total sales of that product. ...
0
votes
1answer
28 views

#VALUE! error using excel 2016 nested IF statements

I'm unable to perform a simple nested IF statement on this data, the conditional seems perfect to me so it must be a formatting error or something else. I'm new to excel so I could be wrong. This is ...
0
votes
1answer
44 views

How to return first non-blank value with LOOKUP function

I have a table: table in which I reference data from a dataset using a LOOKUP function. I can reference columns(such as new_deaths) which have the latest(non-blank) data just fine. However, for some ...
-1
votes
1answer
106 views

Excel - How to delete the infinite blank rows?

We have 5000 records of data. For some reason, our Excel file has over a million rows where every column is blank. How can we get rid of these 1 million + empty rows?
0
votes
2answers
105 views

Is there a way to copy all empty cells in a data set on one sheet and paste the empty cells into a data set on a different sheet?

Is there a way to copy all empty cells in a data set on one sheet and paste the empty cells into a data set on a different sheet? For every cell in the “Sales Sheet” that is blank, the corresponding ...
0
votes
0answers
20 views

Excel: Writing a 'report' within a worksheet

Summary I want to have a specific worksheet within a workbook display a set of data from another worksheet within the same workbook - like a kind of report - in a specific order, for re-use/population ...
0
votes
1answer
43 views

Excel - automatically updating cell reference when applying conditional formatting to range

My question concerns conditional formatting in Excel. Here is a summary of my situation: I have conditional formatting set up for a particular row in Sheet A. The formatting formula checks whether ...
0
votes
0answers
36 views

Excel Chart “Save as Picture” making dates into numbers

I'm trying to save a picture of an excel chart (for use in distributions where it doesn't allow me to just copy and paste the chart). So I right click the chart I want to save, click "save as ...
0
votes
1answer
28 views

Excel: Drop down with month instances

Is there a possibility to have a drop down with months inside, when choosing one month to show me all instances that are within that month? Something similar has Excel format Tables, when you choose ...
0
votes
1answer
23 views

Combining an Excel IF Statement with VLOOKUP

I have the following if statement in Excel: =IF(VLOOKUP(AB2,AD:AD,1,0)=TRUE,1,0) What I am trying to do is say the following: "If the results of the VLOOKUP return any value other than "#N/...
1
vote
1answer
47 views

COUNTIF across multiple sheets does not work with SUMPRODUCT

I have the two sheets below. (The example table above is highly simplified for this question) I'm trying to write a formula that adds the total occurrences of A under the year 2017 (Answer is 3). ...
1
vote
0answers
11 views

Excel table.combine({Sheet1},{Sheet2}) erases data on an append

I have a power query combination that looks like this: = Table.Combine({#"Sheet1", #"Sheet2"}) Now the tables combine, and most of the data appends, but some rows are missing in ...
0
votes
2answers
35 views

Excel: Return all matches in columns using one criteria (from drop down)

I am having two tables, one table is main data, separated into many columns and blocks per 10 rows in one, this is fixed. In Column A are unique numbers that will be used in drop down. Table 1 On ...
0
votes
1answer
32 views

Changing the layout of an Excel Sheet

A little help will be deeply appreciated. I have a database whose layout is not convenient for me. Here is the layout: I would like the sections within the FINANCIAL column to become headed columns ...
0
votes
3answers
55 views

How to split a string by certain length in excel and put it into columns

In excel 2016 I want to split a single string with no separators into a 2-character cell under column A (same column). The string has no fixed length. Example AAB4101X -> AA | B4 | 10 | 1X Example ...
0
votes
1answer
25 views

Blocking certain rows for certain users when sharing an excel-sheet

This year, I will finish a master I've been doing and enjoyed, and we, the participants, would like to stay in touch after the course ending. We already have the WhatsApp Group, but it's difficult to ...
0
votes
1answer
38 views

Conditional formatting for specific group of values/rows based on cell value(s)

I am having some data table that is separated in lets say "blocks" of rows. As you can see on the image, starting from Column D (A1) there are some values in first row, and then 5 rows empty ...
0
votes
1answer
45 views

MSExcel: Is it possible to make an adaptive formula through reference?

Example: Desired result: (Please note that the red values in the first image above are made red to signify that they are actually functions, imagine adding an "=" sign before them.) I have ...
0
votes
1answer
26 views

Cannot Reference Pivot Table headers due to header formatting

I am writing a SUMPRODUCT function that references headers in a pivot table for my report on a separate sheet. The header that is giving me an issue is the year, i.e. 2020, 2021, etc. I have tried ...
0
votes
1answer
94 views

Open Semicolon Delmited Text File in Excel Via Double Click

I can make Microsoft Excel open up a semicolon separated value text file by placing "sep=;" at the beginning of the file. Then upon double clicking, it properly separates every column and ...
0
votes
1answer
43 views

SUMIFS with three criteria

I am trying to write a SUMIFS formula that will aggregate my data based on three criteria. As per the first table, I am presented with data that provide the sales for each agent, broken down by three ...

1
2 3 4 5
26