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
0answers
10 views

How to find last digit IF next column has a condition?

In Tab 1 I have 2 columns, B with the material numbers of 11 digit, and C with the commentary. In Tab 2 I have one column (E) with the 10 material number digits. In column F I have to find the last ...
1
vote
2answers
17 views

Extract the latest data in an Excel worksheet

I have data in an Excel worksheet. To simplify a bit, there are three columns: the first one (column A) is the name of an individual the second column (column B) is the date a performance was ...
1
vote
1answer
80 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
2answers
3k views

PivotTable Change Data Source broken in latest version (1706)?

Create a sheet "2015": Create a sheet "pivot" und sum the amount: Create a sheet "2016" with different data Try to select the data from "2016" for the pivot table and hit the OK button and nothing ...
0
votes
2answers
4k views

How to bulk convert numbers to text in Excel without formulas?

I have imported tabular data into Excel and it interpreted long sequnces of figures as numbers and displayed them in scientific form: How to tell this thing, that these are text? In previous version ...
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 ...
1
vote
1answer
3k views

Excel 2016 New Window grayed out

I want to have the same Excel file open in two windows. I want things that I do in one window to appear in the same file that is being displayed in the other window. My "New Window" button ...
2
votes
2answers
15k views

Not able to sort columns

I have a pivot table and I would like to sort some columns. But the "sort" function is not working at all. When I click "sort from A to Z" nothing happens. I am just able to sort the first column of ...
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
39 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
1answer
3k views

Calculate average ignoring blank cells which also contain formula

I am having trouble with the final Grand Total Average calculation to ignore “blank” and “zero” from the group of sub totals from the above columns.The blank cells also contain a formula. I only ...
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
52 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
3answers
5k views

Excel “Can't find Project or Libary” - but which one?

Using Office 2016. I have an Excel file with VBA. Whenever I open it, I get the error "Can't find Project or Library". Excel immediatlely closes. I opened Excel and, without opening the file, looked ...
1
vote
1answer
865 views

Getting the error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm using Visual Studio 2017 Enterprise edition, MS Excel 2016 (64 Bit) I am gettin this error while I'm trying to read the data from Excel into a dataset. I checked the solution and I installed the ...
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
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
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 ...
2
votes
3answers
2k views

Find text in the textbox in Excel

I've come to realize the Excel "Find and Replace" dialog couldn't find text in the textboxes. I think I need a macro to solve this. How could I find text in the textboxes: in the active sheet in all ...
0
votes
1answer
3k views

Show data label on error bars

I have a bar chart showing rates per lane (trucking) with error bars showing the % variance between 2 (Feb 2018 and Feb 2019). How do I get the % to show on the error bars, like a data label? enter ...
3
votes
1answer
4k views

How to disable “marching ants” from overriding my external clipboard data in Excel 2016?

Did the copy-and-paste behavior change in Excel 2016? Every time I want to copy something from an external application, I have to press Esc first in Excel 2016 to cancel the marching ants (selection ...
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, ...
-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 ...
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
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 ...
1
vote
2answers
3k views

Excel - Automatically changing to all-bold or no-bold on save

I have a series of steps in Excel. The workbook is quite extensive, with 15 tabs all having different versions of the same steps. I use these steps as a process to write novels. So for example, step ...
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 ...
3
votes
2answers
5k views

Missing Paste (P) option in Excel 2016

I'm copying some data from a webpage which is tabulated. When I click Ctrl+C in Excel 2010 and Ctrl+V to paste, it pastes correctly. However, in Excel 2016, the Paste (P) option is not there. Only - ...
2
votes
1answer
3k views

Excel: Find the earliest/latest values for each date in a list of dates & times

I have list of sequential arrival & departure times like this: | IN/OUT | DATE/TIME | |--------|-----------------------| | IN | 1/2/2019 8:25:59 AM | | OUT | 1/2/2019 11:51:...
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
5k views

How to change Data Source in Power Query for Pivot Data? (Browse Button missing)

Ok, short explanation what I did: I created a Report in a Workbook ("Main File") linked with Power Query to another Workbook ("Source") which contains my data source. I have multiple Queries, 8 of ...
1
vote
1answer
7k views

Save Excel Sheet as CSV in UTF-8 Using VBA

I'm working on the following macro in excel, which loops through all of the Worksheets in a Workbook and saves each to its own .csv file. The issue is that I need it to save as a .csv encoded with UTF-...
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 ...
3
votes
2answers
7k views

Excel 2016 Freezing when entering data into a spreadsheet with multiple formulas

When working in spreadsheets with multiple formulas, if users enter data into a cell then leave the cell, Excel freezes for a few seconds to a couple minutes. The time varies depending on which ...
0
votes
0answers
3k 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 ...
2
votes
2answers
45k views

Excel - pivot table does not group dates

I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script. For some reason ...
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 "...
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 ...
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 ...
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
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
1answer
8k views

Excel formula changes itself - bug or intended behavior?

I have a weird error which caught me out recently. I want to understand if it is expected Excel behavior and if so, is there a way to prevent it or turn it off? You can recreate the issue by creating ...
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
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. ...

1
2 3 4 5
26