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
4
votes
2answers
11k views

excel - activate addin through a Group Policy object

I have an addin that I'm installing on the roaming profile of users. Yet I have to activate it manually for every users. I thought about activating it automatically with GPO. I read that but it ...
2
votes
2answers
230 views

Dynamic Charts In Excel

Is it possible to add a new series to a chart when adding a new column to a table? For example, I have the following chart: Pulling data from this table: If if I add a new column to the table, for ...
2
votes
3answers
2k views

Return sum of multiple vlookups

Okay, so I have an issue I'm trying to solve all in one formula. I have a setup of tables: How many NFPI (Number of Fruit x Percent Intensity) do I have for Apples? I need to scan column G of Table ...
1
vote
3answers
1k views

Strange residue on cell with Data Validation when using Excel VBA

Thanks to Raystafarian I get rid of that left over Data Validation residue box by deleting all of them from the work book before exiting. Private Sub Workbook_BeforeClose(Cancel As Boolean) ...
0
votes
1answer
191 views

Align two columns in excel based on 1 criteria

This has me stumped. if you have data that looks like ABCD: How do I get it to HIJK? I am trying to put 2 pivot tables next to each other, so I can compare the two sheets, but when I do it ...
7
votes
3answers
569 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-?????-...
4
votes
9answers
55k views

Excel chart formatting lost when Refresh All or individual Right Click on Data > Refresh

I have 4 pivot charts that rely on data that is refreshed from a connection. When I click refresh all I lose all the formatting I set up (colours/borders/line and bar and 2nd axis choices) I have ...
8
votes
2answers
12k views

How to create custom shortcuts in Excel 2016 for mac

I recently updated to Excel 2016 from Excel 2011 for mac, and I'm missing a very important feature which saved me a lot of time: custom keyboard shortcuts, i.e. being able to create new shortcuts for ...
5
votes
3answers
22k views

Stop Excel formula from changing when inserting/deleting rows

I'm trying to set up a budget workbook for my personal budget using 13 sheets, 1 for the totals and the other 12 for each month. I cannot for the life of me figure out why the formulas I have change ...
3
votes
1answer
42 views

Autocomplete with Excel with any data in the same column

Let's say you have 3 lines in a column: abc, def, hgi. If the cursor is in the next line, and you start writing a, auto-completion is offered, which is handy If there is an empty line, and you start ...
2
votes
1answer
214 views

How to have Office 2013+ Applications Open to Blank (Workbook, Document, etc) automatically?

Every time I launch microsoft excel 2013 (2016) (or Word, etc) it gives me a screen where I need to select which template I want to open - How do I avoid this selection screen, like in other ...
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 ...
1
vote
1answer
288 views

Attempting to transpose two column table in excel using first column to group values in second column

I have a two column table in Microsoft Excel 2016 that simply lists a expense category in first column and expense amount in second column. expense category are not unique and will repeat multiple ...
0
votes
3answers
201 views

Calculate the median of the 20 peers that are closest in terms of total assets

I'm trying to calculate the median P/E ratio of the 20 firms that are closest to the firm to be valued in terms of total assets. I got all my company (S&P 500) tickers in column C, the respective ...
3
votes
1answer
1k views

Match with interval

I have this table I have to get the index of column which has the interval that contains 165 (Tier 2). For example 165 -> Tier 2 15 -> Tier 0 ... I tried to split the text interval in MIN and MAX ...
3
votes
2answers
84 views

I want to count IF both condition are true Logically count by AND excel

I have two Columns both are categorical columns. Like Age_group and Engagement_category. And I want to get count no. of each engagement_category in each Age_group. | Engagement_category | Age_group | ...
3
votes
2answers
2k views

Numbers in scientific notation imported to Excel 2016 as text

This is a repeat of Error in importing the numbers in scientific notation. That question was never answered and I am not allowed to comment. I am having the exact same problem with Excel 2016. In ....
2
votes
1answer
27k views

How to conditional format based on cell color

I have excel 2016 I am trying to conditionally format adjacent cells based on the color of the cell i.e. if B5 is green then A5 should become green. Is this possible? I'm not concerned at this time ...
2
votes
1answer
27 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 ...
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. ...
2
votes
1answer
2k views

Auto-populate cells in Excel

As you can see in my screenshot, I have a table called "Payment Details Form" that needs to be filled and printed for each individual of 100 employees: In another worksheet, I have a data table of ...
2
votes
1answer
60 views

Transpose unique rows to columns while IF statement conditions are met but do not consolidate

Current State: A B C D E FG Component FieldKey Cost$ Qty 7120 7120 1220 $4.71 9,783 7120 9907062 1220 $7.80 4,061 7120 ...
2
votes
1answer
102 views

Change cell color when a different value is encountered

Hello there. I Inserted an SQL query in Excel to pick for me data like the above. Is there a way I can set Excel to automatically Change The Background color each time it encounter a different value....
2
votes
2answers
2k views

Is it possible to convert MCQs on excel into Word?

Is it possible to convert MCQs (Multiple choice question) on excel into Word? I have an excel file which has many MCQs with the options and the correct answer (each one of them is in separate column) ...
1
vote
1answer
85 views

MS Excel - Combine tables with only some matching data

I have 2 sets of data that I want to combine into one table. I have tried to use Power Query but I don't think it can quite do what I am after and was wondering if there is another way. It might be ...
1
vote
1answer
443 views

How to ignore column values under a certain threshold when making a chart in Excel

I have a column of about 14,000 populated numerical cells, and when graphing this column I would like to ignore all values that are less than 1000. These values are due to error in sensors and show up ...
1
vote
1answer
4k views

Improving Excel poor filtering performance [closed]

I am having some serious performance issues with Excel when trying to filter relatively small data sets (50,000-250,000 rows). Filtering a single column on a 100k row dataset to exclude a value may ...
1
vote
1answer
12k views

How to automatically import data from csv file and append to existing Excel table

I have a .csv file and a master excel file. The master file contains a table and I would like to automatically append the data in the .csv file to the existing table. The data has the same headers and ...
1
vote
1answer
53 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 ...
1
vote
0answers
170 views

Localized shortcuts for official US, not working ones

Apparently Microsoft maintains only one list of Excel keyboard shortcuts for Windows: Keyboard shortcuts in Excel for Windows. But in localized versions of Excel with various keyboard layouts not all ...
1
vote
2answers
11k views

View Source code Microsoft Excel

Is there an equivalent to view source code in microsoft excel for either the entire excel workbook or possibly for individual cells? Ideally I would like a way to view a list of every possible option ...
1
vote
1answer
258 views

Extract Unique Substring from Range of Excel

I have cell values with names that are fairly close to each other. I would like to extract a common sub-string. Here is an example. 1100_250_Jump_12HR_100MD_S_run1 1100_250_Jump_12HR_100MD_S_run2 ...
1
vote
1answer
357 views

Reinstate worksheet protection upon save and close

I currently have a VBA code that will automatically unprotect all of the worksheets in my Excel 2016 file by username. Currently when I open the file it asks for a password. I do not want this to ...
1
vote
1answer
820 views

Finding the top 10/15/20 percent (excel 2016 or google sheets)

I have an alphabetical list of students in Column A. In Column E is the total number of award dollars they have earned. I need to find the top 10% award earners, top 11-15%, and then the top 16-20%. ...
0
votes
1answer
4k views

Excel bar chart with gradient values (not percentages) and value lines

I am trying to create a bar chart that has a gradient fill specific to static values and then a line to indicate the actual value. My data is like so: Name Time A 16 B 33 C 40 D 45 And ...
0
votes
2answers
130 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
0answers
19 views

Get data from the cell of next row in a table, if a cell is found empty

I have to create a formula based table on the next sheet (e.g. Sheet-B), based on the data/table on "Sheet-A". My 'Sheet-A' data has 100+ rows and 30+ columns, where 'Column-A' has text data ...
0
votes
2answers
6k views

Extract numbers after a specific word in a cell

I have an XLS file that lists hundreds of various times that are all a single string in a single cell, all in the following format: Days: 103, Hrs: 12, Mins: 15 Days: 34, Hrs: 8, Mins: 27 Days: 10, ...
0
votes
2answers
3k views

Excel 2016 Macros - Windows/Mac Issue

I'm using Excel 2016 (Desktop - not online) and I've created a worbook with a Macro that I've signed using a self-signed certificate. I've set the 'trust center' settings on Windows so it will only ...
0
votes
2answers
94 views

Keep only one copy of the duplicated data

I am trying to clear out a list of content that appears 2,3,4 or more times repeatedly. I have two columns and I would like to eliminate the whole row of any repeatedly entry of column B. My ...
0
votes
2answers
134 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
3k views

Excel prevent cell and its content from being deleted (no sheet protection)

I have 2 columns in my excel sheet which I want to have protected in a way that you can't delete the cell nor its content. I don't want to use the built in sheet protection, instead I want to use VBA (...
0
votes
2answers
393 views

Add custom category type column to an Excel 2016 Power Query

Used the power query feature of Excel 2016 to import several CSV files containing personal financial history and trying to use the power pivot thing. Problem is I really need to group the ...