Skip to main content

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
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 ...
Anonymous users's user avatar
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 ...
blackandorangecat's user avatar
-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 ...
MUHAMMAD Ahmad's user avatar
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","...
guest474747's user avatar
-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 ...
Ramhound's user avatar
  • 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 ...
austin yarnell's user avatar
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 ...
Dillon's user avatar
  • 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 ...
AllSolutions's user avatar
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 ...
James Beatty's user avatar
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 = &...
Guilherme's user avatar
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 ...
user348514's user avatar
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 ...
User1974's user avatar
  • 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 ...
User1974's user avatar
  • 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 ...
ExcelQuestion's user avatar
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 ...
Adam's user avatar
  • 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 ...
JS3's user avatar
  • 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?
Limyx826's user avatar
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 ...
Kratocus's user avatar
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 ...
user348514's user avatar
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 ...
jayke's user avatar
  • 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 ...
Nilesh Gajare's user avatar
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 ...
BlueBorna's user avatar
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 ...
user1091735's user avatar
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 | ...
Luk Woz's user avatar
  • 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 ...
AllSolutions's user avatar
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,...
AllSolutions's user avatar
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 ...
Max Will's user avatar
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 香&#...
J K's user avatar
  • 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.
sultan's user avatar
  • 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 ...
amedicalenthusiast's user avatar
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 ...
AllSolutions's user avatar
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 ...
user1735120's user avatar
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 ...
Ghostrider22's user avatar
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 ...
Edmond Helen's user avatar
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 ...
rshardja's user avatar
  • 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 ...
arthemis235's user avatar
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-...
TheStudent's user avatar
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" ("...
Dietmar Höhmann's user avatar
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 "...
Jaime. V. Pages's user avatar
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 ...
cocoakrispies93's user avatar
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 ...
Danil's user avatar
  • 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 ...
user1091735's user avatar
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 ...
Beck's user avatar
  • 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 ...
Stefan Martens's user avatar
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!
Caveman's user avatar
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 ...
Daniele's user avatar
  • 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....
User1974's user avatar
  • 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 ...
A F's user avatar
  • 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 ...
jonatr's user avatar
  • 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 ...
derBjörn's user avatar

1 2
3
4 5
29