Microsoft Excel





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I exported data from a Shopify store to a CSV file. The original data are product descriptions from the store inventory.

When you scroll down the worksheet to ROW 10313, some of the product description and other columns are no longer populated. COL A displays the following type of formatting (XML ?) rules. Those rules continue to be displayed to the last row of about 32000. Some of the rows contain the correct product data:

<m:brkBin m:val=""before""/>
<m:mathFont m:val=""Cambria Math""/>
UnhideWhenUsed=""false"" Name=""Colorful Grid Accent 6""/>
<w:LsdException Locked=""false"" Priority=""73"" SemiHidden=""false""

Can you explain what is happening and how to fix it?
PMI ACP® Project Management
LVL 13
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Where could I get an Excel or Access version of the dummy data set - Worldwide Importers? I know I can get it via SQL but I want to get the Excel or Access version?  Here is the SQL link..
My Excel does not recognize LongLong data type needed to run the ActiveX version of Interactive Brokers Trader Workstation Excel dde spreadsheet. It appears that I need to be running a 64 bit version of Microsoft Office. What is the best way to do this and could this cause any problems with existing Office applications that I run.  Thank yo very much!

How to get this Pivot output or formula driven output based on data.
File is attached.Get-Output.xlsx

I want to find highest values' Row and Column in an array in Excel.
The file is attached for the reference.Get-Highest-Value-in-Array.xlsx


I am using the code below on a userform in Excel to run a report stored in access database. It currently prints the report immediately. I am trying to have the report just display without printing. I would prefer that the report opens in excel and the user can then decide to print or discard.

Right now if the user runs the report it locks the access dba until the print job is complete. I need to prevent that from happening since we have multiple writing to the database from the excel userform. Any suggestions or perhaps a better method?

Private Sub CommandButton2_Click()
       Dim objAcc As Object
       Dim strWhere As String
       strWhere = "IDnNo = " & Me.txtBrNo & ""
       Set objAcc = CreateObject("Access.Application")
       objAcc.OpenCurrentDatabase FilePath:=TARGET_DB
       objAcc.DoCmd.OpenReport "rptClasss", acViewPreview, strWhere
       On Error GoTo ErrHandler
       objAcc.RunCommand 340 
       On Error GoTo 0
       Exit Sub
       If Err = 2501 Then 
           ' Ignore
           MsgBox Err.Description, vbExclamation
       End If
       Resume Next

End Sub

Open in new window

Hi Experts

I have a daily report that shows what our warehouse team has put into stock for the previous day - on Sheet 1 is :-

Column A has the date of the transactions
Column B as the hourly bracket
Column C has the actual posting time of the transaction
Column D has the location with which they have mad the transaction
Column E has the quantity that was posted to inventory

On Sheet 2 I have put some labels in the cells such as :
Quantity replenished before 2pm
Quantity replenished after 2pm
Number of locations replenished before 2pm
Number of locations replenished after 2pm

For the above on Sheet 2 Ive tried using the data on Sheet 1 and various Count If / Sum if combinations to get the results for the four summaries above on Sheet 2, however Im having difficulty in using the time as part of the formula

I've also tried to use SUMPRODUCT / FRREQUENCY as Column D has duplicates that need to be counted as 1 fulfilled location and not counted per entry

Is there a way using the time in Column C on Sheet 1 to a) sum the quantities put away before and after 2pm and b) count the number of locations that were used before and after 2pm

Any assistance would be greatly appreciated

1.  I have a macro that I would like to run on multiple files in a folder.  Each file may have between 1 to 400 records in it.  2.  I will need the macro to be able to copy and paste values in Columns G, H, and I (values after macro runs) to the end of the file if the file no matter how many rows it has Again some files only have 1 record, some 10 records, etc.  See macro in the attached file.
Currently the macro removed 2 columns; adds 3 columns, do calculations in the 3 columns (G-I) and remove all columns after column i.
I have a large number of excel files I need to open and run a macro to count columns and count non-blank and blank values on a row.  The first 8 columns are the same and then after ~ the columns per file various.  It could be 1 column to 45 columns.  Also a file could have only 1 record or it could have over 300 records.  I am trying to figure out a way to open all of the files at once apply the macros to remove a few columns and add 3 columns to hold the "total count", "blanks", and "completed" values, along with having  the output from each file combined into one larger file.  Any help would be greatly appreciated.
I have a table that is generated from a data source.  The table is based on a 6-month time frame.  My challenge is how to populate the table if I do not have all 6-months of available.  So if I have 3-months of data, then they those 3-months would be displayed reading left-to-right, with the oldest in the left most column.

I have played with nested IF statements, but with no success.

Attached is a small example of my data with a table showing 4 of the 6 months and one with all 6-months. Table_With_Moving_Data_EE.xlsx
OWASP: Threats Fundamentals
LVL 13
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

I have a report in excel where the total time is written like so:


I was wondering / hoping someone had a formala to confert that data into a straight number of seconds.



Thank you,
Excel calendar from "MicrosoftMonthView" creating double calendar.
I want only one single calendar, not two.
If you maximize then restore the second calendar window disappears, but I don't want to have the use do that everytime when they open.
Is it possible to add a custom button that point to my macro in Excel 2019 ?

Our FE.mdb front end database runs in "user mode" which means: special keys off; navigation off; full menus off; etc
But Joe needs a "tester mode" with special keys ON.

Currently, we accomplish this manually: v
Joe opens FE.mdb then clicks File > Privacy Options > Current Database > Use Access Special Keys > OK
After closing and reopening FE he is in "tester mode".

But he needs to do this every day because our network signon script always recreates c:\front end\fe.mdb from the server's FrontEndMaster folder.

I am planning on modifying the signon script so that Joe's refresh comes from a FrontEndSpecial folder which I must carefully maintain. Is there a cleaner  approach?
Try to set a OFFSET function but it returns an error. Any idea ?

Excel VBA
When I create an invoice for my customers, alot of my customers decide to pay me over time. Those that opt in to pay over time make a monthly payment to me. As I run some reports, I am trying to figure out two things: 1. How many months does it take for each invoice to get paid in full? 2. In how many months is 1/2 the invoice amount paid. I want to know this because when 1/2 the invoice is paid, I offer them other discounts. I attached a sample of my report I export. I added two columns (B and C). I put the answers in those columns that I am hoping you can help me with the formula. So, can you please help me with the formulas in columns B and C? Thank you in advance!!!
I am using this code to put time stamp in cell B5:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.

    With Me
        Set KeyCells = .Range("D5")
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            ' we will put time stamp in cell B5
            Application.EnableEvents = False
            .Cells(5, "B").Value = Now()
            .Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
            Application.EnableEvents = True
        End If

    End With

End Sub

It works ok however I need to concatenate  "Run at: "  to this time stamp.
Any idea.
The variables lRow and lCol will only show the correct information on the first loop, the following loops is a report on the first loop. A similar thing happens if I use LastRow variable.

Public Function ExportToExcel(ReportFilterType)
' Tools > References > Microsoft Excel Object Library

On Error GoTo Error_Message
'Requires Reference to Microsoft Excel Object Library
 Dim rsMainList As DAO.Recordset
 Dim rsExport01 As DAO.Recordset
 Dim rsExport02 As DAO.Recordset
 Dim rsExport03 As DAO.Recordset
 Dim sqlExport01 As String, iCol01
 Dim sqlExport02 As String, iCol02
 Dim sqlExport03 As String, iCol03
 Dim xlObj As Object
 Dim xSheet01 As Object 'Upload File
 Dim xSheet02 As Object 'Batch Summary
 Dim xSheet03 As Object 'Batch Detail
 Dim strFolder As String
 Dim LastRow As Long
 On Error Resume Next
 DoCmd.Hourglass True
 If rsMainList.EOF Then Exit Function
 strFolder = CurrentProject.Path & "\"
 Do Until rsMainList.EOF
    Set xlObj = CreateObject("Excel.Application")

    sqlExport01 = "SELECT Database, [Posting Date], [Account Combination], Co, Div, Fun, Rig, Job, AFE, Maj, Min, [I/C], [Debit Amount], " + _
    "[Credit Amount], [Total Amount],[GL Description], [Upload ID], [Batch ID], [Currency ID] " + _
I have a table with columns A,B,C,D,E,F,G.  Column A-F are categories and are sorted first on A, 2nd on B and so on through column F.  G is a price.

I want to count and total column G for rows that match on all columns A-F.

So for a table that contains columns like Country      State      County      City      Street House      Price(actual column names are different and make little sense to someone outside the company),
how do I count the number of houses on a street and subtotal the price?  The problem is that you could have two cities with the same street number.  These two cities could be adjacent in the sorted rows.  So I can't just count and subtotal when the street changes since sometimes the streets will be the  same while the cities are different.
CompTIA Network+
LVL 13
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I have issue with change event in Excel Worksheet.
Excel 2016.

In my workbook I have several worksheet.
On first one I have two cells that I am using as parameters for my data source query  at the same time when I change value in one of two cells I want to use that  as trigger to pull date and time when this happened and put that on spreadsheet.

Here is code of Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("G4:G5")

    If Not Application.Intersect(KeyCells, Range("G4:G5")) Is Nothing Then

        ' we will put time stamp in cell B5
        Cells(5, "B").Value = Date & " " & Time
        Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
    End If

End Sub

Event is fired only when I first time open workbook and change value in those two cells.
After that event is not fired data source is refreshed and I got new data in my spreadsheet but change event is not triggered for date time display.

What could be issue.
Hello, so I have this Excel table (facsimile):

Initial table
I've been asked to make it so that the table can be printed using the second column as a way to split it into more tables. So, the data should be grouped depending on the second column value.

So this table should be printed on 3 pages, and we'd get these 3 tables, one on each page:

3 tables, grouped by the value in the second column
Does Excel have some quick way to do this which I don't know of? Do you have any suggestions?


I have a workbook that nearly does what I want it to do but I think the formula corrupt.

In sheet called Tableau if data missing from Watford sheet if comes up "NO MATCH"  and when data missing from Tableau sheet but in watford sheet it should also show as "NO MATCH"  but at moment it is showing REF!

Im also trying to set it up so that I get rid of sheet called "new rec" and I dont want any references to this.

Any Ideas?

File attached with formulas
Hello experts,

The following procedure allows me to add string on multiple columns.

Sub Add_Specific_String_Multiple_Columns()
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String
    Dim strToAdd As String
    On Error GoTo Error_Routine

    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")

    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    If IsNumeric(intWhich_temp) Then intWhich = intWhich_temp

    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
        & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    strToAdd = InputBox("Input the value that you want to add.", "String To Add!")
    If strToAdd = "" Then
        MsgBox "No input!", vbExclamation
        Exit Sub
    End If
    For Each strCol In Split(strColList, ";")

Open in new window

When I use the following equation it works great

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(2018,11,20)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(2018,11,21)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

But if I try to use this equation it breaks:

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(c5)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(h5)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

I am TRYING to refer to another cell to grab the date.  What am I doing wrong??
Is there a way to copy formulas from one workbook to another WITHOUT copying the file reference and if so how? Thank you

Microsoft Excel





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.