# 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.

**0**

votes

**0**answers

12 views

### Fixing Excel timesheet by time in and time out per user and date

I have this badly formatted table which is difficult to read, 'I' means time in and 'O' means time out.
which I want to transform it like this
I want to align each time in and time out to its ...

**0**

votes

**0**answers

9 views

### Can't get dynamic print area working

I have just finished building a report which will have a variable number of entries and would like the print area to adjust dynamically. The print area needs to run from B2 across to AD2, and down to ...

**0**

votes

**0**answers

14 views

### How to add a list of values to a named range manually?

How to add a list of values to a named range manually? I do not want to map it to cells.
For e.g., = {"Apple"; "Orange"}
I am looking for something similar to c() in R which saves set of values to ...

**0**

votes

**0**answers

15 views

### How do I Extract file links from Dropbox in bulk?

So I have hundreds or rarely around a thousand nage files on dropbox. I need links to each one of them in an Excel workbook.
I've seen the technique by using folder links, you can get bulk images... ...

**0**

votes

**2**answers

33 views

### How do I append to multiple cells in Excel?

For example:
If the cell's value is "ABC", I want to append "D" to it.
Suppose I have a huge list and this cell with this exact value is random throughout the list. How do I reliably append to this ...

**0**

votes

**1**answer

24 views

### How do I search with Excel for cells with specific characters at the end of the string?

I need to know how to use pattern matching in excel.
Suppose I have 3 cells...
ABC
ABD
JGFC
I need to highlight those with C AT THE END.
This is a small example. I need to do similar stuff for ...

**0**

votes

**2**answers

33 views

### Double entry with same info but in two columns in MS Excel

I have a big database where the data is like this:
Now, I just want to get the info for unique values (for me Steve-Kevin is the same as Kevin-Steve, I can just take any one of them). How can I sort ...

**0**

votes

**1**answer

20 views

### Match 2 dates in columns return 3rd

I have a column of dates (A3:A89)
I have a column of text (B3:B89)
I have another column of dates, although the cells are formatted to show only the date (O6:O43)
I need to find a date that matches ...

**0**

votes

**0**answers

15 views

### Power Query with Additional Column

I am using a power query to read rows indexes by an item number from one file I receive to one file I am maintaining. I need to add some columns that are editable by me (cell editing), in a way that ...

**1**

vote

**1**answer

25 views

### Excel 2016 Chart showing random dates in x axis

I'm simply trying to graph some time-scale data using a date column and 3 data columns, but Excel keeps screwing up the dates in the chart... (showing dates from year 1900-1903 instead of 2018)
All ...

**0**

votes

**0**answers

36 views

### How to replace a column of numbers with a numbered column?

I got our new timetable for the new course, the thing is that it's actually two excel spreadsheets; one with the number of the lecture and the time, the other is the title of the lecture. So if want ...

**0**

votes

**0**answers

35 views

### Excel Athletics Data

I've been trying to do a report which summarizes all my athletics records in a way that shows my season bests and personal bests.
Questions:
How can I select data and filter it in a way that selects ...

**0**

votes

**1**answer

14 views

### Excel - SUM and COUNTIFS

I need to do a SUM in a row where there are some cells with "AL" and some cells with "HD".
The formula I have is
=SUM(COUNTIFS(B4:AF4,{"AL","HD"}))
but it assign 1 to both options.
I would like to ...

**0**

votes

**0**answers

13 views

### Unable to view macros or compile VBA project in Excel

I am unable to open a workbook that has macros on a particular computer. It will open on any other workstation. I have tried launching Excel in Safe mode and disabling macros with message and nothing ...

**0**

votes

**0**answers

18 views

### Prevent Excel from converting a number code to a date in the existing csv data set

I downloaded an existing data set csv file which has unique id code for every row like "11-2011" which excel would convert to "Nov-11", which corrupts my data.
I can re-download the csv file from the ...

**0**

votes

**0**answers

13 views

### How to make a scatter plot when x and y are on different matrix

I have a matrix "X" with x coordinates, and a matrix "Y", with Y coordinates
both matrix are 900x900 (RowxColumn count)
I need to plot a scatter plot X vs Y on a single series (so I can add ...

**0**

votes

**1**answer

27 views

### PowerQuery ODBC connection revokes password: due to auto-refresh?

I have a nice PowerQuery query that connects with ODBC to a database. Our passwords change every month. When I enter GetAndTransform to edit the query, that time of the month - even if I don't ...

**0**

votes

**0**answers

27 views

### How do I drill down by value in a pivot table?

I have a pivot table that sums a boolean column (so 1 = yes, 0 = no). I want to be able to double-click in a cell and get just those rows that have a value of 1 for the column. Instead, when I double-...

**2**

votes

**1**answer

43 views

### Excel requires dates to be typed in American format

In order to type a literal date into an Excel cell, I currently have to do it in the American format (mm/dd/yyyy). This is unacceptable.
How can I have it such that Excel will correctly recognise ...

**0**

votes

**0**answers

24 views

### Text to columns with multi line text

I've got some sharepoint data I've exported to excel. There's a specific column for annual spend that is causing me a headache. The cells in this column contain annual spend data for multiple years, ...

**0**

votes

**0**answers

22 views

### my if function (excel) not working with true/false - using Office 365 - windows

Why isn't this working?
E2: =IF(D2=TRUE,B2,"")
D2: =B2=C2
No matter whether D2 is true or false, I get a blank in E2.
I've tried changing formats. Does it have to do with the fact that D2 is a ...

**1**

vote

**0**answers

26 views

### Excel 2016 waterfall chart losing data and formatting

I have a problem with a report I built and released into the wild.
It contains a waterfall chart with an offset within it linked to a spinner button to change the displayed data range on the fly. ...

**0**

votes

**1**answer

38 views

### How to compare group of columns to another group of columns in excel and highlight matches?

I have two groups of data. Each group is 3 columns. Group 1 is 1500 cells long. Group 2 is 500 cells long.
I need to see if there are any matches from group 2 in group 1 and highlight them.
I know ...

**0**

votes

**1**answer

17 views

### Excel: COUNTIFS or Nested IF counting sequential columns

this is my first time posting here so please be a little patient with me. I'm trying to write a formula that will tracked missed scan events (for a package tracking database).
I have several date/...

**0**

votes

**1**answer

54 views

### Excel 2016: Query from folder not combining csv's by header row

I'm trying to combine multiple csv's in Excel 2016 using the New Query->From File->From Folder. I have done this in the past without any issues so I'm not sure what I'm doing wrong this time.
...

**1**

vote

**2**answers

79 views

### Conditional formatting based on date difference

I need help with making the row or at least the cell turn red when it has been >20 days since the date that was entered in the cell.
For instance: I issue an item to John on 10/01/2018 and so I place ...

**0**

votes

**0**answers

57 views

### True/False for two arrays having 1+ values that are the same

Edit note: The problem has changed slightly since first posting; see edit
So I'm looking to check two arrays (of strings or integers/floats; although it'll be the same for both arrays) and get a True ...

**0**

votes

**1**answer

7 views

### PowerPivot returning all values

I am trying to learn with PowerPivot and from what I saw, it would be THE tool to help me reconcile similar data in two different systems. However I cannot seem to get through the basics - I have ...

**0**

votes

**0**answers

21 views

### Setup connection to folder instead of individual workbook

I have a master dashboard pulling from several Excel documents and have setup connections between them so the user can just open the dashboard and everything is updated. The one area I am not ...

**1**

vote

**2**answers

130 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 ...

**1**

vote

**2**answers

62 views

### How to set exact chart area size in excel 2016?

I know how to set the size of the diagram, but I cannot find the option for setting the size of the chart area (not sure if this is the correct name in English). The box I want to change is marked ...

**1**

vote

**1**answer

32 views

### Copy data to a new table and auto update dynamically in Excel

I have a table with some data that is similar to the table below:
I would like to display a table on the right of this table to show each colour product in its own column:
The worksheet would ...

**0**

votes

**1**answer

34 views

### Simple IF() Formula No Longer Works

Has Microsoft pushed out a crummy update?
=IF(A1="",B1,A1)
no longer works. I get the pop-up:
here is a problem with this formula.
Not trying to type a formula?
When the first character is an ...

**0**

votes

**0**answers

57 views

### Excel re-apply filter when data changes in another sheet

I have a workbook with 2 sheets. The first sheet includes a daily work log and a note on whether or not each item is complete. I call this the "Log" sheet. E.g.:
Then I have another sheet, which I ...

**0**

votes

**2**answers

17 views

### Why is my match formula giving inconsistent output?

I am using below formula to get the last value of each individual items in rows in a pivot table.
=HLOOKUP("Last Updated",$D$4:$D$293,MATCH(A5,$A$5:$A$293)+1)
The intention of using the match ...

**0**

votes

**2**answers

34 views

### How can I format date_space_hour to a time format

I get this text data in an excel worksheet
16/10/2018 00:11:53
16/10/2018 00:41:52
16/10/2018 04:21:19
16/10/2018 04:30:37
it is "day/month/year space hour:minute:second"
I tried formatting as date ...

**0**

votes

**0**answers

29 views

### Excel 2016 autorecover fails to work. How to get back the new file?

Excel 2016 autorecover hasn't worked for days. It does not autosave any file and we hasn't noticed until the program hung. How can we get back the new file? Is it still possible?
We were working on a ...

**1**

vote

**1**answer

36 views

### Find text in the textbox in Excel

I've come to realize the Excel "Find and Replace" dialog couldn't find text in the textboxes.
I think I need a macro to solve this.
How could I find text in the textboxes:
in the active sheet
in all ...

**0**

votes

**0**answers

25 views

### How to export Excel worksheet to another format without changing the original format

I do frequently export Excel worksheets to another format (for example, HTML or CSV), and always get annoyed by Excel changing the original format I was using (XLS or XLSM) to the one used for ...

**28**

votes

**7**answers

9k views

### In Excel, when I enter 22222.09482 then I see 22222.0948199999 number in the formula bar

Could you please help - as I have a weird situation that when I enter a number 22222.09482 in cell then I see a different number 22222.0948199999 in the formula bar. Below is the snapshot of the ...

**0**

votes

**1**answer

29 views

### Is there a way to access the built-in excel form via a shortcut?

Is there a way to access the built-in excel form via a shortcut? Macros/VBA and addins are disabled. Users are running either Excel 2013 or Excel 2016.
Search has yielded only custom toolbar ...

**0**

votes

**2**answers

42 views

### Sum row/cell values when multiple column conditions are fulfilled

I'm want to sum every row/cell range in a table where one of (multiple) conditions is fulfilled in one of the columns.
An example:
I want to find the sum of all cells where the two (or more) ...

**1**

vote

**0**answers

27 views

### How to switch which sheet another Excel sheet is looking at?

Excel (2016). Suppose I have some piece of Excel in AnalysisSheet that analyzes data in some other data sheet. And suppose the following:
There are actually several such data sheets. For example, ...

**0**

votes

**1**answer

14 views

### Find specific cells in column N, change value in column O

I am new to VBA coding and am trying to search for a name in column N and replace the value in column O.
I have tried to modify this code but I cant get it to replace the cell next to it.
Columns("N"...

**0**

votes

**2**answers

68 views

### If formula contains specific text/symbol/function

How can I detect if certain cells contains specific formula with specific text/symbols?
1. A formula which is =today(), or =date(), or =time()
2. A formula which starts with =(any alphabet letter)
3. ...

**0**

votes

**1**answer

15 views

### Is it possible to link 2 different cells to a ComboBox in Excel?

I have a ComboBox that's linked to a cell, R2. It outputs whatever value is entered into it in R2, but what I'd like (if even possible) would be to have the ComboBox output the value to two different ...

**0**

votes

**0**answers

8 views

### Excel formulas referencing cells within a range of data do not update when that data is sorted or filtered

After an hour of searching Google, the closest similar problem I could find to the issue I'm experiencing is the following linked post on this site. However, a viable solution for my application was ...

**1**

vote

**1**answer

62 views

### Excel 2016: 3D column chart with transparency and no gap

I'm trying to make a decent-looking chart using a 3d representation of three different datasets (3 years, 12 monthly values).
If I try with an area chart, I have peaks/valleys at the single data-...

**0**

votes

**1**answer

51 views

### How to format big numbers to show million separator in Excel?

Given the following number 11452610,20 (11452610.20), I would like to format it like that: 11.452.610,20 (11,452,610.20)
I already tried to check "Use Thousands separator" but it only shows as 11452....

**-1**

votes

**0**answers

6 views

### return value (date) from most recent closest value

I am tracking daily balances from two accounts. Each row has a date in Column B. Totals for account balance is in Column O. I would like to return the corresponding date value in Column P from Column ...