Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

2
votes
1answer
22 views

Excel: MATCH() formulas with no wildcard matching

I currently use MATCH() in Excel 2016 formula columns such as: = MATCH( [@[ITEM_CODE]], IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]], 0 ) The result is essentially a foreign key. It is used in other ...
1
vote
1answer
17 views

Comparing 3 columns to 3 other columns for a match?

I am trying to compare 3 columns of data: NPI#, month, and Year to 3 other columns with the same data looking for an exact match on all three. If and/when all 3 match then I want to return the ...
0
votes
2answers
37 views

Change the specific text become a number in excel

Can I actually Make a specific text become a number in Excel? For example, in Column B:B there are a lot of A and B, and I want to change A=1 and B=2. If this is not clear, take a look the picture : ...
0
votes
1answer
20 views

Excel For Mac 2016 Scatter Plot Multiple Series in Rows

I would like what seems like a really simple scatter plot in Excel but I cannot find a way to do it without a lot of manual entering. I must be missing something because it seems such an obvious ...
0
votes
1answer
34 views

How do I disable Excel touch scroll bounce at top of sheet?

How can I disable Microsoft Excel's sheet bounce when touchpad scrolling to the top or left of sheet on Windows 10? You can see what I mean by the video here, https://imgur.com/a/epsgqIV I have ...
0
votes
0answers
8 views

One Specific Row on an Excel Worksheet Not Being Sorted Correctly

I'm having an oddly specific issue with data in an Excel sheet (download https://app.box.com/s/34xjmarn9j8jbme0f1d3shi5rctdigr4). It involves sorting by multiple column headers. All rows in the ...
-1
votes
1answer
39 views

Excel 2016 formating

in the old excel I could format paragraph in a cell to add more space above and below the words typed in. how can I do the same in 2016? I cant find paragraph formatting in excel 2016
0
votes
1answer
48 views

Excel - How can I probe all the precedents of a given cell?

I'm a relatively naive excel user who's been dropped in the middle of an ocean of cells. I've been tasked with coding the value of one cell in a workbook into python. Of course, this cell has multiple ...
3
votes
3answers
28 views

Add only working days in grid style using Microsoft Excel

I like to have a calendar date view of the five working days in Excel. I manually built a first row with dates from 30th July 2018 to 3rd Aug 2018 (Mon to Fri, which are working days) and a second ...
0
votes
1answer
54 views

How to Nest VLOOKUP properly in Nested IF statements

I swear I am not being lazy. After staying up late one night I figured out how to make all this work. But of course I did not save, and the computer froze before I got back to it. Drop Box Link at ...
0
votes
0answers
20 views

Why are the totals of my Pivot Table wrong?

It is clearly supposed to add up but it isn't. I don't know if it has anything to do with my DAX formulas. For instance, PagibigER's formula is: =if(sum(Input[Reg Pay])>0,100,0)
0
votes
3answers
70 views

Excel: how can I find a cell being referenced?

Suppose I'm at cell C1, and the code in it is "=A1". Then in cell D1, I want to see that C1 is linking to A1, and then link to B1 instead. More generally, if a cell X is linking to a cell (a,b), is ...
0
votes
0answers
10 views

Saving an Excel 2016 document without changing its content changes its hash

As the title suggests, the hash (crc-32, md5, sha-1, etc) always changes when saving an excel 2016 file (.xlsx) without having changed any of the contents. This is detrimental to any document ...
0
votes
1answer
40 views

Conditional remove duplicate in excel

I have a dataset where I want to remove duplicates in column H for each corresponding unique value in column I. For example, in the screenshot in the link, if I just remove duplicate in column H there ...
0
votes
2answers
38 views

Excel 2016 time formatting cells

Using Excel 2016, I want to enter 90 minutes as 90:00, or 75 minutes and 33 seconds as 75:33, or 102 minutes and 54 secs as 102:54; then sum the column to report as 268:27.  How do I format the data ...
0
votes
1answer
21 views

Excel - Is it possible to make a drag and drop function to save files directly into a folder?

I am working on a EDMS system which is to be created in Excel. Could anyone clarify whether or not it is possible to drag and drop files directly to the folder path THROUGH Excel? Instead of accessing ...
0
votes
0answers
9 views

Comparing multiple columns in excel 2016 for exact matches and returning a list.

I have a workbook with a random list of id numbers with a specific month and year that I want to compare to another master list of ID numbers, months, and years. Note: each of the ID numbers on this ...
0
votes
0answers
8 views

Excel Sorting dilema

I have a spreadsheet which contains two main columns, one is headed "Surnames" the other "Forenames". The forenames data is a mixture of proper names e.g. "Brian" or Robert Paul" and sometimes only ...
0
votes
1answer
30 views

Aggregate spreadsheet data based on date-time ranges

I have a spreadsheet containing logging data into a certain system Log in time Log out time Id User_type 2018-03-21 18:28 2018-03-21 19:59 1584550 S1 2018-03-21 14:57 ...
1
vote
1answer
57 views

How to make MS Excel cells display dark background with white text instead of the reverse which is default?

By default, MS Excel cells displays white background with black text. This is glaring to the eyes. Some even find it painful. How can we make MS Excel to display black/dark background with white text ...
0
votes
0answers
24 views

Microsoft Excel365: Equation value is turning zero while editing any cell

I've developed Financial Model in Excel 2016 and using it in Excel 365. The problem I'm facing is, whenever I edit any cell, value turns into '0'. I am sharing the Screenshots: Before the edit: ...
1
vote
1answer
44 views

How to disable/enable office clipboard

Question: Is there any way that you can disable clipboard in Office 2016? I don't mean show/hide status when coping or similar stuff from clipboard options. I mean to completely turn it off. It so ...
0
votes
1answer
64 views

Excel language change from local to english - win7

I want to change the language in Excel* (office package) from Danish to English on my firm computer with Win7 enterprise**. I have been through online guidelines and changed to: Display language = ...
1
vote
2answers
74 views

Filtering Excel Data by Column Value and Saving Columns to Individual Files

Many years back we had to come up with a solution to survey results that we were getting via CSV. Back then we'd get data where the first column was e-mails, and subsequent columns were 1's or null ...
1
vote
1answer
29 views

Why does a downloaded spreadsheet not automatically calculate?

I have an Excel spreadsheet that was created using Office 2016 and the sheet that I created has formulas that reference blank cells. This sheet, which can be downloaded here, is set to automatically ...
1
vote
0answers
29 views

How to plot timeline chart with start and end date columns

I have a table like this: | Resource | % | Start | End | |-------------|-----|------------|------------| | John | 0.1 | 10/1/2018 | 11/15/2018 | | Paul | 0.2 | 8/20/...
0
votes
1answer
50 views

Counting t-shirt sizes in a column but also separated by commas

I am counting sizes in a column but some of the cells also have extra sizes separated by a comma. The problem I am running into is counting "L" "XL" and "XXL". The formula I am using to count "S" ...
2
votes
2answers
44 views

Convert a text string with a leading dash to a negative number

The online broker I use must have the most dysfunctional website worldwide! They provide no "export" function, instead telling their users they can just copy data from tables rendered in their browser,...
0
votes
1answer
16 views

How do I use keyboard shortcuts to enter a value into a cell that has a list data validation entry?

So I used data validation to only allow an user to enter values from a list. How do I select something from the list without using the mouse? For example, here's what I made: Clicking on the little ...
0
votes
0answers
14 views

Primary and Secondary axis in Excel 2016

I have two Y axes on a stacked bar chart: the first axis needs to be from 9900 to 10500, the second axis needs to be from 0 to -1200. I would like the X axis to be at 9900 on the primary Y axis and 0 ...
1
vote
1answer
33 views

Assign Nested Sequential unique numbers for items in Excel Table

I'm trying to generate a unique ID number for a items in a table in excel. For example: 1.2.4 For each column the number is uniquely numbered based on the text. For example in the table below would ...
0
votes
1answer
25 views

Copy a hyperlink from one sheet to multiple other sheets

I have a workbook with 12 sheets, one for each month named JAN, FEB, etc. I have a hyperlink in cell B2 of JAN which jumps to cell B200 of JAN. I'd like to copy that hyperlink to all the other sheets ...
0
votes
1answer
36 views

How do I use Ctrl + End to go to the last formatted cell?

Using Ctrl + End on a excel worksheet goes to the last cell in the range (moves in the direction of the arrow) that has some content in it. This usually ignores formatted cells. That is, if you're in ...
0
votes
1answer
54 views

Simple Excel bar Graph [closed]

I am trying to make a very simplistic graph and am not sure if how to do this in Excel. I have monthly claims data (ie: $15M paid in July). I am trying to have a singular vertical bar that has at is ...
1
vote
1answer
59 views

Background color of text, including overflow

I have some cells with text, that overflows into the next column. Now I want to color the background of the text. In Excel I can set the background color of the cell, but is there a way to set the ...
1
vote
2answers
35 views

How do I select a cell range using the keyboard while editing a formula?

When your typing a formula, you can hit one of the arrow keys to select a cell with your keyboard: That's nifty because you don't have to reach for your mouse to select a cell. However, while editing ...
0
votes
1answer
27 views

How can I use “Format as table” option in Excel 2016 without losing my data format?

How can I take advantage of sorting, filtering and generating charts from my data using "Format as table" option in Excel 2016 without losing its current table format? Please note that when I convert ...
0
votes
0answers
17 views

How to convert plain text into hyperlink in Excel?

I have an Excel file with plain Text in Cells. For example: Cell A4 has the text: Check Business Wiki Now I want to convert "Business Wiki" part into a clickable hyperlink in the same text cell (A4) ...
1
vote
1answer
33 views

How to fix the location of a comment with pane freeze?

I'm having issues again with an old Excel bug (currently version 'Home and Business 2016'): I use "Freeze panes" for keeping several headings in my worksheet. I add a comment to one of the headings, ...
3
votes
2answers
55 views

MS Excel: How to use format painter with just the keyboard?

If you want to paste a format, you can copy and use the keyboard shortcut to paste special (Ctrl + Alt + V). MS Excel also has a useful format painter tool. You can select it using a keyboard shortcut ...
0
votes
2answers
34 views

Match a column header (of nonspecific location) against column contents, and get all row headers

I am using Excel 2016 on Windows 8.1. I want to be able to match two criteria, where one criterion is a column header and the other is cell contents that can occur any number of times within that ...
0
votes
0answers
38 views

Is it possible to distribute between start and end date a quantity with Pivot Tables?

This is my first question here. I have a bulk data sheet (in Excel) where the columns look more or less like this: Task name - Task originator - Start Date - End Date - Hours I would like to have ...
-1
votes
1answer
75 views

Turn off Excel Compatibility Mode

I experimented with compatibility mode in Excel 365, now I can't turn it off, every new workbook opens in Compatibility Mode. Crawled the web for an answer, but only found how to save a particular ...
0
votes
1answer
44 views

How can I modify the “Applies To” property of a Cell's Conditional Formatting rule using VBA?

I'm attempting to use VBA to create a conditional formatting rule and apply it to multiple cells. I've attempted this is by creating the rules for one cell only and then editing the appliesto ...
0
votes
1answer
25 views

How to perform the same operation on multiple excel files

I have a series of excel files all numbered sequentially, having data that is of the same format, but taken at different times. I would like to perform the same operations on all the excel files, ...
1
vote
1answer
83 views

How do you round a time and date back to a previous day with a formula in excel?

I am trying to sum up hours for an employee worked in one shift. The employees I'm having a problem with are on night shift. The employees entries in excel are shown as individual tickets for work ...
0
votes
0answers
20 views

Update Values to an Existing Sheet from every new Sheet created by Macro

As the question on the title, I want to update the values of a sheet from every new sheet that gets created by a macro I made. I made a Macro run when inserted Client Name on the inputbox that runs ...
1
vote
0answers
44 views

Excel highlighting selected cell

I'm currently working in Excel a lot (MS Excel 2016) and searching for values. In the search result if I'm clicking on an entry, in the background automatically the cell is selected. Unfortunately ...
0
votes
0answers
17 views

On Excel right click on Sheet tabs is disabled

For the past few days I am not able to right click on sheet-tabs in Excel and select move or copy. The right click menu is not coming up. It looks like it could be a VBA issue as MS support person did ...
0
votes
1answer
62 views

Display Show List when Value selected in Cell

]I would like a dashboard worksheet that has a text box listing the string value of a particular cell in each worksheet in a workbook, but only if that cell matches a specific value. Scenario: I have ...