How Do I Format Cells For Mac

1. How Do I Format Cells For Mac Numbers

Oct 19, 2018 - To change all the cells in a table, select the table. In the Format sidebar, click the Cell tab. Do any of the following: Change the cell borders: In. By Geetesh Bajaj, James Gordon. Formatting cells and cell ranges in Excel 2011 for Mac is like formatting tables. In the Font group of the Ribbon’s Home tab, clicking Borders button displays a drop-down gallery of border styles.

I am putting together a Network Table containing the details of all of my Network Devices and Connections. I need this because every time I need service, the first thing they ask me is the Model Number and Serial Number of my Modem, which is buried deep inside of a Wall Closet; or, they ask me for an IP or MAC Address which I cannot possibly memorize. So, I need to mix Number Formats with Text Formats for the following Example Cell items: 1. MAC Address: 00:1R:54:C8:CD:30 2.

IP Address: 250.250.250.58 3. Part Number: 515291-017-00 Is there a way to mix these formats, as shown above?

I cannot find it anywhere. From what I can find, Numbers only allows Text OR Number Formats, but no mixing of the two formats. Hi yo', The three examples can each be easily handled in a cell formatted to Text, and a cell left on 'Automatic' will automatically show them as Text.

There's nothing particularly numeric about any of these, despite the fact that each uses numeric characters. Functionally, they're simply labels, used in indexing, but not used in any calculations. The only instances in which I can see Automatic formatting failing are in the cases of serial numbers and the like containing either no non-numeric characters or containing only the letter E. Numbers will see these as 'numbers' and will format them as such.

Short ones will be aligned right, rather than left like the three examples. Long ones (over 11 digits) will be rounded and displayed in scientific notation. Those containing an E will be interpreted as being written in scientific notation and displayed as such. In these cases, you can force Numbers to see the entry as Text by starting the entry with a single quote. Example: 98765 displays as 1.23457E+14 (scientific notation) '98765 displays as 98765 Regards, Barry. Hi yo', The three examples can each be easily handled in a cell formatted to Text, and a cell left on 'Automatic' will automatically show them as Text. There's nothing particularly numeric about any of these, despite the fact that each uses numeric characters.

Functionally, they're simply labels, used in indexing, but not used in any calculations. The only instances in which I can see Automatic formatting failing are in the cases of serial numbers and the like containing either no non-numeric characters or containing only the letter E. Numbers will see these as 'numbers' and will format them as such. Short ones will be aligned right, rather than left like the three examples.

Long ones (over 11 digits) will be rounded and displayed in scientific notation. Those containing an E will be interpreted as being written in scientific notation and displayed as such. In these cases, you can force Numbers to see the entry as Text by starting the entry with a single quote. Example: 98765 displays as 1.23457E+14 (scientific notation) '98765 displays as 98765 Regards, Barry.

Barry, Yes, I agree with, and understand, what you have written. Now that I have 2 answers, I realize that I did not properly ask my question. I know that I can just Format the Cell as Text and then I can basically put anything in that Cell; but, then I have to type EVERY CHARACTER for each Cell. Thank you for the single quote recommendation as I did not know about that one, and I already had the problem with a long serial number displaying in Scientific Notation, which does not help me at all. What I really want to do is to be able to just type the numbers and characters, and have the Cell automatically Format it as an IP Address, a MAC Address or a Part Number. Like how you can format the Cell as a Telephone Number and it automatically puts in the spaces, parentheses and hyphens for you.

That is what I really want, the Application making it easier for me to enter the data without having to type each identical character, like a period, a space or a colon, for each entry. Does that state my issue a little better? Thank you, Yo. The difficult part of all this is if the OP wants that cell to automatically and robustly determine which format it is. I have no idea how it can know whether the number being entered is a MAC address, IP address, or part number.

There doesn't seem to be anything that makes each stand out if the dots and dashes are not typed in. In the telephone number example, the software knows it is a telephone number so it knows where to put the dashes. That's not the case here. For example is 8 an IP address (250.250.250.58) or a part number (250250-250-58)? Is 58 an IP address (250.250.250.158) or a MAC address (25:02:50:25:01:58)?

My suggestion is to have three columns. One is IP address, one is MAC address, one is serial number. Format each so they add the dashes, colons, and dots where desired. Type the numbers/letters into the correct column. Message was edited by: Badunit. No need to yell. It wasn't clear what you were asking.

Sounded like one cell, three formats to me. I'm not so sure you'll be able to do a custom format for the MAC address where you type in the numbers and letters and it reformats it (i.e, adds the colons) in the same cell after you hit return. Custom format doesn't seem to handle that unless it is all digits. You might have the same problem with the part numbers if they have any letters in them or if different parts have different formats. If all numbers and the format is always the same, a custom format is possible. IP address custom format is ###.###.###.### and hide the separator. You have to type in three digits for each section, such as '058' not '58'.

Part Number, if it is all numbers and always of the format you gave is ######-###-## and hide the separator. My suggestion is to have three columns. That's what I had assumed was the plan. Then formatting an additional cell column corresponding to each data cell could be easily populated with =MID(A2,1,3)&'.' &MID(A2,10,3) to create a URL, for example. (Note, the example doesn't employ any 'smarts', so it assumes the input string was 12 digits in a cell (preceded by a single quote to flag the cell as 'text') - it assumes leading zeroes, if any, are included.

The 'output' cell is formatted as 'automatic'. Input of '50 would be output as 250.250.250.250 - the leading single quote flags the cell as text and is ignored by the parsing.). If going the formula route, then I'd like to add a few suggestions. Maybe you or someone with a similar issue will be able to pick out a piece and apply to your situation. Assuming part# is always 11 digits and MAC always contains at least one non-number, then the input would be distinguishable.

First, format your entry cells as Text (e.g. Column A) so the leading zeros remain as you type them. Then, in column B: =IF(LEN(A1)=11,'Format PART',IF(ISERROR(VALUE(A1)),'Format MAC','Format IP')) This uses the fact that VALUE will return an error if the cell doesn't contain a number.

In formula above, you'd want to replace Format PART, MAC, and IP with your formatting formulas. So, with Brie Fly's IP format formula: =IF(LEN(A1)=11,'PART',IF(ISERROR(VALUE(A1)),'MAC',MID(A1,1,3)&'.' &MID(A1,10,3))) If MAC does not always contain at least one non-number, then you'll need to tag the MAC entry to be able to distinguish from IP and strip it out later while formatting. For example: for MAC you might type #30 Then the formula would look like: =IF(LEN(A1)=11,'Format PART',IF(ISERROR(VALUE(A1)), IF(LEFT(A1)='#','Format MAC-strip #', 'Format MAC'),'Format IP')) Or, you could simplify the formula by ALWAYS tagging MAC entries, so type #001r54c8cd30 Then formula might be: =IF(LEN(A1)=11,'Format PART',IF(LEN(A1)=12,'Format IP', IF(LEN(A1)=13,'Format MAC-strip #','must be typo, wrong number digits')) Also, if you didn't want to worry about capitalizing letters when typing, you could use UPPER in the formatting portion of the formula for MAC. Apple Footer. This site contains user submitted content, comments and opinions and is for informational purposes only. Apple may provide or recommend responses as a possible solution based on the information provided; every potential issue may involve several factors not detailed in the conversations captured in an electronic forum and Apple can therefore provide no guarantee as to the efficacy of any proposed solutions on the community forums.

Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the.

In, I demonstrated how to transform your static data into a simple yet sexy database in a matter of seconds. If you don’t know how to use table formatting, go read that post and then come back.

Otherwise, your data will look like one of those housewives who goes to grocery store with curlers and a moo moo sporting red lipstick. Enough chatter. Let’s jump in. If you want to follow along, you can. (Just click the Download button in the upper-right corner of your browser window. Don’t ask me why Dropbox sticks it up there.

Developers ) Standard Number Formatting You would think everyone would use number formatting; it’s so rudimentary. But I can’t tell you how many times I see decimals in charts that should be formatted as percentages or no thousandths separators (a pet peeve of mine) or two decimal places filled with 0’s because the number didn’t need decimals. These newbie mistakes are uncalled for because Excel placed several number formatting options front and center in the Number section of the Home tab. Click for larger image. For the neophytes, here are what those icons mean, from left to right: Currency Convert to currency. This option will left-align the dollar symbol and right-align the number. Technically, this is referred to as Accounting formatting.

(If you want the currency symbol to be flushed up against the number, don’t use this icon; use the Currency option in the drop-down menu above this row of icons.) You can also choose different currencies from the drop-down menu to the right of the currency icon.% Convert decimals to a percentage., This is not a comma; it’s a thousandths separator. And if you have a number greater than 9999 you should use it. (I usually use it for numbers greater than 999.) One weird quirk with this button is it adds two decimal places whenever you apply it. Most of the time they’re unwarranted. If you don’t have decimals, you should ditch the decimals because they just add noise. But that’s what the next icons are for. Increase Decimal Add decimals.

Since I’m not overly neurotic or a scientist, I very rarely use this option. Decrease Decimal Lob off unwanted decimals. I use this all the time. PSA: Please — for the love of all that is holy and measurable — get rid of decimals in chart axes. Select the axis, press Ctrl-1 (Mac: Command-1) to bring up the formatting options, and adjust it in the Number section.

Click for larger image. More Ribbon Options Both PC and Mac offer more number formatting options from the ribbon.

How Do I Format Cells For Mac Numbers

All you have to do is click the drop-down menu above the icons we just looked at. (Yeah, guess who Microsoft loves more.) These options are pretty lame. The only one I use on occasion is Fraction. When you see the flexibility you have with the custom number formatting, you’ll snub your nose at these bourgeois offerings. Custom Number Formatting Options Okay, this is where it starts to get fun. At the risk of sounding cliche, your options are nearly endless once you learn how to rock the Custom option. Pro Tip: To open formatting options, just select the cell(s) you want to format and press Ctrl-1 (Mac: Command-1).

This actually works for anything in Excel: charts elements, images, etc. Date Formatting Excel gives you quite a few options to choose from under Number Date in the Format Cells dialog (which, again, you get to by pressing Ctrl-1 or Command-1 on the Mac). But I’m fond of the format that looks like Aug 13 (no year), which Excel doesn’t offer. Following a few simple principles, you can construct your own formatting options.

We’ll use August 3, 2012 in our examples below: m: 8 mm: 08 mmm: Aug mmmm: August d: 3 dd: 03 ddd: Fri (because it fell on a Friday) dddd: Friday yy: 12 yyyy: 2012 So here are some examples of how you can show Aug 3, 2012: Aug 3, 2012: mmm d, eye Friday, August 3, 2012: dddd, mmmm d, yyyy Fri. 08.03.12: ddd.

Mm.dd.yy Formatting Positive & Negative Numbers I’m always looking for new ways to make data pop when I have to use tables (like if there’s too much data to shoehorn into a chart). A great example of this is webmaster tools data.

If you run a report like the Search Queries report from Google Webmaster Tools (GWT) — by navigating to Traffic Search Queries — and click the “With Change” option, as shown below, you’ll have a sea of positive and negative numbers in your export. Click for larger image.

Sure, in the GWT interface they’re all colorful, but once you get them into Excel, they transform into the ugly duckling of data, and nothing stands out. But Excel gives you the option to dictate formatting for positive, negative numbers, and even 0. To format these numbers, you just need to remember to follow this format: formatting for positive number; formatting for negative number; formatting for 0. To use colors, Excel has a handful built in: BLACK GREEN RED BLUE CYAN MAGENTA WHITE YELLOW Note: These aren’t case sensitive. If you’re a diva and those options are too constricting, Excel offers 56 colors in the form of color X.

I actually find the green option too light, so I use color 10 instead. You can find the in the helpful chart on this site. We’ll experiment with a GWT Search Queries report from the (the agency I work for). Here’s a sample of a column that has positive and negative numbers: To format these, the steps are simple: Step 1: Select the column you want to format.

Step 2: Pull up the Format Cells dialog and navigate to Number Custom. Step 3: In the Type field enter the following formula (of sorts): This just tells Excel, in addition to the colors, make the numbers percentages with no decimals.

If you want one decimal place, just add it to the number, e.g., 0.0%. Two decimals, make it two, e.g., 0.00%. If you didn’t want percent, drop the% sign, e.g., 0.0 would translate to something like 4.7. You can also add things like currency symbols. Here’s a screenshot of what your spreadsheet will look like after applying custom formatting to a Search Queries report. Click for larger image.

One more thing I’ll say about formatting numbers with color is you can use conditional formatting to format positive and negative numbers, but you shouldn’t. Conditional formatting is ultra cool, and I’ll be covering it in my next post. But it’s a processor hog. So the litmus test I follow — to keep my spreadsheets as light and agile as possible — is if it’s a static number, I use number formatting. However, if it’s formatting is conditional on another factor, I use conditional formatting.

Okay, let’s try that again in plain English Let’s say you run a rank report from your fave tool, and you want the current rank to be red if the keyword has fallen in rankings and green if it’s moved up. Since the color of the current rank is conditional on the rank of the previous day, week, or month (most importantly, a value from another column), you would need to use conditional formatting. In the case of the webmaster data, all of the values are static, so we were able to use custom number formatting. Adding Text To Number Formatting (Yes, It’s Still actually A Number) Let’s say you don’t want negative signs, parentheses, or colors. Instead, you want positive numbers to append “Profit” to a currency value, “Loss” to a negative currency value, and “Break Even” (with no number) if it’s 0. Your formula would look like this: $0″ Profit”;$0″ Loss”;”Break Even” Note: You have to include a space inside the quotes, or it’ll look like $4583Loss.

No grinding on the dance floor please. If you look at the formula bar, you can see Excel sees it as a number value, not text.

Another clue that it’s a number is it’s right-aligned. Text is always left-aligned in Excel. Condition-Based Formatting Square brackets aren’t just useful for formatting positive and negative numbers; they can also be used to format conditions. We’re also going to use another handy function Excel offers. You can use leaders, just like you can do in Microsoft Word, by setting a character to repeat.

It’s easier to see it in action and then explain it. So let’s say we want to write a formula that specifies that for numbers less than 1000, we want the word “Low” to be left-aligned, with the number right-aligned; for numbers greater than 2000, we want the word “High” to be left-aligned, with the number right-aligned; and numbers in between to say “Medium” with the number right-aligned.

Here’s what the formula would look like. Click for larger image. The last condition basically says, “And for everything else, left-align the word ‘Middle’, por favor.” And here’s what it would look like when you’re finished: And how did we get those extra spaces? Just throw an asterisk into your formula; then whatever character you put after it (in this case a space) will be repeated to fill up the cell. If we had put a period after it, it would have filled the space with periods. Even though I’m not a big fan of tabular data, there are quite a few things you can do to give your data a makeover and make it actionable, even within the confines of a table. Keep in mind  you can sort and filter by color once you apply any kind of color formatting to your data.

So if you wanted to see all query terms that moved up in rankings in your GWT Search Queries report before those that fell, you can sort by putting the green numbers at the top, then the red, then the black. To learn even more advanced uses of custom number formatting, check out this. Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed.