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
1 answer
33 views

Formula to get comma-delimited list of unique values from table column

I have a table in Excel 2016: ID TIME_PERIOD -- --------------- 1 FORECAST 2 HISTORY 3 BEYOND FORECAST 4 HISTORY 5 BEYOND FORECAST 6 BEYOND FORECAST 7 FORECAST 8 FORECAST 9 ...
user avatar
  • 105
0 votes
0 answers
8 views

Filling following Six cells based on the letter entered in one cell

I have a sheet where I have to enter the letter combinations that shows the time taken by the product on different work stations. For example, if a products "X" arrives in the company, it ...
user avatar
1 vote
0 answers
30 views

How to set color for Maximum and Minimum Value bar in Clustered Column Chart?

Let's say, I've the following Pivot Table generated from a Random Sales Dataset. And the corresponding Pivot Chart. How can one make the Maximum & Minimum value bar of "Sum Of Profit" ...
user avatar
0 votes
1 answer
15 views

Reuse an existing external datasource connection for PivotTable (without creating a duplicate connection)

In Excel 2016, I can create a connection to an Oracle database (via ODBC) using Microsoft Query or the Connection Wizard. Creating a connection using one of those methods works as expected. The ...
user avatar
  • 105
0 votes
0 answers
14 views

Dynamic filter in PivotTable based on the system date

I have a pivot table in Excel 2016. In the PivotTable, I want to create a filter on the data's YEAR column so that only records where the YEAR is between this year and this year + 9 are used in the ...
user avatar
  • 105
0 votes
1 answer
7 views

Navigate to a specific table/view in list of objects in Microsoft Query

When setting up a Microsoft Query connection (to an Oracle table/view via ODBC) in Excel 2016: Problem: I have hundreds of Oracle views to scroll through in order to get to the view that I want: ...
user avatar
  • 105
1 vote
1 answer
32 views

Stacked bar chart to show asset maintenance types by year

I have a table of roads by year. The NEXT_ACTIVITY column categorizes the roads by the theoretical maintenance activity that the roads fall into. YEAR COUNT/TOTAL ROAD_COUNT NEXT_ACTIVITY ...
user avatar
  • 105
1 vote
1 answer
20 views

VBA code to enable three Cells, each in its own worksheet, equal to each other always

I have three cells in three worksheets: Cell A1 in Sheet1, Cell A2 in Sheet2 and Cell A3 in Sheet 3. I would like to have values in the three cells always equal to each other. This means that an ...
user avatar
  • 113
1 vote
2 answers
43 views

Why can't INDIRECT() take a dynamic array reference just after opening a file?

I have a formula containing INDIRECT() pointing to some range -- call it B4:F8. If I specify the range explicitly, like this: =INDIRECT("B4:F8") then it works fine. However, if the range in ...
user avatar
  • 352
0 votes
2 answers
23 views

concatenate with ISBLANK and delimiter

I am attempting to combine two columns in Excel 2016, with a space between the results. My problem is being able to use ISBLANK to ignore blanks on either column, and still have the space delimiter in ...
user avatar
0 votes
2 answers
40 views

Excel: Use table from one sheet in a different sheet

I have a table in an Excel sheet; the table and sheet are both called "LTIP_RD_COND_CURRENT_VW". The table's source is a Microsoft Query via an ODBC connection to an external database. I ...
user avatar
  • 105
0 votes
1 answer
63 views

Excel Convert Row to Matrix

Is there an integrated function of Excel 2016 to transform a series of 1x95 lines of data into unique 5x19 matrices? I have a ton of 5x19 matrices that are getting imported into Excel. Unfortunately, ...
user avatar
0 votes
1 answer
46 views

Compass bearings on excel

Would like some help creating an excel formula: Compass bearings are from 0 to 359. I have attached a picture of how it looks like here: Basically, I am looking for a solution for when I input 2 ...
user avatar
0 votes
1 answer
22 views

Time since previous order

I need to go through a big amount of data for the last 2 years in a company and calculated the time since previous orders for Users who have made multiple purchases. Which Excel function can be used ...
user avatar
0 votes
1 answer
23 views

Excel Pivot Charts - Creating different charts (with different grouping) from the same data

I would like to create (at least) two pivot charts sourced from the same data that display the data in different groupings; one will be grouped by years, the other grouped by months. I am able to ...
user avatar
-2 votes
1 answer
23 views

Formula helps to extract value

0 Please need a formula to get the NUMBER after the "SV in the description. In the 1st row, want to extract the 1 and in the 3rd, row want the @1 AND Fourth row want the 10. CONSTENT.1 SN.1 AMMI ...
user avatar
0 votes
1 answer
23 views

Excel SUMPRODUCT with OR and multiple criteria in { }

I would like with sumproduct to sum where: name = {"john","an","hui","james"} OR Number= {"one","two","five","six","...
user avatar
1 vote
1 answer
50 views

Is it possible to display just the exponent value of a 5-digit number in Excel?

I have the following table and I am attempting to format data in a very specific format. In order to use the raw value for it's attended function, I must format the raw value, so there are 5 ...
user avatar
  • 39.3k
0 votes
1 answer
30 views

Keeping two rows together when sorting

I would like to know how to link specific rows when sorting a table in excel. I have already made the table and in each row contains data for a specific person. Each person has two rows. My boss would ...
user avatar
0 votes
1 answer
17 views

Stopping Excel Conditional Formatting rules changing between tabs

So I have set up a worksheet for my job and it is spread out between 3 different excel tabs. I created a number of different Conditional Formatting Rules to set up all the sheets in the same way to ...
user avatar
  • 1
0 votes
1 answer
30 views

Is it possible to filter a numeric column for values starting with a specific number, without using a helper column?

I have an Excel data where one column has numeric values of varying lengths, with sample values like 100, 100001, 11050, 201, 222001, etc. I want to filter values starting with 1. I am able to do it ...
user avatar
0 votes
0 answers
19 views

Populating a column based on a selection from another column in Excel 2016

I want to populate a cell with a list based on the selection of an item in another cell. E.g. whenever I select debit in column2 it will show a list of options in column3. However, if I select credit ...
user avatar
  • 1
0 votes
1 answer
191 views

Prevent Excel Conditional Formatting from fragmenting when deleting row

Using Excel 2016. In the "Applies To" Section, I have =$F:$F because I want it to apply to column F In the 'rule' the formula is =AND($F1=$F2,NOT(ISBLANK($F1))) Intent: Highlight cells, in ...
user avatar
0 votes
1 answer
69 views

Excel VBA How do I set active cell to be only a cell with X data?

Dim NA As String NAFinder = 9 NA = ActiveSheet.Range("F9").Select For i = 0 To NARows NA = ActiveCell.Offset(i, 0).Value If NA = &...
user avatar
0 votes
1 answer
29 views

Excel web queries - how do I add the data I get to a running list rather than just updating the latest number?

I have a web query that pulls data from a website. Each day the website publishes an updated value for an index. And I can pull that value into my spreadsheet. But I'd like to make a running list of ...
user avatar
0 votes
3 answers
254 views

Pivot Table: Expand/collapse all rows using keyboard shortcut

In an Excel 2016 pivot table: Is there a keyboard shortcut we can use to expand or collapse all rows at a given level? For example, CTRL + click the plus sign to expand all rows? I'm aware that it's ...
user avatar
  • 105
0 votes
1 answer
289 views

Pivot Table: Sort row by hidden field

I have a table in Excel 2016 (source: Microsoft Query/ODBC): Raw data: Construction Type Location Sort Reconstruction Main St - Red St to Blue St 1 ...
user avatar
  • 105
1 vote
1 answer
97 views

Excel Formula to display date when cells are filled in

I have a table and I need a excel formula that says the following: If Cells C1, E1 and F1 are filled out, than today's date will appear. However if Cells C1, E1 and F1 are not filled out, than the ...
user avatar
0 votes
4 answers
55 views

How do I get VLOOKUP to return all results not just one (using formula not auto-filter)

I have a table full of several orders and the dates. On another page i want to be able to type in a date and for all orders to show from that date, like a VLOOKUP. But, a VLOOKUP would only display ...
user avatar
  • 1
0 votes
1 answer
22 views

How do I count how many times this category appeared only if the value in the column name is not equals to "N/A"

I have these sample data:https://docs.google.com/spreadsheets/d/1TSpuMRktIRAoACH5s1Dk9189NsNpJwjRtNaR5QROZg8/edit?usp=sharing I wanted to count how many times a certain category has appeared only if ...
user avatar
  • 103
0 votes
3 answers
40 views

Excel, How do you make the the exponent number of e+11 to become e+12

356810115212.947 is display as 3.5681E+11 in Excel. I want it to be display as 0.3568E+12, is there a way to do it?
user avatar
1 vote
1 answer
120 views

In Microsoft Excel, how does one have a default value of a cell that changes when data is entered but revert back to the default if data is deleted?

I have a sign in sheet that has multiple column titles and would like to have a default value in each column. The intent is when data is not entered there is a value there that states what information ...
user avatar
0 votes
1 answer
286 views

Copy Excel Combo Box into Every Cell in Column

I have created a combo box in a worksheet following the instructions in another post. Now I'd like to have this box appear in every cell in a column. The idea is that I can keep a running list of what ...
user avatar
0 votes
0 answers
33 views

Excel cell becomes input or output depending on another cell

I have a situation where depending if cell C5 is BUY or SELL, cell D14 respectively shows the outcome of a formula or I have to input a number. How can simply achieve it hopefully with programming?
user avatar
0 votes
0 answers
117 views

How to prevent crashing my 64bit VBA application when opening a workbook containing 32bit VBA code

I am writing a VBA application to preprocess excel files that I receive periodically from a client. These files contain 32 bit VBA code (which I don't care about), and I am running Excel 64bit. When ...
user avatar
  • 1
0 votes
1 answer
58 views

Excel Pivot display multiple columns

I had a Salary data month wise now i want to calculate the difference between last 2 months salary field wise below is the sample data Required Sample output of single employee. I have tried using ...
user avatar
0 votes
1 answer
41 views

How to split certain random row values in a specified column

I need to find who are our top business clients according to total sales and by products. This is easy while using a pivot table. But the problem is data in excel is not clean. I need to split one ...
user avatar
0 votes
0 answers
54 views

Excel for Web Data Validation List Not Working with FILTER() function

I am using a combination of a data validation list - which contains zip codes - and the =FILTER() function to try and create a drop down list of zip codes for the user to search from. When I remove ...
user avatar
1 vote
1 answer
48 views

How to create an Excel chart of the following data

I'm trying to create an Excel line chart that would dynamically present the following data (this is just a sample, the data set is much bigger and is changing a lot): Name | Salary change date | ...
user avatar
  • 13
0 votes
2 answers
139 views

Excel: How to validate hierarchical data for consistency in hierarchy

I have a hierarchical data in Excel, like this: Level 1 Level 2 Level 3 GL Name Current Liabilities Trade Creditors Foreign Creditors Bob Current Liabilities Trade ...
user avatar
1 vote
1 answer
69 views

Excel: How to arrange similar values right after the 1st consecutive block of rows containing the same value

I have an Excel sheet where values in a column are duplicate and in non-consecutive rows. The values are not sorted in any particular order. I want to bring all rows containing the same value together,...
user avatar
1 vote
0 answers
45 views

Excel Secondary Axis being Stupid with Stacked Bar Charts

I hope you can help with this as it is the first time google has no result for an error I thought it was common. I am following the instructions for a bullet chart, but instead of a column, I would ...
user avatar
1 vote
2 answers
473 views

How do you open/import a CSV file with Unicode characters encoded as numeric character references?

In Excel, I tried to import (using Data > Import) a CSV file with Chinese characters. The characters are represented as Unicode numeric character references (NCR); for example 香&#...
user avatar
  • 113
0 votes
1 answer
43 views

Microsoft Excel data rearrange - split column data in half

Is there any way to rearrange data in Excel like in the image below? I also want to remove duplicate rows and keep unique values.
user avatar
  • 3
0 votes
2 answers
112 views

In Microsoft Excel, how to copy an entire entry to another sheet automatically?

I am currently extracting data for a research, and I want to automatically copy the data to multiple sheets when a condition is fulfilled, as the data keeps being updated over time. I am aware that ...
user avatar
0 votes
1 answer
80 views

Excel - verify that all identical values in a column are in consecutive rows / Highlight or count non-consecutive duplicate values

I need to verify if all duplicate values in an Excel column are in consecutive rows or not. I do not want to change the sort order of Excel. I just need to know whether there are duplicate values ...
user avatar
0 votes
1 answer
25 views

Count all 0 and negative 0 in a row in excel

I need to count the value 0 in this table, including the negative -0 in Excel formula using countif The logic I'm aiming for is, Count all 0s and all negative 0s in this row. Expected Result: 2 Actual ...
user avatar
0 votes
1 answer
27 views

In Excel, get the item from a column that doesn't appear in another column, and the columns are in two different worksheets

I have two columns that are in two different worksheets. I have to create a formula that lists the loan numbers of column A in sheet1 that are not in column A in sheet2. Every loan number that is not ...
user avatar
0 votes
1 answer
26 views

How do I get my Excel Ribbon to work?

I am new to Excel, as this is one of my first times using it, and I am having trouble clicking elements that are on the Excel Ribbon. I would like to create a "What-If Analysis" for the ...
user avatar
0 votes
2 answers
62 views

Excel: Calculate running balance for different group

I would like to calculate a running balance for a different group. For illustration, my table consists of the the following columns and data/information: Acct_Msisdn Txn_Amount created_on Running ...
user avatar

1
2 3 4 5
28