Excel Functions


Excel Basics

16.1 Importing Data from Text Files into Excel 15.4 Dynamic Formulas by Using Excel Data Validation Techniques 15.3 Adding a Custom Excel Data Validation Error 15.2 Excel Decimal Data Validation 15.1 Creating an Excel Data Validation List 14.6 Excel Function: SUBTOTAL() 14.5 Excel Function: DCOUNT() 14.4 Excel Function: DAVERAGE() 14.3 Excel DSUM Function with AND Criteria 14.2 Excel DSUM Function with OR Criteria 14.1 Excel DSUM Function Single Criteria 13.9 Removing Duplicates in Excel 13.8 Using Conditional Formatting to Find Duplicates 13.7 Format a List as a Table 13.6 Creating Subtotals in a List 13.5 Filter an Excel List Using the AutoFilter Tool 13.4 Using Custom Sorts in an Excel List 13.3 Sorting a List Using Multi-Level Sorts 13.2 Sorting a List Using Single Level Sort 13.1 Understanding Excel List Structure 10.4. Creating a Custom Template 10.3. Opening an Existing Template 10.1. Intro to Excel Templates 9.4 Printing a Specific Range of Cells 9.3 Adding Header and Footer Content 9.2. Changing the Margins, Scaling and Orientation 9.1. Viewing your Document in Print Preview 8.6. Working with Excel Pie Charts 8.5. Moving a Chart to another Worksheet 8.4. Formatting an Excel Chart 8.3. Adding and Modifying Data on an Excel Chart 8.2. Working with the Excel Chart Ribbon 8.1. Creating an Excel Column Chart 7.4. Working with Excel SmartArt 7.3 Formatting Excel Shapes 7.2 Inserting Shapes In Excel 7.1 Inserting Images and Shapes into an Excel Worksheet 6.9. Using Conditional Formatting 6.8. Merging and Centering Cells 6.7. Creating Styles to Format Data 6.6. Using Excel’s Format Painter 6.5. Formatting Percentages 6.4. Formatting Data as Currency Values 6.3. Adding Borders to Cells 6.2. Changing the Background Color of a Cell 6.1 Working with Font Formatting Commands 5.7 Moving and Copying an Excel Worksheet 5.6. Deleting an Excel Worksheet 5.5 Renaming an Excel Worksheet 5.4 Hiding and Unhiding Excel Rows and Columns 5.3. Changing the Width and Height of Cells 5.2. Inserting and Deleting Rows and Columns 5.1 Moving and Copying Data in an Excel Worksheet 4.9 Using the AutoFill Command to Copy Formulas 4.8 Excel’s AutoSum Shortcut Key 4.7 Using the AutoSum Command 4.6 Adjacent Cells Error in Excel Calculations 4.5 Working with the COUNT() Function 4.4 Working with the AVERAGE() Function 4.3 Working with the MIN() and MAX() Functions 4.2 Working with the SUM() Function – Excel 4.1 The structure of an Excel Function 3.7 Understanding the Order of Operation 3.6 Relative Versus Absolute Cell References in Formulas 3.5 Creating Basic Formulas in Excel 3.4 Working with Cell References 3.3 Entering Date Values in Excel 3.2 Working with Numeric Data in Excel 3.1 Entering Text to Create Spreadsheet Titles 2.10 Common Excel Shortcut Keys 2.9 Opening an Existing Excel Document 2.8 Saving an Excel Document 2.7 Understanding the Structure of an Excel Workbook 2.6 More on the Excel Interface 2.5 Customizing the Excel Quick Access Toolbar 2.4 Introduction to the Excel Interface 2.2 Microsoft Excel Startup Screen 2.1 Opening Microsoft Excel

6.4. Formatting Data as Currency Values

So, way back in the earlier discussions we are talking about different types of data that you can add to an excel worksheet. You add text you had numeric data you had dates you had functions and formulas.

Now I want to revisit the idea of the numbers, we have got a bunch of numbers in here. These numbers represent one of a couple of things.

I have got a lot of dollar amounts in here money that I am spending on different bills for these different months, so those should be represented as currency values. They should have things like decimal places thousandths commas, the dollar symbol or whatever. We use for the country we live in.

Formatting Data as Currency Values 1

The other numbers I have here are percentages, and right now I am getting decimal percentages. I want to turn that into an actual percentage. So, it is still a little bit of formatting on some of the numbers that we have inside of this worksheet.

First, let us do the currency values.

Thus, my first step I want to format two different groups of numbers I want to format D6 to F10 cell. I now want to format them as currency values, so I will go to my Home tab. About halfway through you got a section called number and we looked just really-briefly at those numbers section earlier when we were talking about the dates.

Here I am just going to flip on the currency style. Now, I want to point out that there are a few other currency styles here. I have got English United States I have got English United Kingdom and Euros and Chinese, Yen and so on. I can get into more counting styles or currency styles and pick from some other ones as well. For me, I grab English United States and I have now applied a currency value or an accounting style to my numbers.

Formatting Data as Currency Values 2

Now remember, what does it mean when you get the pound symbols the hashtags ####, remember that we discuss that is really-briefly earlier. While these are numbers and it is just too narrow, so I mean expand the column with that column and there are my numbers.

I have now got currencies styles or accounting styles decimal places thousand commas in my case the dollar symbol.

Now this is great because it is more accessible. Users can look at this and right away they think oh dollar amounts are great but for me personally this becomes a little too crowded for me, there is just too much going on. It is just it is a big rectangle and not enough white space in there. I like to leave the first row and the last drill as currency style decimal places thousands comma the mark the dollar symbol. But everything in between(D6 to F9), I still want the decimal, I still want that thousands comma. But the dollar symbol it is just too much going on in there. I want to open-up some whitespace and try to make this more presentable more readable.

There is just not enough white space to separate the lines, so you keep reading the same thing because your eyes are tired.

Same idea here. I want to open-up some white space, so I am going to remove the dollar symbol.

Now, I highlight D6 to F9, and go back to Home tab, back to Number and I am going to turn on the Comma style, I will give that a click.

Formatting Data as Currency Values 3

This leaves the decimals leaves the thousands comma if necessary but removes the dollar symbol. And for me I just think it looks so much better so much more presentable, easier to read.

And once again for me it just looks more presentable. But I will leave that up to you.

Now this is formatting our numbers as currency so try it out. Jump in, Open up your monthly quarterly report, and drop in your currency styles, Get those decimal places those thousands comma and the appropriate currency style for yourself.