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.
1,437
questions
0
votes
1
answer
26
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 ...
0
votes
1
answer
3k
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 ...
-2
votes
1
answer
68
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 ...
0
votes
1
answer
186
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","...
-3
votes
2
answers
219
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 ...
- 43k
0
votes
1
answer
471
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 ...
0
votes
1
answer
122
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 ...
- 1
0
votes
1
answer
5k
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 ...
- 376
0
votes
1
answer
3k
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 ...
- 15
0
votes
1
answer
435
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 = &...
0
votes
1
answer
87
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 ...
- 339
0
votes
3
answers
11k
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 ...
- 157
1
vote
1
answer
5k
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
...
- 157
1
vote
1
answer
2k
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 ...
0
votes
4
answers
906
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 ...
- 1
0
votes
1
answer
25
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 ...
- 103
0
votes
3
answers
403
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?
- 53
1
vote
1
answer
2k
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 ...
- 11
0
votes
1
answer
3k
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 ...
- 339
0
votes
0
answers
764
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 ...
- 1
0
votes
1
answer
198
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 ...
- 103
0
votes
1
answer
121
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 ...
0
votes
0
answers
327
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 ...
- 13
1
vote
1
answer
151
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 | ...
- 13
0
votes
2
answers
940
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 ...
- 376
1
vote
1
answer
601
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,...
- 376
1
vote
0
answers
742
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 ...
- 29
1
vote
2
answers
3k
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 香&#...
- 113
0
votes
1
answer
98
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.
- 3
0
votes
2
answers
2k
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 ...
0
votes
1
answer
583
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 ...
- 376
0
votes
1
answer
67
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 ...
- 123
0
votes
1
answer
95
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 ...
0
votes
1
answer
72
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 ...
0
votes
2
answers
955
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 ...
- 101
1
vote
0
answers
273
views
Use Same Scale for Axis and Bubble Sizes in Bubble Size Chart Excel
I've been dwelling on the Internet for a long time before understanding how Bubble Charts work in Excel. Now it seems logical to me that the Bubbles have their own scale to represent data in a way ...
- 21
0
votes
2
answers
221
views
Index Function with error "You've entered too many arguments for this function"
=index(_70Rain,1+int((row(b4)-1/columns(_70Rain)),mod(row(b4)-1+columns(_70Rain),columns(_70Rain))+1)
I just followed the code given in https://www.extendoffice.com/documents/excel/1172-excel-...
0
votes
0
answers
186
views
Data connection to webservice works after being edited but fails after closing and reopening Excel
I've created an Excel workbook that retrieves data from a REST webservice. Works perfectly on my machine. But if the end user opens the sheet and clicks "Daten/Alle aktualisieren" ("...
- 101
0
votes
0
answers
233
views
Excel Files Opn On 1 Click - Windows 10
word files opening with only one click
I have come across this same issue, but its with Excel, not Word. By simply selecting the file it opens up. The 'Click items as follows' option is set to "...
0
votes
1
answer
483
views
In Excel my formula is skipping the first two cells of a table
Hi I have an Excel Spreadsheet and I'm trying to convert a table with multiple rows and columns into just one column for sorting and filtering. But the issue is that it doesn't start at cell A2 where ...
0
votes
1
answer
672
views
Join two tables by 2 keys
I am using Microsoft Excel 2016. And I have 2 tables on different sheets.
sheet1.tbl1 {
key1,
key2,
val1,
fun1,
fun2
}
sheet2.tbl2 {
key1,
key2,
val1,
val2
}
I want to bring values to sheet1.tbl1 ...
- 11
0
votes
0
answers
254
views
Can Excel only display the results from a FILTER, ISNUMBER, SEARCH formula and not the entire array of searched data?
I am creating a spreadsheet that can search an entire "Control" data table using two search boxes.
Search TextBox1 searches by UPI
Search TextBox2 searches by Zip
The two search boxes are ...
- 13
0
votes
1
answer
133
views
Excel 2016 Stopping automatic formatting of timestamps
I'm entering a list of timestamps from a video into Excel 2016. However, when I reach numbers above 24, Excel reacts like it's a 24 hour clock and changes my number. For example, if my timestamp is at ...
- 101
1
vote
1
answer
5k
views
Open UTF-8 csv files by double-clicking in Excel
I want to open UTF-8 coded csv- or text-files directly in Excel 2016 or later.
Of course I am aware of the import option, but I want to do this with a simple double-click.
Problem is that this way ...
0
votes
2
answers
847
views
Excel Custom Ribbon Separators
I was looking for a way to insert vertical separators into a custom ribbon in Excel 2010, which seems to be problematic for everyone. I've searched the planet and been told it can't be done!
- 1
1
vote
1
answer
2k
views
Is there a keyboard shortcut to put the focus on the name box in Excel 2016?
I wonder if I can jump to the name box (the one above the column "A", and below the ribbons) via a keyboard shortcut.
(somewhere I've read about Alt+F3, but it doesn't work)
Thanks in ...
- 111
6
votes
3
answers
2k
views
Given the line between points 1 and 2, what is the change in angle to point 3?
I have polyline vertices in an Excel table.
ASSET_ID VERTEX_NUM X Y ANGLE_CHANGE
---------- ---------- ---------- ---------- ------------
10 1 118.56 3....
- 157
-4
votes
1
answer
1k
views
I need a formula that looks for the first two numbers in a cell and add 0 in front of it [closed]
hey,
Im looking for a formula that looks for the first two numbers in a cell, and if it starts with 13 or 14 and its length is 9 digits, it adds 0 in front of the number. i hope its possible.
for ...
- 1
0
votes
1
answer
312
views
Excel 2016 -calculating average,median,max,mean based on category in a different column
Hi, I want to calculate average,median,max,mean for every single 'type' in the table by itself. How do I do it ? It seemed to me like a very simple task and yet I'm struggling with it...
Thanks in ...
- 147
0
votes
1
answer
31
views
Merge Row Data in Excel with Text-Values
I've got an Sheet looking like this:
Name
Fact 1
Fact 2
Fact 3
Fact 4
Texas
x
Texas
x
Texas
x
Texas
X
Texas
Ohio
x
Ohio
x
I want to merge those rows to just this:
Name
Fact 1
Fact 2
Fact ...
- 11