Wasim Akram

Dear FODAY,

Thank you for your response. As per your query-

1. You can create a Pie Chart to visualize the sales from the “Insert” option.

Imagine a dataset with multiple Bakery Products and their Total Sales. Now we will create a pie chart using the information from the dataset.

First, select the products and total sales column and then press “Recommended Chart” from the “Insert” option.

Second, from the new dialog box choose “Pie Chart” and press OK to continue.

Finally, within a moment your final pie chart will be in your hands showing sales of multiple products.

2. As per your second query-
We will use the IF function to calculate the “10%” discount price if the sales of cake is more than 100.
To do that choose a cell (G5) and write the following formula down-

=IF(E5>100,(D5-(D5*0.1)),D5)

Hence, hit Enter and the final output will be in your hands.

3. Now coming to the last query you wanted to use the data validation for ladies with only 5 letters in their name. Well, below I have shared the simplest solution.
Suppose we have a dataset with some Lady Names. Now we will use data validation for the name list.

First, selecting the names from the list click the “Data Validation” option from the “Data” feature.

Second, click “Settings” and choose criterias according to the screenshot and press OK to continue.

Finally, we have applied data validation over the name list. To check try editing any names not fulfilling the condition and you will get a notification about “This Value doesn’t match the data validation restrictions defined for this cell.”

Hello jean,
Thank you for sharing your problem with us!
In order to calculate the participation percentage, you can follow the instructions below.

1. Simply choose a blank cell.
2. Insert the following formula:
=COUNTIF($C$5:$C$25, E5)/COUNTA($C$5:$C$25)

Thus you can calculate the participation percentage.

Note:
1. The formula you used did not work because you did not lock the cell range using absolute reference.
2. Another reason is don’t leave the cells blank for the choosen cell range.

Dear vishal saini,
Thank you for your response.
Here I have shared a solution using VBA code to protect a worksheet in the manner you are looking for.

First, opening the VBA window you need to put the following code in the This Workbook section. Don’t forget to change the sheet name according to your sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set WS = Worksheets("Sheet1")
Sheet1.Addresses = ""
Sheet1.Values = ""
For i = 1 To WS.UsedRange.Cells.Count
If IsEmpty(WS.UsedRange.Cells(i).Value) = False Then
Sheet1.Addresses = Sheet1.Addresses & _
WS.UsedRange.Cells(i).Address & ","
Sheet1.Values = Sheet1.Values & WS.UsedRange.Cells(i).Value & ","
End If
Next i
End Sub

Next, insert the below code in your worksheet. You can change the password from the marked section.

Public Addresses As String
Public Values As Variant
Public NewAddresses As String
Public NewValues As String
Public IsDone As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Password = "ExcelDemy"
Adds = Split(Sheet1.Addresses, ",")
Vals = Split(Sheet1.Values, ",")
If Target.Value <> "" Then
Sheet1.NewAddresses = Sheet1.NewAddresses & _
Target.Address & "*" & Target.Value & ","
End If
Found1 = False
Found2 = False
NewValue = ""
If Target.Value = "" And Sheet1.IsDone = False Then
For i = LBound(Adds) To UBound(Adds)
If Adds(i) = Target.Address Then
Found1 = True
Exit For
End If
Next i
NewAdds = Split(Sheet1.NewAddresses, ",")
For i = UBound(NewAdds) - 1 To LBound(NewAdds) Step -1
Arr = Split(NewAdds(i), "*")
If Arr(0) = Target.Address Then
NewValue = Arr(1)
Found2 = True
Exit For
End If
Next i
If Found1 = False And Found2 = True Then
Target.Value = NewValue
UserPassword = InputBox("Enter the Password: ")
If UserPassword = Password Then
Sheet1.IsDone = True
Target.Value = ""
Sheet1.IsDone = False
Target.Select
Else
MsgBox ("Invalid Password.")
Target.Select
End If
End If
End If
End Sub

Finally, you can protect your worksheet in the manner where you can add data and after saving the data you can’t delete the same until you don’t save.

Thanks
Wasim Akram
Exceldemy Team

Dear Anita,
Thank you for the comment. We really appreciate your response for sharing with us.
The currency sign not appearing in Excel may be due to the values are stored as text in your spreadsheet. When values are stored as text, you need to convert those as numbers and then change the format to currency to get your desired currency format. You can follow the below instructions to learn about this technique.

First, choose the cells (D5:D12) and click the error icon following the image below.

Next, choose Convert to Number option from the list.

As the numbers are now converted as number, now press CTRL+1 to visit the Format Cells feature to add currency symbol.

From the Format Cells window, choose Currency from the left pane, select your desired currency symbol and hit OK.

As a result, you will get the currency sign added in your worksheet.

If you are still having problem with the currency symbol, then you can share your workbook with us. We will check and solve the problem.

Thanks & Regards
Wasim Akram
Team Exceldemy

Dear Ben,
Thank you for your comment. We really appreciate you for contacting with us.
In order to insert multiple rows of data, you can try the below VBA code.

Sub InsertDataIntoTable()
Dim tableName As ListObject
Set tableName = ActiveSheet.ListObjects("Table1")
Dim dataRows() As Variant
dataRows = Array( _
Array("1/1/2022", "Apple", 5, 1.77), _
Array("2/1/2022", "Banana", 3, 2.05), _
Array("3/1/2022", "Orange", 8, 0.95))
Dim i As Long
For i = LBound(dataRows) To UBound(dataRows)
Dim addedRow As ListRow
Set addedRow = tableName.ListRows.Add()
With addedRow
.Range(1) = dataRows(i)(0)
.Range(2) = dataRows(i)(1)
.Range(3) = dataRows(i)(2)
.Range(4) = dataRows(i)(3)
End With
Next i
End Sub

Using the Array statement inside the VBA code, you can add as many rows you want inside your table.

Thanks & Regards
Wasim Akram
Team Exceldemy

Dear TAYFOON,

Thank you for your response.

If you want to see the arguments as “RETAILPRICE(Price1,Price2,Divisor)”.
Then follow the instructions below-

1) In the module Place the following code-

Public Function RETAILPRICE(Price1, Price2, Divisor) RETAILPRICE = (Price1 + Price2) / Divisor End Function

2) Then go to your worksheet and type “=RETAILPRICE(” and after that press Ctrl+Shift+A to get the arguments just as you want.

Hope you found your solution. Thanks!

Dear FLEET,

Thank you for your response.

The answer for the intersection of “Laptop” and “China” will be “$63,800”.
The SUMPRODUCT function returns the sum of an array from an argument. With the intersection between “China” and Laptop” within the cell range we got two outputs which are “$56,000” and “$7,800”. Thus summing the total value with the SUMPRODUCT function stands to “$63,800”.

You can check the screenshot below.

We have also attached a worksheet with our article. You can practice the formula there, too. Thanks!

Dear ERIC CHARLES BAUMGARDNER,
In order to enter your own workbook value into the code, you need to change the ranges inside the code. Check the following screenshot. Thanks!

Dear Jamil,
Using conditional formatting you can search for a specific text and color them according to your choice. Follow the instructions below.

Suppose we have a dataset of some countries’ names in cells (B3:E9). Now we are going to color a specific text (United States) from the list.

First, select all the cells and click the “Text that contains” option from the “Conditional Formatting” feature.

Second, put your desired text and choose a color of your choice. Gently, press OK.

Finally, we have successfully highlighted a certain text from the list.

Hope you got the solution you are looking for. If you still having problems then check the link below. Thanks!
Highlight Cells Based on Text

Dear DON_1234567,
You can use the simple subtraction formula to calculate the difference between two times.
You can use the formula below-
=C5-D5
Check out the screenshot below-

If you are still not getting the result. Then change the cell format to time.
You can visit the below article to learn more. Thanks!
Calculate Difference Between Two Times