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, a neat list of 7th-grade students. There is a centered title, colored header, borders on each cell, readable birth dates, and cash money formatted in Indonesian rupiah. Let's start from the plain data. Hello, welcome to the Excel tutorial for beginners, creating a neat and attractive table format. We will turn the plain student data into a readable table with a title, color, borders, rupiah format, and date format.
Step 1: Untidy raw data
This is the raw student data. The content is correct, but it's hard to read, there is no title, no borders, and the cash money numbers don't have the rupiah symbol. A neat table is not just about appearance. A neat table makes it easier for others to understand the data, and makes us look professional.
Step 2: Table title with Merge and Center
First, let's add a title. Click on row number 1, then press Ctrl-Shift-Plus twice to insert two empty rows above the table. Type the title in cell A1, list of 7th-grade students. Then select A1 to E1, and click the Merge & Center button so the title is centered in the table.
Step 3: Enlarge and bold the title
The title is still small. With the title cell still selected, click the increase font size button several times to make the text larger, then press Ctrl-B to make it bold. Now the title clearly appears as the header of the table.
Step 4: Colored table header
Next, the header of the table. Select A3 to E3 via the name box. Press Ctrl-B to make it bold, and Ctrl-E to center-align. Then press Ctrl-1 to open the Format Cells box, select the Fill tab, and click on the light blue color. Click OK, and the header of the table now has a background color.
Step 5: Table border lines
Next, the borders. Select the entire table, from A3 to E15. Press Ctrl-1, select the Border tab, then click the Outline button for the outer borders, and the Inside button for the inner borders. Click OK. Now every cell has borders, and the table looks structured.
Step 6: Format Rupiah for cash
Now format the numbers. Cash is in rupiah, so select E4 to E15, press Ctrl-1, go to the Number tab, then choose Custom. In the type field, type quotation mark, rupiah, space, quotation mark, then hashtag, dot, two hashtags, zero. This format displays the dot as a thousand separator. Click OK, and the numbers change to rupiah, although the original content is still a regular number. We can also change the display of the birth date.
Step 7: Format date of birth
Select E4 to E15, press Ctrl-1, go to the Number tab, choose Custom, then type DD, space, MMMM, space, YYYY, the date will appear with the full month name, for example 3 March 2011. Click OK.
Step 8: Center align and widen columns
The number column and the class column are easier to read if centered. Select H4 to A15, hold Ctrl, then select C4 to C15, and press Ctrl-E. Then widen all the columns. Select columns A to E, double-click on the column boundary line, so the width fits the content.
Step 9: Copy format with Format Painter
Another very useful trick is the Format Painter. For example, if we add a new column named phone number, and want it to have the same style as the name column. Click cell B3, then click the Format Painter button which looks like a brush on the Home tab. Then click cell F3. Immediately, cell F3 gets the same color, bold text, and border. Now type the column title, phone number, and widen column F. We don't need to repeat the settings from scratch.
Step 10: Save the result
Our table is now neat. Press Ctrl-S to save. Notice, we only changed the display. The original data hasn't changed at all. That's the power of cell formatting in Excel.
Done! The table is now neat and professional
Done. We have created a neat table. Centered title, colored header, border lines, currency format, and date format. 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 creation services, reports, and dashboards, as well as sells the practice files for 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
52 video classes1,276 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.
