[Libs-Or] Tech-Talk: EXCEL/G-Sheets – Filtering Your Content

Tue Mar 26 16:44:13 PDT 2024

Welcome to the latest issue of Tech-Talk!

Having trouble reading this email?
You can view the tech tip<https://www.tech-talk.com/excel-filtering-your-content.html> and the communication tip<https://www.tech-talk.com/12-power-words-10-would-you-be-willing.html> online instead at:
https://www.tech-talk.com/login/oregon

When prompted for a username and password, use ORLIBTECH for both.

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.
Sort the data in your spreadsheet

[https://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

[https://files.constantcontact.com/ee1208b4001/bed541b4-0a14-42f0-8b53-e7fab8c5d579.png]<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTdjKS5c1PSCEXRbBFzMofi8_ND_iwQxThFg61gPQ9vwjJrLZ2Oo5FUrvmt52Y27zrGNZ_DCN8ZMATy8LuYm-4QZr3RwluwFjSMnZnsGC0Rs8hsSiSXcnxrx8SnWST4QqSd7l_MbEc7qd&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==>

Photo by Andréas BRUN<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTdjKS5c1PSCE55cZNyFSeagAhbHl_CnAB7Z7Zp1ClFN870BAmjbnJKN3Yg4T5aTYm72ki_9oznswrJ4yG-s1pVVQXd8feTfItBm7nQQw2zIGXq8oNo6OAu_w7QGIV8iifF3bVtfMIDoePLNCbev_sinvn3N-eoOipdY0y8ZJG5fely2txGwY0FQvkXuR-oZXahaO5PXPqZZFYhmHaymb9QA=&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==> on Unsplash<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTdjKS5c1PSCEKnztzs78XCmIjpbHEuChkaNFlnkpX1bjcM9qolHmhp7dlvs9BwVS5-b_aBCrJHZ_yHFexaRss5CdImmz_tyDUM3yQ8Lebr-4UXiFFV3D1SNwMIsXia6fjUunsXh4bKmt0SLXpF4DPOb1zPNGBOGbEuUZkuNRRaZjgmwh23Ks0OJY0BTdiUiazl8Q0fpT7J6UP5hqzqZ7TKLgbMmOpbWE_giOYIWaHI12U4x3n1_-GJbVaQpIk1ZPdFB3Qliu0t2A3QnBOIUk8i8=&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==>

[https://files.constantcontact.com/ee1208b4001/e25f6bcf-26a8-4e59-91f2-cc248962042d.png]<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTabIqlIqndJkz7oyoisNQffBiC8jbL81-QV9nJY1JOD06CSQwhHsuIeLrKW2YBTlZgXEf5J37rQlwH-AkJX-RPzu0fJYgDQxoOJ80tcX9Ljy&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==>

Webinars for You

NOTE: All Webinars are One Hour Long and Begin at 3 pm ET / 2 pm CT / 1 pm MT / 12 Noon PT

·     March 27: [EXCEL & ACCESS] Use Simple Excel & Access Databases to Organize Storytelling Resources

·     April 10: [EMAIL] 10 Gmail Features and Shortcuts You Might Not Be Using

·     April 24: [COMMUNICATE] Leadership Communications: Tools and Techniques, Part 1

·     May 8: [EMAIL] Getting Started with the "New" Outlook for Windows

View Webinars and Register Here<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTdm1h7P7kn_2ONBYF6JsunJRWTeYi1FUhLUa81FKQnT3wOeEcwRdg3BO0Vl5rlX3YHUT-GFd48gVGV4FjCt0qZDktwnArHmMqDqVv-mJImgU&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==>

EXCEL/G-Sheets – Filtering Your Content

Intermediate

Have you ever been working in a spreadsheet and wished you could find all the items that fit multiple criteria – a subset of your data?

You could try the Sort function to rearrange your content, but when you have more than one element you’re looking for, this feature just doesn’t do an adequate job.

[Excel filter]

For example, you may need to find all of the individuals in your worksheet that are:

·     Located in the Northwest region AND…
·     Who had sales of over $100,000 in the first quarter of the year AND…
·     Who has been with the organization for less than one year.

Once you get past a few variables in the Sort feature, it gets more difficult. This is when you should harness the power of the Filter option. It is a simple way to narrow down your data by one or more columns.

Unlike Sorting, Filtering doesn't just reorder your content. It hides the rows containing data that do not meet the filter that you specified. When you use a Filter, you are telling Excel to only show you those line items that fit the elements you selected.

Filtering gives you an easy way to work with a subset of your data in your spreadsheet. You’re not deleting the content, just telling Excel to only show you certain rows. Once you run a filter you can continue to work with that slice of data. You can copy, edit, format, create charts, or print.

What Can You Filter?

Filtering gives you control over what content you want displayed. Create a subset that matches certain criteria that you want to view, such as:

·     A date or range of dates
·     A number or range of numbers
·     The same text value (example: a state abbreviation)
·     Part of a text value (like all cells that contain part of a word)
·     Cell color

EXCEL - How to Set Up a Basic Filter

First, make sure you have a Header Row set up in your spreadsheet with column names as this is where you will see the function for Filtering when it is set up. (Little triangles.)

[https://files.constantcontact.com/ee1208b4001/23cf2f3c-64ad-4c0c-96bc-8a13879037dd.png]

NOTE: It is important to think about what you want to filter in your data before setting up your spreadsheet so that you have a consistent type of criteria in each column. For example, you wouldn’t put a date in one row and in the next, under the same column head, put a dollar amount.

To Create a Filter:

·     Select the range of data that you want to filter by highlighting the rows/columns.
·     On the Data tab, in the Sort and Filter section, click on the Filter icon.
·     A dropdown arrow will be added to the header row.

[filter]

How To Filter

To filter by an element shown in the header row, click the dropdown arrow and choose the item (or items) by checking the box next to them, and then click OK.

In the example below, we wanted to find all of the people who signed up for the Infographics webinar. We filtered by Webinar Topic and put a checkmark in the Infographic box.

[choose filter]
·     After you make your filter selection, your content will be reduced to just those items in rows that contain your selected filter item(s).

·     You can continue to filter by another column to reduce your results even further.

TIP: Notice below that the dropdown arrow for the header cell has changed from a triangle to a filter symbol so that you’ll know you have filtered content in that column.

[https://files.constantcontact.com/ee1208b4001/2dcbe03f-7c27-4912-901c-a5f55ebc8a0c.png]

To turn the Filtering option completely off, go to the Data tab and click the Filter icon to disengage it. However, your data will remain filtered unless you previously went to each column that you filtered to clear the filter for that column.

Advanced Filter Choices

Text Filters

If you have information that contains a certain word in the text (or part of a word) — and you want your filter to find any and all data that has that text in a particular column, you can use a Text Filter.

For instance, you may have rows that contain data like Microsoft Word, Microsoft Excel, Microsoft PowerPoint ... under the column heading of Software. If you want your filter to list any rows that have “Microsoft” in it (no matter if it is Word, Excel or PowerPoint), you can filter by the word.

Click the dropdown arrow for your column and select Text Filters. Pick the “Contains” option. On the next screen, type in Microsoft. Your filter will now show results with any cell that contains the word Microsoft as part of the entry.

Note that there are other Text Filters like Equals, begins with, Ends with and so forth

[sort by text]

Sort by Number

If your data contains numbers and you want to find a particular number or a number range, you can use the Number Filter. For instance, if you want your results to contain a dollar range, like $10,000-$50,000, you can use this filter to find all of the content in between.

[filter]

Click on the dropdown arrow and choose Number Filters. Here you’ll find all sorts of helpful options including Greater Than, Less Than, Between, etc. You can select from actual values in your spreadsheet cells, or type in your own custom amounts.

In the example to the right, we clicked on the Sales column, chose Number Filters, then Between. In the Custom AutoFilter box, we entered 10,000-50,000. So our new filtered content only contains entries that had sales between $10-50K.

Date Filters

If you're working with information that has Dates, try using Date Filters.

[https://files.constantcontact.com/ee1208b4001/e6bc9c7f-7905-492d-b79b-f3bbf83bef37.png]

Again, click the dropdown arrow in that column and select Date Filters. There are a few different ways that you can select to filter:

1.  Either use the checkboxes to select by year, month or date (when you click the + sign by each month, individual days will show).
2.  Or, under Date Filters, choose from one of the preset options that will be displayed like Today, This Week, This Month, This Year, etc… Additionally, you can create your own custom date range.

These are only a few types of options in filtering. Play around with the criteria to narrow down your content so that you are displaying the results you need.

Google Sheets - Basic Filtering

Similar to Excel, you can use the filter option in Sheets in the same way to hide data in the spreadsheet to focus and see only the content that you need.

First, be sure each column has a title for the header.

·     To create a filter, first select your range of cells.
·     Then in the toolbar go to Data and select Create a filter from the menu. (Or click on the Filter icon in the menu.)
·     Each column header will now have a filter icon in the top right.

[https://files.constantcontact.com/ee1208b4001/670ed36b-e0e1-41d2-998a-a2640f8158bf.png]

·     In the column you want to filter by, click the filter icon and from the menu you can choose to filter your data by color, condition or values.
·     For example, if you toggle the arrow beside Filter by condition, depending on your data, options include filtering by Text, Date, or Number (greater than, less than, equal to, etc.)
·     You can filter more than one column to narrow the results as needed.

[filter by condition]

When you want to remove the filter, go to Data in the menu and choose to Remove filter.

NOTE: If you have shared the spreadsheet with others, anyone with permission to Edit your spreadsheet will be able to view and change the filter as well.

[https://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communication: 12 Power Words

#10: "Would you be willing ...?"

Oh my goodness, sometimes you get to a point in a situation where a colleague just doesn't want to do what you suggest, right? And there are times when a team member is very upset and doesn't want to agree to the solution that's on the table.

Then there are the occasions when you know in advance that you're going to ask someone to do something ... and you want them to agree. How can you phrase the request so they jump on board?

All three of these situations can benefit from using the phrase...

"Would you be willing to ... ?"

This phrase opens up the receptivity of the listener.

Compare these:
·     "I know you're busy, but I need you to look at this additional responsibility." vs.
·     "I know you're busy, but would you be willing to look at this additional responsibility?"

·     "I see you're upset, so I think you should talk to the supervisor." vs.
·     "I see you're upset, so would you be willing to talk to the supervisor?"

* * *

If you can't wait 12 weeks to get all the power words (or learn more about this one), go to Tech-Talk.com<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTXUoHyNymFae4HTqc_AmN1OGaUw1onzbET7PjnK4PAEK0sLqzyA_eDhm_6bzbnK04jqTUjN7eCI_Tk180tmGzoC-Lm6dzNcWfw==&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==>. Search for "12 Powerful Words"

[https://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTRPIvVZq6cJUUPQ72llz8BNUkcNIcQ_wCWnZzdwUAFxZze5Lh9yP9WoyXK-1wsbRxaOlbEQqvDEB9sl0zqluiqMV930RDHLjvbVSHKWyWO_H&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==>

[Ask a question]<https://r20.rs6.net/tn.jsp?f=001C_6io7bkQdjbEK64JLTHZ5IxyoWb7PIzqQOpw6NKPhnLW7WmkjHNTYpeofhEKb9Jmy6z8GU7vWvtwx78rVlwn-yjMbIEBIVmX7rtrku60j8vjvgC2TBbXcTK4QcHkZeP7awAlSq2wrAn2y_5njHDazkxncCtHPA441k2JcJaSZ0=&c=UJkQnhOoHf8Z_C2rKkA1T0-QnIy3EYg1CR1yZiqeHcFAAogkZ-EtDg==&ch=k-rfUDNSp-M1kEyVHS8MSkixWpc7gzScMmw8bgWKLixIqJiHf3wKlw==>

Copyright 1996-2024 Shared Results International. Published weekly. Distribution is limited by license. For information on how to include additional recipients, contact support at tech-talk.com<mailto:support at tech-talk.com> 585-615-7795.

Cheers,
Darci Hanning, MLIS (she/her/hers)
Public Library Consultant / CE Coordinator
Continuing Education Resources: https://libguides.osl.state.or.us/conted

State Library of Oregon | Library Support and Development Services
971-375-3491 | darci.hanning at slo.oregon.gov<mailto:darci.hanning at slo.oregon.gov> | www.oregon.gov/library<http://www.oregon.gov/library>
Follow @StateLibraryOR: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Instagram<https://www.instagram.com/statelibraryor/> | Pinterest<https://pinterest.com/statelibraryor/> | YouTube<https://www.youtube.com/channel/UC0-kU8Gu0jS_YcnXg-b_TRA/featured>

[cid:image001.png at 01DA7F97.E91C5BB0]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20240326/354ff427/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20240326/354ff427/attachment.png>