Learn Excel, Pivot Tables & Charts

Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Last updated on November 11, 2021

Chandoo

Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

Slicers – what are they?

Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.

For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.

Add region as report filter and filter for the region you want.
Add a slicer on region and click on the region you want.

With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.

See this demo:

Getting started with Slicers – Video

Here is a quick 5 minute video tutorial on Slicers. If are just getting started with this AWESOME feature, you must watch the video, NOW. See it below or head to my YouTube channel.

Download Slicer Examples Workbook

This post is very long and has many examples. Please click here to download slicer examples demo workbook. It contains all the examples shown in this post and a fun surprise too.

How to insert a slicer?

Note: Slicers are available only in Excel 2010 and above.

Adding a slicer in Excel 2010:

In Excel 2010, you can add a slicer only to pivot tables. To insert a slicer, go to either,

Insert ribbon and click on Insert Slicer
or Options ribbon (PivotTable Tools) and click on Insert Slicer

Adding a slicer in Excel 2013 / 2016 / 2019 / 365:

In Excel 2013 and above, you can add a slicer to either pivot tables or regular tables.

Adding slicers to regular tables:

When you add a slicer to regular Excel tables, they just act like auto-filters and filter your table data. To add a slicer to regular table, use Insert ribbon > Insert Slicer button.

Adding slicers to Pivot tables:

To add a slicer, you can do either of these things:

Right click on pivot table field you want and choose “add as slicer”
Use either analyze or insert ribbon to add the slicer.

Single vs. Multi-selection in Slicers

You can select a single item or multiple items in slicers. To multi-select,

If the items you want are together, just drag from first item to last.
If the items you want are not together, hold CTRL key and click on one at a time.
You can also click on the “checkbox” icon in slicer header to multi-select items in slicers.

Creating interactive charts with slicers

Since slicers talk to Pivot tables, you can use them to create cool interactive charts in Excel. The basic process is like this:

Set up a pivot table that gives you the data for your chart.
Add slicer for interaction on any field (say slicer on customer’s region)
Create a pivot chart (or even regular chart) from the pivot table data.
Move slicer next to the chart and format everything to your taste.
And your interactive chart is ready!

Demo of interactive chart using slicer:

Here is a quick demo.

Linking multiple slicers to same Pivot report

You can add any number of slicers to a pivot report. When you add multiple slicers, each of them plays a role in telling the pivot table what sub-set of data to use for calculating the numbers.

Linking one slicer to multiple pivot tables

You can also link a single slicer to any number of pivot reports. This allows us to build very powerful, cross-filtered & interactive reports using Excel.

To connect multiple pivot tables to single slicer, follow these steps.

Optional: Give names to each of the pivot tables. To name the pivot tables, click anywhere in the pivot, go to Analyze ribbon and use the pivot table name field on top-left to give it a name.
1. If you don’t name your pivot tables, Excel will give them default names like PivotTable73. This can be confusing once you have more than a few pivot tables.
Right click on the slicer and go to Report Connections (in Excel 2010, this is called as PivotTable connections).
Check all the pivot tables you want. Click ok.

Now both pivot tables will respond to the slicer. See this demo:

Linking slicers to more than one chart

You can use the same approach to link one slicer to more than one chart (pivot chart or regular one).

See this demo:

You can examine this chart in detail in the Slicer Examples workbook.

Capturing slicer selection using formulas

While slicers are amazing & fun, often you may want to use them outside pivot table framework. For example, you may want to use slicers to add interactivity to your charts or use them in your dashboard.

When you want to do something like that, you essentially want the slicers to talk to your formulas. To do this, we can use 2 approaches.

Dummy (or harvester) pivot table route
CUBE formulas route

Dummy pivot table route

This is the easiest way to capture slicer selection into a cell. Using a dummy pivot table, we can find out which items are selected in slicers and use them for some other purpose, like below:

The process is like this:

Let’s say you want to know which profession is picked up in the slicer (so that you can use it in some formulas or charts).
Create another pivot table.
Add the profession field to row labels area.
Link the slicer to this new pivot table as well (using report connections feature of slicer)
Now when you click on the slicer, both original pivot and this new dummy pivot change.
Access row labels like regular cells in your formulas to find out which slicer item is selected.

See this illustration to understand how to set up the formulas:

CUBE Formula approach:

This is relevant only if your slicers are hooked up to a data model thru something like Power Pivot, SAS Cubes or ThisWorkbookModel in Excel 2013 or above.

To find out slicer selection, we need to use CUBERANKEDMEMBER() Excel formula like this:

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”, Name_of_the_slicer , item_number)

Let’s say you have a slicer on Area field, and its named Slicer_Area (you can check this name from Slicer properties)

To get the first item selected in the slicer, you can use CUBERANKEDMEMBER formule like this:

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”, Slicer_Area, 1)

This will return the first item selected on slicer. If there is no selection (ie you have cleared the filter on slicer), the Excel will return “All”.

Bonus tip: You can use =CUBESETCOUNT(Slicer_Area) to count the number of items selected in slicer.

Bonus tip#2: By combining CUBESETCOUNT and CUBERANKEDMEMBER formulas, you can extract all the items selected in the slicer easily.

Please download Cube Formula Slicer Selection example workbook to learn more about this approach.

Note: this file works only in Excel 2013 or above.

Formatting slicers

Slicers are fully customizable. You can change their look, settings and colors easily using the slicer tools options ribbon.

Here is a quick FAQ on slicer formatting:

Q. I have too many items in slicer. How to deal with this problem?

Simple. See if you can set up your slicer in multiple columns. You can also adjust the height and width of slicer buttons to suit your requirements. If your slicer is still too big, you can adjust the font size of slicer by creating a new style.

Q. I don’t like the blue color of slicer. What do I do?

You can switch to another color scheme. Just go to Slicer Tools Options ribbon and pick a style you want.

Pro tip: You can create your own style to customize all aspects of a slicer.

Q. I don’t like the title on slicer. Can I get it rid of it?

Yes you can. Right click on the slicer and go to “Slicer Settings”. Uncheck display header option to remove the header & clear filter button.

Q. My slicer keeps showing old products (or categories etc.) that are no longer part of data after refresh. What do I do?

Simple. Right click on the slicer and choose “Slicer settings”. Check Hide items with no data option.

Q. I want to make my slicers look good. But I don’t know where to start…

Here is an inspiration for you.

Slicers vs. Report Filters

In a way slicers are like report filters, but way better. (Related: Introduction to Pivot Table Report Filters)

There are few key differences between both.

Report filters are tied to single pivot tables. Slicers can be linked to any number of pivots.
Report filters are clumsy to work with. Slicers are very easy to use.
Report filters may not work very well in a touch screen environment. Slicers are great for touch screen UIs.
Report filters take up one cell per filter. Slicers take up more space on the worksheet UI.
Report filters can be automated with simple VBA. Slicers require a bit more code to automate.
You can access report filter values using simple cell references. Slicer values can be extracted using either dummy pivot tables or CUBE formulas, both of which require extra effort.

Slicers vs. Timelines:

If you have a date field in your data, you can also insert a “timeline”. this is a special type of slicer, that works only with date values.

Here is a quick demo of Timeline slicer.

You can also customize the look & feel of Excel Timelines.

The download workbook has an example of timelines.

Slicers & Compatibility

Slicers are compatible with Excel 2010 & above versions of Excel. You can also use Slicers with Excel Online.

If you create a workbook in Excel 2010 (or above) with slicers and email it to a friend using Excel 2007, they will see an empty box where slicer should be.

Slicers work on desktop & web versions of Excel in the same way.

Download Slicer Examples Workbook

Please click here to download slicer examples demo workbook. It contains all the examples shown in this post and a fun surprise too.

Also download the Cube formulas approach for slicer selection extraction workbook to learn that technique.

Additional Resources to learn about Slicers

If you like slicers and want to learn creative ways to use them in your work, check out below examples:

Do you use Slicers? What are your favorite tips about slicers?

As mentioned earlier, slicers are one of my favorite features of Excel. I use them liberally in my dashboards, charts & workbooks.

What about you? Do you use slicers? When do you use them? What are your favorite tips when it comes to using slicers? Please share in the comments area.

Share this tip with your colleagues

49 Comments
Ask a question or say something...
Tagged under advance, CUBE Formulas, data model, downloads, dynamic charts, Excel 101, excel 2013, Learn Excel, Microsoft Excel Formulas, pivot tables, power pivot, relationship, screencasts, slicers
Category: Learn Excel, Pivot Tables & Charts

Want an AWESOME
Excel Class?

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.

Rebekah S

Reporting Analyst

FREE Goodies for you...

Related Tips

2026 calendar and planner Excel template - how to use

Learn Excel

53 Responses to “Conditionally play sounds when a cell value changes”

Robert says:

August 13, 2008 at 11:02 pm

There is another alternative of doing this quite easily: Yes, Excel is able to talk to you!

Assume you are having a calculated or otherwise changing value in cell A1 (e.g. =RANDBETWEEN(0,100)), a threshold in cell B1 (e.g. 50) and a text in cell C1 (e.g. "Threshold exceeded").

Go to the VBA-editor (ALT-F11) and copy the the following procedure into the corresponding sheet:

Private Sub Worksheet_Calculate()
If Range("A1").Value > Range("B1").Value Then
Application.Speech.Speak (Range("C1").Value)
End If
End Sub

Excel will read the text in cell C1, whenever the value in cell A1 is large than the threshold in cell B1. Turn your speakers on and play with cell C1. It sounds a bit funny sometimes, especially when you try to make Excel read texts in other languages than English. But it works...
- brad says:
  
  January 20, 2018 at 6:31 pm
  
  I'm trying to use UDF and the speech aspect. I want a cell to calculate character total and when it reaches a limit it will speak the value. For some reason I can't get any UDF to show up in the list or work. I've tried simple ones such as:
  
  Function BeepOnce()
  Beep
  End Function
  
  with: =If (B3>30, BeepOnce(),"") in the cell but it gives errors. Confused.
  
  When a cel char count reaches 30 and I'm typin gfast, it will report the number.
  
  Im a blind writer and this would be very helpful. I can't get any to work though. Can't get any UDF to show up anywhere.
Mary says:

September 29, 2010 at 3:56 pm

I tried keying in the code exactly as you have:

Private Sub Worksheet_Calculate()
If Range("cx15").Value > Range("cy15").Value Then
Application.Speech.Speak (Range("cz15").Value)
End If
End Sub

When I try to run the condition, I get a compliation error.

The formula in my excel spreadsheet in cx 15 is: =IF(CO15>120,1,0)
cy 15 = 0
cz 15 = "Less than 120 days"

I'm driving myself crazy - HELP!!
Hui... says:

September 30, 2010 at 12:21 am

@Mary
The code you supplied and below works as it should in Excel 2007
===
Sub Worksheet_Calculate()
If Range("CX15").Value > Range("CY15").Value Then
Application.Speech.Speak (Range("CZ15").Value)
End If
End Sub
===
There are 2 reasons it maynot be working
1. Make sure it is on a Worksheet Object in VBA for the page you are working on not in a general code module
2. Make sure the " characters are real, often you can copy and paste text from here and although the " mark look ok, they are not the correct characters, If it isn't working retype them all
- Anil says:
  
  May 12, 2014 at 8:31 am
  
  Hi Hui,
  
  The SPeech VBA is working...but it reacts to all the changes that is taking place in various cells in the sheet. I need this speech vba to react to only when changes happen in a particular cell (A1) and particular sheet...is there a vba that can help me find a solution. Thanks in advance for your assistance.
  - Hui... says:
    
    May 12, 2014 at 3:02 pm
    
    @Anil
    Some of the comments above discuss that requirement
    
    Please read those first
Mary says:

September 30, 2010 at 3:09 pm

YEAH - thanks so much Hui. I had it in the general code module.
Another question?
Can I have this function check a range of cells in Excel?
The code as it is now is checking cells CX15, CY15 and CZ15
I need it to check from rows 15 - 45?
Hui... says:

September 30, 2010 at 11:33 pm

@Mary
Sub Worksheet_Calculate()
Dim myText as String
For Each c In Range("CX15:CX45")
If c.Value > c.Offset(0, 1).Value Then
myText = "Row" + Str(c.Row) + " " + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next
End Sub
Mary says:

October 8, 2010 at 8:24 pm

I keyed in the code exactly as above and I get a runtime error 1004 - application defined or object defined error.

Sub Worksheet_Calculate()
Dim myText as String
For Each c In Range(”CX15:CX45?)
If c.Value > c.Offset(0, 1).Value Then
myText = “Row” + Str(c.Row) + ” ” + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next
End Sub

The formula in my excel spreadsheet in cx 15 is: =IF(CO15>120,0,1)
cy 15 = 0
cz 15 = “Less than 120 days”

The code is in a VBA worksheet - any thoughts as to what I'm doing wrong?
Hui... says:

October 9, 2010 at 1:54 am

Mary
you need to be very careful copying text of the web
There are times where a character " will copy and look like a " but is in fact not a "
If you have copied the above go through the code and manually replace all the " characters with a " character

Also make sure the code is on the code page for the sheet you are working on, not a code generic module page in VBA
Mary says:

October 13, 2010 at 9:35 pm

The above string works wonderfully - however, every keystroke in Excel initiates the function. Is there a way I can set it to run on command?
Hui... says:

October 14, 2010 at 12:57 am

Mary
The following will run whenever there is only a change in the area CX15:CZ45
.
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myText As String
Dim TR As Integer
Dim TC As Integer

TR = Target.Row
TC = Target.Column

If TC >= 102 And TC = 15 And TR c.Offset(0, 1).Value Then
myText = "Row" + Str(c.Row) + " " + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next
End If

End Sub`

.
Otherwise you can go back to the following code and add a Button or a Shape to your worksheet and link the macro to it so that it only runs when you click on it
.

Sub Speak()

Dim myText As String

For Each c In Range("CX15:CX45")
If c.Value > c.Offset(0, 1).Value Then
myText = "Row" + Str(c.Row) + " " + c.Offset(0, 2).Text
Application.Speech.Speak (myText)
End If
Next

End Sub
Amin says:

April 27, 2011 at 5:22 am

Hey Hui,
I came across this site by mistake.
Man this is so COOL.
Will this also work as

Column A has all Names, Column B is Blank.
When a Cell in Column B is not Blank ("") then speak the corresponding name in Column A

It doesn't make any sense to write multiple if-then statements to make it work.
I know I caught this few months late, but I could really use this
Thanks
Hui... says:

April 27, 2011 at 5:32 am

@Amin
Try the following
Copy and paste it into a Worksheet Module for the Worksheet in VBA you are working on

Private Sub Worksheet_Change(ByVal Target As Range) Dim myText As String For Each c In Range("B:B") If c.Value <> "" Then myText = c.Offset(0, -1).Text Application.Speech.Speak (myText) End If Next End Sub
Amin says:

April 27, 2011 at 8:44 pm

Hui,
It works but it repeats all name as:
Cell A1=John, A2=David, A3=Lisa.
When Cell B1 is not Blank then is speaks John.
Next when Cell B2 is not Blank it speaks John and David. Second time it should only speak David (fyi B1 and B2 both are not blank)

example: I want it to speak when certain individual logs in. But procedure, when someone logs in it speaks all the people who have already logged in.

how should I change that?

Thanks for assistance.
Hui... says:

April 28, 2011 at 12:01 am

@Amin
Try this modification

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Application.Speech.Speak (Target.Offset(0, -1).Text) End Sub
Amin says:

April 28, 2011 at 2:33 am

Thanks Hui that worked perfectly.

How do I make it work with 4 sets of columns

Same as before but with 8 columns
Columns(A,C,E,G) have names.
Columns(B,D,F,H) are blank to start with.

I tried to few ideas my way but nothing worked
Hui... says:

April 28, 2011 at 3:07 am

@Amin

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Or _
Target.Column = 4 Or _
Target.Column = 6 Or _
Target.Column = 8 Then _
Application.Speech.Speak (Target.Offset(0, -1).Text)
End Sub
Vivekanand TM says:

November 3, 2011 at 4:30 pm

I need a Excel Speaking code for the below condition

If I3 ="Buy Alert" ' If I3 cell has Buy alert Text then, sound should be like " Vivek Buy Alert For Infosys(next cell text) and
If I3 ="Sell Alert" ' If I3 cell has Buy alert Text then, sound should be like " Vivek Sell Alert For Infosys(next cell text)

Please help me to fix this in my excel
Peter says:

November 21, 2011 at 8:51 pm

This is soo cool. Im looking for a similar thing
Source B1:B200 have an output in Range C1:C200 every cell in the range C1:C200 checks the condition in D1:D200 and with conditional formating change color when value in C1>D1
How can I play a standard windows sound when C1>D1 and another sound when C1<D1
viral kadecha says:

June 14, 2012 at 7:21 am

hi hui,
i am a trader in stock market
i can export stock rate to excel sheet from my trading software..

is it possible that when stock rate changes the cell speaks means, e.g.( if stock rate is 25 and if it changes to 26 then cell should speak twenty six,if it further changes to 26.50 it speaks twenty six point fifty)
hope u can understand my problem.
waiting for your reply
- Hui... says:
  
  June 14, 2012 at 7:59 am
  
  @Viral
  
  Have a look at: http://chandoo.org/wp/2008/08/04/play-sound-when-cell-value-changes/
  - viral kadecha says:
    
    June 18, 2012 at 6:57 am
    
    HI
    I HAD A LOOK AT YOUR SUGGESTIONS BUT UNABLE TO UNDERSTAND
    I AM USING OFFICE 2003,
    I TRIED THIS
    """""Assume you are having a calculated or otherwise changing value in cell A1 (e.g. =RANDBETWEEN(0,100)), a threshold in cell B1 (e.g. 50) and a text in cell C1 (e.g. “Threshold exceeded”).
    Go to the VBA-editor (ALT-F11) and copy the the following procedure into the corresponding sheet:
    Private Sub Worksheet_Calculate()
    If Range(“A1?).Value > Range(“B1?).Value Then
    Application.Speech.Speak (Range(“C1?).Value)
    End If
    End Sub""""""
    BUT I DON'T UNDERSTAND WHAT TO CALCULATE IN ("C1")?? WHICH RANGE TO BE GIVEN TO ("A1")
    BASICALLY I DONT GETTING IT PROPER
    
    WHAT I WANT IS IF LAST TRADED PRICE (LTP) IN CELL CHANGES THE EXCEL SHOULD SPEAK THE VALUE WHICH IS IN THE CELL
    
    MEANS IF LTP IS 26.60 AND IF CHANGES TO 26.70 IT SHOULD SPEAK TWENTY SIX POINT SEVENTY AND IF IT CHANGES FURTHER TO 26.80 IT SHOULD SPEAK TWENTY SIX POINT EIGHTY
    
    PLEASE HELP ME....
Arindam says:

June 14, 2013 at 3:00 pm

I am using the following code for sound. It was working well but has suddenly stopped respondig. Could u help relating to the matter

Private Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long

Function Alarm(Cell, Condition) Dim WAVFile As String Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000 On Error GoTo ErrHandler If Evaluate(Cell.Value & Condition) Then WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) Alarm = True Exit Function End If ErrHandler: Alarm = False End Function
Elliot says:

September 9, 2014 at 3:04 am

Hey Hui, Great stuff really helpful, Although I am having the same issue as Amin, where any keystroke entered prompts the text to speech. I tried fitting in your fix for Amin but I didn't have success, below is the code I am successfully using, however each keystroke prompts the voice, and the voice lists each cell that qualifies the formula I am using.

Sub Worksheet_Calculate()
Dim myText As String
For Each c In Range("bf4:bf45")
If c.Value = 1 Then
myText = c.Offset(0, -57).Text
Application.Speech.Speak (myText)
End If
Next
End Sub

I tried
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Application.Speech.Speak (Target.Offset(0, -57).Text)
End Sub

any Mods to limit the speech to only what was most recently qualified?
- Hui... says:
  
  September 9, 2014 at 6:14 am
  
  @Elliot
  
  Try:
  Private Sub Worksheet_Change(ByVal Target As Range)
  If Target = 1 And _
  Target.Column = 58 And _
  Target.Row >= 4 And _
  Target.Row <= 45 Then _ Application.Speech.Speak Target.Offset(0, -57).Text End Sub
Elliot says:

September 9, 2014 at 2:28 pm

Hi Hui,

I tried that code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = 1 And _
Target.Column = 58 And _
Target.Row >= 4 And _
Target.Row

Thanks
Elliot says:

September 9, 2014 at 2:30 pm

Seems like the previous cut off the rest of my message, anyways above code the object module didnt allow me to step inot it, I keep getting a pinging, while on the same object module I inserted the previous code I had running and it allows me to step in! Confusing.

Thanks.
Elliot says:

September 9, 2014 at 2:55 pm

Hey Hui,

So the code works, I discovered the code does not recognize the column because each cell in the column is an if then formula with a returned value, such as "1" for true "0' for false, it only works when I remove the formula and insert a 1 in a cell, any vba fixes for this, or I can try a temp. workaround.
Elliot says:

September 9, 2014 at 3:15 pm

Hey Hui,

Disregard the above. The code works, however only after I hit enter in the target column, When the column updates by itself the code doesn't fire.
RAJ says:

November 6, 2014 at 1:35 pm

Hi Hui,

Amaze to see all the above information. I hope even I get a solution to my problem.
I am trying to use this excel as a token machine. I have linked up this token sheet with other sheet which shared by four users. If any off the users changes the token number, it gets change in our main display but the problem it doesnt say the token number. WHAT I EXACTLY WANT IS AS THE VALUE GETS CHANGED IN ANYONE OF THIS SPECIFIED CELLS (B4, B5, B6 & B7 THE EXCEL MUST ANNOUNCE THE NUMBER. For example If in cell B4 the number 7 comes, so the excel should tell " TOKEN NUMBER 7", after that in cell B7 is updated 21, so its should again tell as " TOKEN NUMBER 21"... and so on it should keep doing the same.

I hope I get my solution at the earliest.

Thanks
Raj
nagesh says:

November 17, 2014 at 5:04 am

Dear Sir,
I read your blog, it's very nice. I am a trader, & need a help from your side.
If my stock goes below -5.5 % ( column is = L,M,N,O) then it has to automatically change it's color & make a beep sound or alarm. Please write the code for this query.
Thanks in advance,
Warm Regards,
Nagesh
- Hui... says:
  
  November 17, 2014 at 8:19 am
  
  @Nagesh
  Try the following
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Value < -0.055 And Target.Column >= 12 And Target.Column <= 15 Then Application.Speech.Speak Target.Text Target.Interior.Color = 123456 'Change to suit End If End Sub
  - nagesh says:
    
    November 17, 2014 at 2:38 pm
    
    hi sir,
    i tried your coding today,It works for color and sound didn't come for below -0.055!
    but getting the below error
    Application.Speech.Speak Target.Text ( it's in yellow color in vb code sheet). Also got error like run time error, application defined or object error! I am using ms excel 2003.
    please advice me
    - Hui... says:
      
      November 17, 2014 at 3:11 pm
      
      @Nagesh
      That won't work in 2003
      I'll see what options there are tomorrow
      It's sleeping time here
      - nagesh says:
        
        November 17, 2014 at 4:45 pm
        
        ok sir,
        See you tommorrow, Sleep tight, sweet dreams, bye
        nagesh
      - Hui... says:
        
        November 18, 2014 at 1:30 am
        
        @Nagesh
        I think the blog software removed some details
        The code as posted was
        
        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Value LTE -0.05 And Target.Column GTE 12 And Target.Column LTE 15 Then
        Application.Speech.Speak Target.Text
        Target.Interior.Color = 123456 'Change to suit
        End If
        End Sub
        
        Replace GTE with >=
        Replace LTE with <=
nagesh says:

November 17, 2014 at 10:19 am

Hi Hui,
Thanks for your kind reply. I will try this ..
Regards,
nagesh
Nagesh says:

November 19, 2014 at 6:04 am

Hi Hui Sir,
I tried your updated coding, again it's showing same error. Sir, i will make you things clear as follows"
1) I am using MS EXCEL 2003 & It's a part of MS OFFICE STANDARD EDITION 2003.
2) I'm new to this excel, I need formula for the excel and coding for vb .
3) Sir, Below is the Data needs to play sound when the Number comes down -5.5%. It's in L,M,N,O, P Column of Excel Respecitively!
%Total 1 = L Column; Optional = M Column; Calc=N Column, Final=O Column, Additn = P Column in Excel Respectively!

%Total 1 Optional Calc Final Additn
1.0664851 -5.567 -0.499205809 -0.198547765 1.76
4.44350551 -0.1267561 -0.348579275 -0.237667688 2.66
-2.2927545 -2.651762402 -2.472258486 -2.562010444 -2.24
1.194029 0.608955224 0 0.304477612 0.1

Sir please advice me, It's very impt to me to play sound when it comes down -5.5 %, Once it's done, i'll be very happy.
Also, i want to learn online excel vb from your end if you providing excel training, please tell me fees structure and discounts for the excel training.
Expecting Solutions for my queries and hope you solve.
Thanks for your kind reply for my previous mails.
Warm Regards,
Nagesh
Nagesh says:

November 19, 2014 at 7:56 am

Hi Hui Sir,
The code you adviced me is :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 12 And Target.Column < = 15 Then
Application.Speech.Speak Target.Text
Target.Interior.Color = 123456 'Change to suit
End If
End Sub

It's working fine, but only problem is :-
1) It's speaking for all numbers like, -1 %, -2 %, -5%.etc.., I need only for -5.5%.
2) In Cell "A" , I HAVE LIST OF MEDICINES like "Ranbaxy - A2 ", "Cipla - A3", "Micro Labs - A4" upto "A 168". If the data in "L,M,N,0,P" Column comes down in b/w -5.5 % to -20.5 % then , Ms Excel 2003 has to Say: Ranbaxy -5.5 %, Cipla - 6.6 %, Micro Labs - -10 % etc..
- Hui... says:
  
  November 19, 2014 at 8:01 am
  
  @Nagesh
  
  The code I posted is:
  Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Value LTE -0.05 And Target.Column GTE 12 And Target.Column LTE 15 Then
  Application.Speech.Speak Target.Text
  Target.Interior.Color = 123456 ‘Change to suit
  End If
  End Sub
  
  Replace GTE with >=
  Replace LTE with <= If this isn't clear, please post a file in the Forums http://chandoo.org/forum/
  - Nagesh says:
    
    November 19, 2014 at 9:04 am
    
    Hi Hui Sir,
    
    i'm trying to post file in the forum : http://chandoo.org/forum/, it's not taking there. Please Help me the below, Please sir, please
    The code you adviced me is :
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = 12 And Target.Column < = 15 Then
    Application.Speech.Speak Target.Text
    Target.Interior.Color = 123456 'Change to suit
    End If
    End Sub
    
    It's working fine, but only problem is :-
    1) It's speaking for all numbers like, -1 %, -2 %, -5%.etc.., I need only for -5.5% to -20.5%
    2) In Cell "A" , I HAVE LIST OF MEDICINES like "Ranbaxy – A2 ", "Cipla – A3", "Micro Labs – A4" upto "A 168". If the data in "L,M,N,0,P" Column comes down in b/w -5.5 % to -20.5 % then , Ms Excel 2003 has to Say: Ranbaxy -5.5 %, Cipla – 6.6 %, Micro Labs – -10 % etc..
    
    Requesting last last help from your end, my work almost done with your valuable time and advice. Please help me sir,
    nagesh
    - Hui... says:
      
      November 19, 2014 at 12:04 pm
      
      @Nagesh
      
      This is the code, I have posted 3 times
      
      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Value LTE -0.05 And Target.Column GTE 12 And Target.Column LTE 15 Then
      Application.Speech.Speak Target.Text
      Target.Interior.Color = 123456 ‘Change to suit
      End If
      End Sub
      
      Replace GTE with >=
      Replace LTE with <= If the Forum isn't working your file is too large Compress it or save to a dropbox etc style account or email me directly, click on Hui and email is at bottom of the page
Victor says:

July 16, 2015 at 7:00 pm

Here is a copy of part of my Dash Board:

Alert Summary

EM 22.8%
FIN 22.1%
ENERGY 18.1%
SPX 24.0%
NDX 27.0%
RUSSELL 23.5%

Each of the percentages is a link to a separate page in the work book. For example, the 22.8% is actually =+EM!D10 which is the answer to the following formula =((ABS(G7/((F7+F6)/2))))

I want some sound alert that will tell me when the value is greater than 35%. Since the spread sheet dlynamically update every second, I would assume that a ding ding ding repeating would work fine.

Can you give me a step by step on creating an appropriate macro that I can run when I am afc. I have no programming skills but can cut and paste or retype with the best of them 😉

Thanks in advance for any help you can give me. BTW the range on the Dash board that I want to cover is b10...b15
mark swan says:

May 2, 2016 at 1:31 am

Dear Huai,

is there a way to have a vba code to make a sound alert dynamically when a condition is met without running the module or have a button? I tried the options above and beep now commands but no luck.
Ian Wardell says:

May 2, 2016 at 1:05 pm

More explicit instruction are required. Where exactly does one put:

Function beepNow()
Beep
End Function

??
Nazmul Hossain says:

August 28, 2016 at 7:04 pm

Dear Sir (Hui),
Pls help me to provide such a excelmacro code so that I can easily heard a specified Sound of the text which I want when the criteria or the condition happened.It may be Hello/Congratulations etc.
Suppose A120=SUM(A5:A119) &
D120=SUM(D5:D119)
G120=A120-D120
Condition:IF(G120=0,"hello"/"congratulations "

Otherwise "Sorry" / "be patience"
Please help if possible.
- Hui... says:
  
  August 29, 2016 at 6:24 am
  
  @Nazmul
  
  Please ask this in the Chandoo.org Forums http://forum.chandoo.org/
  
  Please attach a sample file to simplify the task
  - Nazmul Hossain says:
    
    August 29, 2016 at 3:43 pm
    
    @Hui,
    I can not find the attachment option & message option in chandoo.org site after sign up.Please help.
    - Hui... says:
      
      August 30, 2016 at 6:32 am
      
      @Nazmul
      
      You have to be registered
      Then goto: http://forum.chandoo.org/forums/ask-an-excel-question/ and select Post a New Thread
      Complete the form
      Next to the Submit button is an Add Attachment button, or simply drag the file onto the screen
Zain says:

January 12, 2017 at 7:21 pm

Hi,

I need a code for the cells speak
auto matically with a regular interval of 10 min.means after every 10 min my cells speak.
Nishant says:

June 26, 2017 at 5:14 pm

Hi Hui,

Hope you are in good health and doing well. I was surfing the internet for some excel solutions for my workbook and I came across some very good solutions by you. Hence, I am writing to you to get some insights to my problem

So here it goes,
I have a software which gives me live stock prices in excel and I have added all my analysis to that file so that I can get Long and Short Calls for the scrips I analyze. Now, there are around 200 companies that I analyse and the calls appear as the stock price change and reach my buying or selling levels.

I need my excel to say the companies name which is in cell C2 when "Long" / "Short" text appears in my cell B2 which has my analysis formulas. Current market price is in A2

Also I need this for a range as I mentioned I have 200 companies.

I really hope you can help me with this.

This is what I have tried uptill now, but this works for one cell and not a range

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("B2").Value = "50" Then
Application.Speech.Speak (Range("C3").Value)
End If
End If
End Sub

Please Find me a Solution

Thank You,

Best Regards,
Nishant Shah
- Hui... says:
  
  June 27, 2017 at 12:30 am
  
  @Nishant
  Can you please ask this question in the Chandoo.org Forums http://forum.chandoo.org/
  Please attach a sample file to simplify the solution

Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Chandoo

Slicers – what are they?

See this demo:

Getting started with Slicers – Video

Download Slicer Examples Workbook

How to insert a slicer?

Single vs. Multi-selection in Slicers

Creating interactive charts with slicers

Demo of interactive chart using slicer:

Linking multiple slicers to same Pivot report

Linking one slicer to multiple pivot tables

Linking slicers to more than one chart

Capturing slicer selection using formulas

Dummy pivot table route

CUBE Formula approach:

Formatting slicers

Slicers vs. Report Filters

Slicers vs. Timelines:

Slicers & Compatibility

Download Slicer Examples Workbook

Additional Resources to learn about Slicers

Do you use Slicers? What are your favorite tips about slicers?

Get FREE Excel + Power BI Tips

Welcome to Chandoo.org

Related Tips

53 Responses to “Conditionally play sounds when a cell value changes”

Get FREE Excel & Power-BI Newsletter

Get Started

Online Classes

About

Downloads