Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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
0answers
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
0answers
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
0answers
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
0answers
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
2answers
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
0answers
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
2answers
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
2answers
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
1answer
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
1answer
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
0answers
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
2answers
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
2answers
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
0answers
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
1answer
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
0answers
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
7answers
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
1answer
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
2answers
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
0answers
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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 ...