วีดีโอ

Hello @KHEMARAMKUMAWAT9181, Thanks for the enthusiasm! 😊 Let me know if you have any questions or need help! Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @suryadavid2448, To automatically update the effective day for each user based on production, you can use a formula that checks the production value. If it's greater than 0, increment the effective day; otherwise, keep it the same. You can use the IF function in combination with a helper column to achieve this. Assuming the production data is in column B and the effective day starts in column C, you can use this formula in cell C2 and drag it down. =IF(B2 > 0, C1 + 1, C1) This formula checks if the production (B2) is greater than 0. If true, it adds 1 to the previous effective day (C1); otherwise, it keeps the same effective day. Adjust the cell references as needed for your data. Regards ExcelDemy

@@exceldemy2006 understood, but lets say for 0ct 01 2024, there were 10 users but in which 9 did production, So for Oct 1 9 users effective day will be 1 and non productive user's effective day will be -. Respectively for Oct 2 I will add the same list of users in the sheet. If the user mentioned in row 1 is added again in row 11 on the next day, will the formula automatically detect ?

Hello @suryadavid2448, To track effective days across multiple dates, even when the same user appears on different rows, use an approach with SUMIFS or COUNTIFS to check for previous instances of production. For example, assuming "User" is in Column A, "Production" in Column B, and "Date" in Column C, use this formula in Column D (Effective Day): =IF(B2>0, COUNTIFS(A$2:A2, A2, B$2:B2, ">0"), "-") This formula counts only rows with matching users and production greater than 0, updating the effective day sequentially for each user across different rows and dates. Regards ExcelDemy

@@exceldemy2006 You are really awesome 😊 I appreciate bro

Hello @suryadavid2448, You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy! Regards Shamima Sultana | ExcelDemy

Hello @WambulanceSookilala, It sounds like the formula may not have been applied correctly across all relevant cells. Double-check if the formula has been copied to the rest of the range, as Excel might reference only the first cell without adjusting for each row. Try using absolute and relative cell references as needed. Let me know if this helps! Please follow the step-by-step procedure of the video or the given article. Regards ExcelDemy

Hello @lopsidedxi4162, Yes, you can copy the code provided in the article if it's open for use. If you encounter a bug, double-check that all cell references and ranges match your dataset. Small adjustments like verifying cell formats or debugging line-by-line can often resolve issues. Regards ExcelDemy

Hello @ShawneePolchisLanteigne, You are most welcome. To track Sick Time and Personal Time hourly, modify your leave tracker to include hours for specific categories. Here’s how: 1. Insert new columns, “Sick Hours” and “Personal Hours,” next to your leave days columns. 2. Enter the number of hours (e.g., 1 for a 1-hour sick leave). 3. Use a formula to sum these hours for each category, like =SUM(Sick Hours Range) and =SUM(Personal Hours Range). 4. Adjust your main formula to add hours from these new columns into your overall total, maintaining accurate records for partial-day leave tracking. This setup tracks partial hours while keeping all leave data in a single table. Regards ExcelDemy

@@exceldemy2006 amazing! Thank you so much!

Hello @ShawneePolchisLanteigne, You are most welcome. Keep learning Excel with ExcelDemy. Regards ExcelDemy

Hello @amaliakhaerunnisa7253, The list box may not be interactive due to Excel's control properties or settings. Try selecting the list box, then go to the “Developer” tab, click on “Properties,” and ensure “Enabled” is set to “True". This should allow clicking and interacting with the list box. If issues persist, saving and reopening the file can sometimes reset control functions. Regards ExcelDemy

@@exceldemy2006 Hi, It seems the list box were not interactive because I freeze some rows. after I unfreeze them the list box work out just fine. Thank you!

Hello @snoon8341, In a bell curve, "Score" represents original data points, while "New Values" are normalized values calculated to create a smooth curve, often using the mean and standard deviation. This transformation helps visually represent the normal distribution, highlighting data tendencies around the mean. Regards ExcelDemy

Hello @smiffos, To achieve multiple selections from a drop-down list in Excel without reopening it, you can use the following VBA code. This code allows you to select items directly in a cell with validation. 1. Right-click on the sheet tab and select View Code. 2. Paste the following code into the code window. Private Sub Worksheet_Change(ByVal Target As Range) Dim OldValue As String Dim NewValue As String On Error GoTo Exits If Target.Validation.Type = 3 Then Application.EnableEvents = False If Target.Value = "" Then Target.Value = "" Else OldValue = Target.Value NewValue = Target.Value If InStr(1, OldValue, NewValue) = 0 Then Target.Value = OldValue & ", " & NewValue Else Target.Value = OldValue End If End If End If Exits: Application.EnableEvents = True End Sub Close the VBA editor and return to Excel. This code enables users to select multiple items from a drop-down list without reopening it, as the selections are appended to the cell's existing value. Regards ExcelDemy

Hello @emilylynn7621, If you want to input 0.5 as half-day leave. You can use the following formula for Half Day (HD) column: =SUMPRODUCT(-($D9:$AH9=”HD”)*0.5) It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5. You will need to format the cells with 2 decimal places to properly display the decimal number; otherwise, it will show you a rounded 1. Regards ExcelDemy

Hello @kassayemulisa9374, Thanks for your feedback! We'll work on improving the visibility. In the meantime, you can try adjusting the video resolution by clicking the settings gear and selecting a higher quality option. You also can explore our article for visibility: www.exceldemy.com/make-salary-sheet-in-excel-with-formula/ Let us know if that helps! Regards ExcelDemy

Hello @healthcarebizbrokers, Thanks for your feedback and appreciation. It means a lot to us. Glad to hear that the first method is on point for your needs. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @saeedalhindaassi3855, Thanks for your feedback and appreciation. It means a lot to us. Glad to hear that it is helpful to you. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @davidhardisty5781 Thank you so much for your feedback! We truly appreciate your insights, especially coming from someone with teaching experience. We will work on slowing down the pace to give viewers more time to follow along and absorb the material. Your advice is invaluable, thanks again for taking the time to share your thoughts! Regards ExcelDemy

Hello @mdmamun-lz3qt, To sum cells with text and numbers in Android Excel, you can use formulas similar to desktop Excel, like SUM, SUBSTITUTE, VALUE, or LEFT to extract and sum numbers. However, the mobile interface is limited, so navigating formulas might be more challenging. You can enter the formulas manually, but there's no direct feature to automate this process specifically for mixed text and numbers in Android Excel. Or you can copy-paste the formula. Regards ExcelDemy

Hello @dmccalldds, It seems like Excel is treating your column labels as data instead of using them as headers. To fix this, ensure that when selecting the data for your chart, you're including the correct range and specifying that the first row contains headers. Also, check if Excel is automatically assigning row numbers as X-axis values, which can be adjusted manually by selecting the appropriate axis labels. Regards ExcelDemy

@@exceldemy2006 I just tried following the video and it seems to work now. Thank you for your response. I did fix my problem with another table by going to: {funnel} | Select Data... | Edit (Horizontal, on the right half), and selecting my Axis Label Range (A2 thru A5) _with_the_mouse_, not typing in "A2:A5". (in case anyone else is having this problem)

Hello @dmccalldds, You are most welcome. Glad to hear that your problem is solved now. Thanks for sharing the solution. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @DarrelMally, Yes, it's possible to create new tabs while maintaining focus on the active sheet by modifying the VBA code. You can add a line at the end of your code to select the original active sheet after the new tabs are created. This way, the focus remains on the active sheet even after the new tabs are generated. Sub CreateTabs() Dim ws As Worksheet Dim activeSheet As Worksheet Set activeSheet = ActiveSheet 'Store the active sheet 'Your code to create new tabs Sheets.Add.Name = "NewTab1" Sheets.Add.Name = "NewTab2" 'Add more tabs as needed activeSheet.Activate 'Return focus to the original sheet End Sub Regards ExcelDemy

Hello @mostainbillah, You are most welcome and thank you for your kind words! We will definitely consider creating a tutorial on this. Meanwhile, a VBA solution would work well for your needs. Here’s a VBA solution to search through a closed workbook, retrieve matching rows from all sheets, and paste them into the active workbook without case sensitivity: Sub SearchInClosedWorkbook() Dim wbPath As String Dim wb As Workbook, ws As Worksheet Dim searchValue As String Dim cell As Range, lastRow As Long, foundCell As Range Dim openWB As Workbook wbPath = "C:\Path\To\Your\MasterWorkbook.xlsx" ' Update with your path searchValue = InputBox("Enter search value:") Set openWB = ActiveWorkbook ' Set reference to your active workbook Set wb = Workbooks.Open(wbPath) ' Open the closed workbook For Each ws In wb.Sheets Set foundCell = ws.Cells.Find(What:=searchValue, LookAt:=xlPart, MatchCase:=False) If Not foundCell Is Nothing Then lastRow = openWB.Sheets(1).Cells(openWB.Sheets(1).Rows.Count, 1).End(xlUp).Row + 1 ws.Rows(foundCell.Row).Copy Destination:=openWB.Sheets(1).Cells(lastRow, 1) End If Next ws wb.Close False ' Close the master workbook End Sub This code prompts for a search term, opens a closed workbook, finds the value across all sheets (ignoring case sensitivity), and copies the entire row to the last row of the active workbook. Simply update the workbook path, and it should work perfectly for your needs! Regards ExcelDemy

@@exceldemy2006 Error Massage Found : ( Microsoft Visual Basic Run-time error '1004': Copy method of Range class failed ) Please See The Code ,I am Waiting For Your Reply

Hello @mostainbillah, The error you're encountering, “Copy method of Range class failed,” occurs when Excel can't perform the copy operation for some reason. This can happen if the workbook or sheet is protected, or if the reference to the range is incorrect. Try modifying the code by explicitly activating the destination sheet before copying. openWB.Sheets(1).Activate ws.Rows(foundCell.Row).Copy Destination:=openWB.Sheets(1).Cells(lastRow, 1) This ensures that the destination workbook is active, which should resolve the error. Regards ExcelDemy

@@exceldemy2006 Sorry , your code nothing work (ws.Rows(foundCell.Row).Copy Destination:=openWB.Sheets(1).Cells(lastRow, 1) IS HILIGHTED ,& “Copy method of Range class failed,”

Hello @mostainbillah, The error may be due to referencing a closed workbook or an invalid range. Try using a more robust method to copy the data: Sub SearchInClosedWorkbook() Dim wbPath As String Dim wb As Workbook, ws As Worksheet Dim searchValue As String Dim cell As Range, lastRow As Long, foundCell As Range Dim openWB As Workbook wbPath = "C:\Path\To\Your\MasterWorkbook.xlsx" ' Update with your path searchValue = InputBox("Enter search value:") Set openWB = ActiveWorkbook ' Set reference to your active workbook Set wb = Workbooks.Open(wbPath) ' Open the closed workbook For Each ws In wb.Sheets Set foundCell = ws.Cells.Find(What:=searchValue, LookAt:=xlPart, MatchCase:=False) If Not foundCell Is Nothing Then lastRow = openWB.Sheets(1).Cells(openWB.Sheets(1).Rows.Count, 1).End(xlUp).Row + 1 ws.Rows(foundCell.Row).EntireRow.Copy openWB.Sheets(1).Cells(lastRow, 1).PasteSpecial xlPasteAll End If Next ws Application.CutCopyMode = False wb.Close False ' Close the master workbook End Sub Changes made: I separated the Copy and Paste operations using PasteSpecial. Explicitly used.EntireRow.Copy to ensure the full row is copied. Added Application.CutCopyMode = False to clean up the clipboard after the paste. Please adjust the code based on your workbook and range otherwise it won't work properly. Regards ExcelDemy

Hello @Musicinmyheadnow, You are most welcome. Glad to hear that you found our tutorial helpful. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @lucatintor4896, Thanks for your appreciation. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @AhsanShakil, Removing data from Sheet 1 won't automatically update Sheet 2 unless specific connections are established. The article explains that using formulas can link data, but if you delete entries, the linked cells might return errors. Alternatively, using Power Query allows for dynamic updates, ensuring data stays current even with changes. Excel tables also help maintain structure and connectivity. Regards ExcelDemy

Hello @annak1371, You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @Adriaan.J.L, Thanks for watching our video. Glad to hear that the single criteria method worked perfectly. To see any particular value only once. You can combine the two non-adjacent columns into a single array before applying the UNIQUE function. This can be done using array functions like UNIQUE combined with VSTACK (if available in your Excel version) or curly braces to combine the two columns. =UNIQUE(VSTACK(A2:A10, C2:C10)) This formula stacks the two columns and extracts unique values, ensuring that any duplicate values between the columns only appear once. Regards ExcelDemy

@@exceldemy2006 cool. Thank you

Hello @Adriaan.J.L, You are most welcome. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @sgeorge1642, You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @samuelchartier812 , You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @VikasVk-i9n, The choice of 65 instead of 62 for the bell curve's x-values likely relates to its representation in the context of the curve. Often, specific values correspond to key statistical thresholds or points of interest that illustrate data distribution more effectively. Regards ExcelDemy

@@exceldemy2006 If I directly apply Norm.dist on score (column : C) and then plot score vs Normal values then it’s not bell curve any reason?

Hello @VikasVk-i9n, The reason why applying NORM.DIST directly to the score values (in column C) doesn't create a bell curve is likely due to how the normal distribution is plotted. For a bell curve, you typically need to generate a range of x-values (e.g., a sequence of scores) and then apply the NORM.DIST function to these x-values, rather than using it directly to the raw scores. The bell curve represents the probability distribution, not the actual scores themselves. Regards ExcelDemy

@@exceldemy2006 Thanks alot for clarification! Understood!

Hello @VikasVk-i9n, You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy! Regards ExcelDemy

You are most welcome.

Hello @annissaqzulkifle1446, The -3 in the first bin is typically used to establish the starting point for the histogram, ensuring it captures values that fall below the mean in a skewed distribution. Using -3 is common because it captures a significant portion of the distribution, extending three standard deviations below the mean, which encompasses about 99.7% of the data in a normal distribution. This helps account for outliers and ensures the histogram accurately represents the skewness of the data. Regards ExcelDemy

Hello @palomavano4705, You are most welcome. Glad it is helpful and the information is clear to create a table in Excel. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @palomavano4705, You are most welcome. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @Gwyned, To segregate numbers in Excel from lowest to highest without adding new values, you can use the SORT function. If you want to separate numbers from a mix of text and numbers, you might first need to extract them using formulas like TEXTJOIN or by using the FILTER function. Sort Numbers: Select the range of extracted numbers, then go to the Data tab >> click on Sort Ascending. Alternatively, use the SORT function in a new cell, like =SORT(A1:A10) Regards ExcelDemy

​@@exceldemy2006 what I need also to segregate numbers ex 14566 and in other column name of item I purchased ex mask, and other 13455 boots, I want both columns segregate with correct number- it's for work purchase order

Basically of its 14566 mask and other is 13355 boots - and 13355 move up I want boots from other column move it and be in correct row always

Hello @Gwyned, It seems like you want to create a sorted list of purchase orders in Excel, where the numbers are sorted in ascending order alongside their corresponding items. 1. Ensure your data is structured with numbers in one column and item names in the adjacent column. 2. If your data is in cell A1(including headers), you can use the SORT function in another range. In a new cell, enter: =SORT(A2:B4, 1, TRUE) This formula sorts the range A2 based on the first column (the numbers) in ascending order. Regards ExcelDemy

@exceldemy2006 but if it sort the numbers in one column, in other column with names will not follow numbers (order numbers it should ) will it ?

Hello @lakhscreations9881, Thanks for the feedback! I'm glad you found the information useful. For more tips and in-depth tutorials on Excel, feel free to follow ExcelDemy. Regards ExcelDemy

Hello @AzhanBinHassan, The 6 BIN values are selected to divide the data into intervals that help create a clearer distribution for the skewed bell curve. These bins allow us to group data points and plot frequencies across the curve, showing the skewness. The number of BINs can vary depending on the data size and distribution. More or fewer bins can adjust the smoothness and detail of the curve. Regards ExcelDemy

Hello @immigreat54, Thank you for your feedback! The TRUNC and ROUNDDOWN functions are designed to provide values without rounding, which can result in the output you mentioned. For instance, a value like 45.785 would truncate to 45.78. If you're seeing different results with an 8-digit number, it might be worth double-checking the original value and the applied formulas. To get your desired result you can use the ROUND function =ROUND(A1, 2) Regards ExcelDemy

@@exceldemy2006 yes, I used the ROUND function to get the desired result. Thanks

Hello @immigreat54, You're most welcome! Glad the ROUND function helped you achieve the desired result. We really appreciate your feedback. Regards ExcelDemy

Hello @mariemartin8756, You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @Kevin-g3g1v, You can drag and fill numbers in Excel using VBA or shortcuts. For shortcuts, you can use CTRL + D to fill down or CTRL + R to fill right. To automate this with VBA, use the following code. Sub DragFillRows() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name ' Define the range to fill Dim fillRange As Range Set fillRange = ws.Range("A1:A10") ' Change to your desired range ' Fill the range based on the first cell fillRange.FillDown End Sub Regards ExcelDemy

@@exceldemy2006 Thanks again!!! Can I point an active cell , drag and autofill from one row above the active cell, down certain numbers of rows? 🙏🙏🙏

Hello @Kevin-g3g1v, To autofill from one row above the active cell down a specific number of rows. Select the active cell where you want to start. Use this formula in VBA: ActiveCell.Offset(-1, 0).Copy Destination:=ActiveCell.Resize([number_of_rows], 1) This copies from one row above the active cell and fills down the specified number of rows. To do it manually; Copy the data above, select the cells below, and use the autofill handle to drag it down. Regards ExcelDemy

@@exceldemy2006 Thanks a lot!!!

Hello @Kevin-g3g1v, You are most welcome. keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @Kevin-g3g1v , Yes it is possible. You will need to use VBA along with task schedular. You can use this VBA code to add a new row automatically in an Excel table every day: Sub AddRowDaily() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name Dim tbl As ListObject Set tbl = ws.ListObjects("Table1") ' Change to your table name ' Add a new row at the end of the table tbl.ListRows.Add End Sub To automate this, you need to use Windows Task Scheduler to run the macro daily. Schedule the Task: 1. Open Task Scheduler on your computer. 2. Select Create Basic Task. 3. Name the task and select Daily for the trigger. 4. Choose the time you want it to run. 5. Select Start a Program and browse for your Excel application (usually located in C:\Program Files\Microsoft Office oot\OfficeXX\EXCEL.EXE, where XX depends on your Office version). 6. In the Add arguments box, type /x "Path\to\your\file.xlsm" (replace with your actual file path). 7. Click Finish. This will run the macro daily at the specified time. Regards ExcelDemy

@@exceldemy2006 THANK YOU SO MUCH!!!

Hello @Kevin-g3g1v, You are most welcome. Thanks for your appreciation. Keep exploring Excel tutorials with ExcelDemy! Regards ExcelDemy

FIND it, 1:💯 😀

Hello @Kevin-g3g1v, Congratulations! Glad to hear that you found the solution. Feel free to reach out to ExcelDemy! Regards ExcelDemy

Hello @mariameniserije762, If you're facing problem with the code section for adding images, it might help to check the image path or ensure that you're using the correct syntax for your Excel version. You can also verify that the images are accessible in the specified directory. If you provide more details about the specific issue you're facing, I'd be happy to help further! Or if your are having problem to find or copy the code, please check out the article link and you can download our Excel file to practice the code. www.exceldemy.com/how-to-create-a-database-in-excel-with-pictures/ Regards ExcelDemy

Hello @EniCipa, To set up Conditional Formatting for your scenario in Excel, follow these steps: 1. Select Column A where your statuses are listed. 2. Go to the Home tab >> from Conditional Formatting >> select New Rule. 3. Choose Use a formula to determine which cells to format. 4. Enter the following formula: =AND(A1<>"Closed", OR(A1="Ongoing", A1="Not Started"), B1<TODAY()-30) Adjust A1 and B1 to match the first cells of your selected range. 5. Click on Format, choose your desired format (like changing the font color to red), and click OK. 6. Press OK again to apply the rule. Now this will change the status to "Alert" for ongoing or not started items if the received date is older than 30 days, while statuses marked as "Closed" will remain unchanged. Regards ExcelDemy

Hello @mirghtaed, You are most welcome. It seems like the issue is related to how you're mapping the XML fields. Make sure you're selecting the entire range of cells (not just A1) for the mapping. XML mapping links the data structure to Excel, so if it's only selecting the header, it won’t map the entire range. Double-check that the XML schema matches your data structure and that your range selection is correct for the rows. Try remapping by dragging the field onto the entire column, not just the header cell. Regards ExcelDemy

Hello @muhammedalibuke4966, You might be referring to a missing line on a distribution chart in Excel. This issue could be due to the chart type selected or data not being plotted correctly. To fix this: Ensure you’ve selected the correct chart type (like a line or scatter plot). Check if the data range includes both the X and Y values. Right-click on the chart and select "Select Data" to verify if the data series is added properly. Regards ExcelDemy

Hello @sp1375, Thanks for your appreciation. You can post your templates requirements in our ExcelDemy Forum: exceldemy.com/forum/ exceldemy.com/forum/members/shamimarita.2/ Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @valjosol12, You are most welcome. Glad to hear that our tutorial helped you to solve you problem. Our aim is to make the Excel easy to use. Keep learning Excel with ExcelDemy! Regards ExcelDemy

Hello @kounnaris, You’re demonstrating two methods for copying data from "Sheet1" to "Sheet2" using VBA. Method 1 is a straightforward one-liner, while Method 2 involves multiple steps to achieve the same result. Both methods are valid; it just depends on your coding style. If you have any specific questions about either method, feel free to ask! You can find more details in the original post. Regards ExcelDemy

Hello @ph.mekawey, To add Power Pivot to Excel on a Mac, follow these steps: 1. Ensure you have the latest version of Excel for Mac, as Power Pivot is included in Office 365 subscriptions. 2. Go to the Data tab and look for the Manage Data Model option. You might need to install the latest updates via the Help menu if not visible. 3. For additional functionality, consider using Power BI, which integrates with Excel. Regards ExcelDemy