At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
This is the final result we will create, data of 20 employees sorted by division and salary, with filter buttons in each column title, locked header row, and salary displayed in Indonesian rupiah. Let's start. Hello, welcome to the Excel for beginners tutorial, sorting and filtering data. We will learn to sort employee data from smallest to largest, from largest to smallest, and by two columns at the same time, then filter only the data we need, and lock the header row so it remains visible when scrolling.
Step 1: Clean up employee data
This is data of 20 employees, ID number, name, division, city, salary, and date of entry. The data is still messy. First, let's tidy it up. Select the letters of columns A to F, then double-click the column boundary line so all content is visible. Then select the salary column, press Ctrl-1, choose custom category, and type in the rupiah format.
Step 2: Sort salary from largest to smallest
Let's sort based on salary. Click any cell in the salary column, any cell within the data. Open the data tab, then click the Z to A button, which sorts from largest to smallest. Now the employee with the highest salary is at the top. Notice, the entire row moves with it, so the name and salary remain paired.
Step 3: Sort from smallest and then by name
To sort from smallest to largest, click the A to Z button. Now the lowest salary is at the top. The same method applies to text, for example names or divisions, which are sorted alphabetically. Click a cell in the name column, then click A to Z, and the names are sorted from A to Z.
Step 4: Sort in two levels, department then salary
What if we want to sort based on division, and within each division sort by the highest salary? There is a trick. Sort the least important column first. Click a cell in the salary column, sort Z to A. Then click a cell in the division column, sort A to Z, Excel keeps the previous order, so within each division the salary remains descending.
Step 5: Sort by date of joining
Date entries can also be sorted. Click on any cell in the date column, then click the A to Z button to sort the dates from oldest to newest, which shows the most senior employees first, up to the newest ones. Excel stores dates as numbers, so sorting them is just as easy as sorting numbers. Click Z to A to reverse it, from newest to oldest.
Step 6: Apply filters
Now let's apply filters. Click on any cell within the data, then press Ctrl-Shift-L to display small arrows on each column header. Filters allow us to display only specific data entries without deleting the others.
Step 7: Filter by Marketing department
Click the arrow in the division header. In the search box, type marketing, then press Enter. Only employees in the marketing division will appear, and the row numbers turn blue as an indicator that a filter is active. We can add another filter in the city column, and both filters will work together.
Step 8: Filter by salary numbers
Filters also apply to numbers. Click the arrow in the salary header, then type 9.000.000 in the search box and press Enter. Only employees with a salary of 9 million will appear, and the total number of rows will show up in the status bar at the bottom left. We can combine division filters and salary filters for very specific searches.
Step 9: Remove filters
To remove filters, press Ctrl-Shift-L again, or click the arrow and choose Clear Filter. The data now appears fully again. Filters do not change the data, they only hide the rows that do not match.
Step 10: Freeze the header row
Lastly, lock the header row. If the data has hundreds of rows, the column headers will disappear when we scroll down. Open the View tab, click Freeze Panes, then select Freeze Top Row. Now scroll down, and the header row will still be visible. Save the file with Ctrl-S.
Done! Data is now sorted and easy to filter
Done. We have learned how to sort data from smallest to largest and vice versa, Sort by two levels, apply filters, and lock the header row. Thank you for watching, see you in other tutorial series. Want to master Excel for work and business? Cerdas Inovasi Digital offers Excel and Office courses, template, report, and dashboard creation services, as well as selling the practice files of this tutorial. Contact us via WhatsApp, or visit cerdasinovasidigital.com. Thank you for watching, see you in other tutorial series.
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,281 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
