***Official*** Excel Help Corner (1 Viewer)

[Chemical X]

I always forget how to format a proper less-than-or-equal-to in Excel. Unfortunately it doesn't just simply recognize the characters ≤ or ≥

Is there a forum dedicated to just excel questions, like this one?

 

[acarey50]

Is there a forum dedicated to just excel questions, like this one?

Google is usually your best friend - there are lots of forums, but I have found google to be easier and quicker in most cases over a forum, unless it is to the point of needing som fine tuning.

Sites like chandoo.org and mrexcel.com off the top of my head are some of the first I look to in the google search results.

 

[Psychopav]

Google is usually your best friend - there are lots of forums, but I have found google to be easier and quicker in most cases over a forum, unless it is to the point of needing som fine tuning.

Sites like chandoo.org and mrexcel.com off the top of my head are some of the first I look to in the google search results.

I agree. The only thing to watch for with Google is often I'll get sucked into a site that's trying to sell me their Excel add-in instead of just giving formulas or VBA programming.

Not a big deal, but it slows me down when I'm trying to solve a specific problem, not looking to buy some software solution that I wouldn't even trust not to have a virus.

 

[acarey50]

I agree. The only thing to watch for with Google is often I'll get sucked into a site that's trying to sell me their Excel add-in instead of just giving formulas or VBA programming.

Not a big deal, but it slows me down when I'm trying to solve a specific problem, not looking to buy some software solution that I wouldn't even trust not to have a virus.

Yeah, there are a few of those - usually they say - here's a formula way to do it, here's a VBA way to do it and hey, here's our add-on you can but that will do it with a few button clicks.

 

[Nemesis]

Looks like Microsoft just updated Excel for Mac, and now I'm having this quirky issue.

The "recent fonts" drop-down list in the home ribbon used to show frequently used variants of several fonts. For example, I'd be able to drop-down and see both Arial Narrow and Arial Black, two fonts I use all the time in formatting my reports. And no others, since those two fonts make up about 95% of my spreadsheets.

However, now, it only lists the base font in the main drop down: Arial, plain. Then there's an arrow> over to all the Arial options: Narrow, Bold, Bold Italic, Black, Narrow Bold Italic, etc. So what was one click--drop-down, select Arial Black, is now click, arrow over, search among the list of not recently used fonts in the Arial family, find the one I do want, click.

Anyone know how to, either: restore the previous functionality to Excel for Mac, or, have a way to create a shortcut, button, script, or macro to "toggle" between two fonts? That is, if there was a shortcut key like Command-Whatever, and I pressed it when the cell was Arial Narrow, it'd switch the cell to Arial Black, and vice versa.

It would have to be a shortcut, button, script, or macro that was local to my machine and not living in the spreadsheet, as my spreadsheets get passed around to various departments around the world and I don't want them to "break" if the other user doesn't have what I have. Also, I would rather add it once, to my machine, rather than have to program it into all 500+ spreadsheets I'm maintaining globally.

While the QAT (Quick Access Bar) doesn't already have specific fonts to choose, I would try recording a Macro and adding it to the QAT at the top of the Excel Frame
https://www.howtogeek.com/232620/how-to-add-a-macro-to-the-quick-access-toolbar-in-office/

Looks like your specific question was asked:   
https://answers.microsoft.com/en-us/msoffice/forum/all/how-add-specific-fonts-or-characters-to-quick/cc0e6649-3403-4d9e-a9e7-bd9febe1c620?auth=1

 

[Brony]

This is tangential to Excel and I'm looking for any solution that can help me with this: 

I have a list of 100+ cases/projects for work.  These are currently in Excel and new cases get added each day.  We have multiple people working on each case and I'd like some place to capture the notes on each case as people work on this, such as

Case 245:

Sept 16th 3PM  Jane - "I just started working on this."

Sept 16th 5:04PM  Bill - "Did you hear about the story on CNN that might affect case 245 and 257?" 

I want to be able to quickly view the notes for each case and maintain the history of all that has transpired. 

Excel doesn't seem to be the right tool for this and our existing CRM sucks and won't be able to do this.   We have Microsoft Teams which can accomplish group chatter but I can't figure out how to make it specific to 100+ individual projects.    

Does anyone know of a collaboration solution where we can keep secure notes on all of these cases within our organization?  Is Azure within MS Teams able to do this?  If I can push new case numbers from Excel into the solution would be the cherry on top. 

 

[Ned]

This is tangential to Excel and I'm looking for any solution that can help me with this: 

I have a list of 100+ cases/projects for work.  These are currently in Excel and new cases get added each day.  We have multiple people working on each case and I'd like some place to capture the notes on each case as people work on this, such as

Case 245:

Sept 16th 3PM  Jane - "I just started working on this."

Sept 16th 5:04PM  Bill - "Did you hear about the story on CNN that might affect case 245 and 257?" 

I want to be able to quickly view the notes for each case and maintain the history of all that has transpired. 

Excel doesn't seem to be the right tool for this and our existing CRM sucks and won't be able to do this.   We have Microsoft Teams which can accomplish group chatter but I can't figure out how to make it specific to 100+ individual projects.    

Does anyone know of a collaboration solution where we can keep secure notes on all of these cases within our organization?  Is Azure within MS Teams able to do this?  If I can push new case numbers from Excel into the solution would be the cherry on top. 

Totally depends on what you're company is willing to spend on.  I'm a Jira fan, but Trello might do the trick for you too.

https://www.atlassian.com/software/jira

https://trello.com/home

 

[Brony]

Totally depends on what you're company is willing to spend on.  I'm a Jira fan, but Trello might do the trick for you too.

https://www.atlassian.com/software/jira

https://trello.com/home

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

 

[Pigskin Fanatic]

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

Are you on Office365?

 

[Ned]

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

Jira is likely the right answer, but there is definitely a learning curve to it. I converted our team to it this year - huge leap forward in project tracking.  Feel free to PM if you want to dive into it.

 

[Ignoratio Elenchi]

Thanks.  Everytime I go to the screenshots, examples for these types of solutions, I never encounter anything that resembles what I'm looking for.  Is the learning curve high to create something like I want? 

I've used the two he mentioned and also Asana, and in my (very limited) experience, while Jira is popular with the devs and IT folks, etc. the non-technical teams preferred Asana.  Practically no learning curve, easy to create a list of projects and anyone can add comments.  Can also create sub-tasks, assign projects to people, and lots of other useful functionality but if you literally just want a list of projects that people can add comments to, any of these solutions will work but Asana might be easiest to pick up.  Trello also quite simple IIRC but no one seemed to like that one as much. 

 

[Brony]

Are you on Office365?

yes.

 

[Brony]

thanks for everyone's feedback.  I'll look into these.  Good stuff! 

 

[Pigskin Fanatic]

yes.

Someone already suggest Trello, which I loved when it first came out years ago, so if you have Office365 then you have access to an app called Planner. It's like Trello, but has integration to Outlook that makes it almost exactly what you described. PM if you need assistance, but I use it for small project tracking (use a real project tracker if it's bigger than a bread basket, which O365 also has), assigning tasks to my analysts, and some collaborative stuff.

 

[Jayrok]

Macros in excel.

We have macros that create a workbook with multiple sheets and grabs data from 4 different reports that are downloaded into a file.  There is a simple user form with combobox created where an analyst selects a system from a drop down list and then a family from another drop down list.  The macros grab the data from reports that are specific to the selected system and family (of tools for example).

Example system from list is "EPS" and a family may be "ABC".  So the macro would grab the data and populate an excel worksheet with columns/rows that are associated in the report filtered by the selected system and family.  So we'd see only the EPS system and family of tools represented by ABC.  Any other family of EPS is deleted and data is cleaned up.

Dim System as String

Dim Family as String

to initialize variables:

System = ""

Family = ""

This works fine.  Normally, we only need just the one family with the system.  We always will only need just one system (per sheet) but there are occasions where we need, say, 3 families of that system to populate the same sheet.  

A drop down list only lists the families and can be selected only one at a time in the combobox.  

What I'd like to do is remove the (Family = "") and have it where it doesn't matter how many families are in the reports, just have the macro populate all families in the appropriate column on the sheet.  

IOW, if there is a text value in the "Family" column cell of the report, I want it populated on the sheet.  Any value... AA, AB, AC BB, BC, CC... any text value.  The macro already works that if the target cell does not have the "Family" selected (in the drop down list), it deletes the row and shifts up, so the only rows showing after completion is the system and family that was selected by user.  

User selects the system from drop down list in combobox.  Then hits button and whatever family is listed on the various reports will be populated on the sheet.

OR, is it possible to have an entry on the family drop down list like "All" where "All" isn't just text but points to a table that contains multiple families?  And then every instance of "family" in the rest of the macros will mean all of the families in the table (but just those families in the table... or Array?).  Is that possible?

I hope this makes sense.  Wish I was better at VBA.  

Thanks in advance.  

 

[Ned]

Macros in excel.

We have macros that create a workbook with multiple sheets and grabs data from 4 different reports that are downloaded into a file.  There is a simple user form with combobox created where an analyst selects a system from a drop down list and then a family from another drop down list.  The macros grab the data from reports that are specific to the selected system and family (of tools for example).

Example system from list is "EPS" and a family may be "ABC".  So the macro would grab the data and populate an excel worksheet with columns/rows that are associated in the report filtered by the selected system and family.  So we'd see only the EPS system and family of tools represented by ABC.  Any other family of EPS is deleted and data is cleaned up.

Dim System as String

Dim Family as String

to initialize variables:

System = ""

Family = ""

This works fine.  Normally, we only need just the one family with the system.  We always will only need just one system (per sheet) but there are occasions where we need, say, 3 families of that system to populate the same sheet.  

A drop down list only lists the families and can be selected only one at a time in the combobox.  

What I'd like to do is remove the (Family = "") and have it where it doesn't matter how many families are in the reports, just have the macro populate all families in the appropriate column on the sheet.  

IOW, if there is a text value in the "Family" column cell of the report, I want it populated on the sheet.  Any value... AA, AB, AC BB, BC, CC... any text value.  The macro already works that if the target cell does not have the "Family" selected (in the drop down list), it deletes the row and shifts up, so the only rows showing after completion is the system and family that was selected by user.  

User selects the system from drop down list in combobox.  Then hits button and whatever family is listed on the various reports will be populated on the sheet.

OR, is it possible to have an entry on the family drop down list like "All" where "All" isn't just text but points to a table that contains multiple families?  And then every instance of "family" in the rest of the macros will mean all of the families in the table (but just those families in the table... or Array?).  Is that possible?

I hope this makes sense.  Wish I was better at VBA.  

Thanks in advance.  

A couple of thoughts, although its hard to say for certain without seeing the code:

1 - Is Family a required dropdown? If not, you can just let the users leave the dropdown blank for when they want all families returned.  To do that, you can wrap the filtering piece of your code with an IF statement.  That gives you the ability to still filter families if you need to.  Like the below.

If Not FamilyDropDown = vbnullstring Then
    <your existing family filtering code>
End If

2 - By dimming as a string, you don't need to initialize the variables to "" - that's already done when you dim it.

 

[Jayrok]

A couple of thoughts, although its hard to say for certain without seeing the code:

1 - Is Family a required dropdown? If not, you can just let the users leave the dropdown blank for when they want all families returned.  To do that, you can wrap the filtering piece of your code with an IF statement.  That gives you the ability to still filter families if you need to.  Like the below.

If Not FamilyDropDown = vbnullstring Then
    <your existing family filtering code>
End If

2 - By dimming as a string, you don't need to initialize the variables to "" - that's already done when you dim it.

Thanks, Ned.  For various reasons I can't paste the code, but it's long and in-depth.  The family symbol is important as many parts of the code rely on that variable in all of the reports.  All of the corresponding data are associated with the family and dozens of columns/rows are created in the sheet.

We typically just work one family per sheet, so Family is a required dropdown.  But it doesn't have to be in this case.  Right now, it's set that if Family dropdown is left blank, it exits the sub and stops there.  

I changed the UserForm to remove the Family list.  So they only pick the system.  This works and it grabs all families but lots of other data is missing so I need to dig deeper.

The variable "Family" is littered throughout the code and is needed to process info.  It's tailored for one family at a time.

Could I create a table containing the required families as one object, and then have the dropdown selection to point to that table (instead of just text in the list) so that every instance in the code of "Family" it will be associated with all entries of the table?  That would seem to work if all instances of "Family" meant families X, Y and Z... instead of just Y.  

Or instead of pointing to drop down list just have the UserForm entry already say "Red", for instance.  So when the userform pops up they select the system and "Red" is already in the other box... where "Red" is tied to the table of families I need.  And then replace "Family" with "Red" throughout the code.  Would something like that work?  

 

[Ned]

Thanks, Ned.  For various reasons I can't paste the code, but it's long and in-depth.  The family symbol is important as many parts of the code rely on that variable in all of the reports.  All of the corresponding data are associated with the family and dozens of columns/rows are created in the sheet.

We typically just work one family per sheet, so Family is a required dropdown.  But it doesn't have to be in this case.  Right now, it's set that if Family dropdown is left blank, it exits the sub and stops there.  

I changed the UserForm to remove the Family list.  So they only pick the system.  This works and it grabs all families but lots of other data is missing so I need to dig deeper.

The variable "Family" is littered throughout the code and is needed to process info.  It's tailored for one family at a time.

Could I create a table containing the required families as one object, and then have the dropdown selection to point to that table (instead of just text in the list) so that every instance in the code of "Family" it will be associated with all entries of the table?  That would seem to work if all instances of "Family" meant families X, Y and Z... instead of just Y.  

Or instead of pointing to drop down list just have the UserForm entry already say "Red", for instance.  So when the userform pops up they select the system and "Red" is already in the other box... where "Red" is tied to the table of families I need.  And then replace "Family" with "Red" throughout the code.  Would something like that work?  

I'm surprised removing the Family list for the UF didn't bomb the whole thing since it sounds like that list is depended on in a lot of places.

It's really hard to answer without seeing what you're working on, but I get why you can't share.

Honestly, if it were me, I'd take a step back and decide how to make it flexible instead of trying to patch in various fixes.  That's how bloated software happens.  Easiest solution is to use the ListBox instead of Combo so users can multi-select.  When the UF initializes, you can build the lists dynamically by reading tables you have hidden in a separate tab (gets you away from having to write out the lists inside the listbox or combobox).  Leaving the selection blank means you want all items that match that list, or you can do the "All" option like you suggested before.

 

[Jayrok]

I'm surprised removing the Family list for the UF didn't bomb the whole thing since it sounds like that list is depended on in a lot of places.

It's really hard to answer without seeing what you're working on, but I get why you can't share.

Honestly, if it were me, I'd take a step back and decide how to make it flexible instead of trying to patch in various fixes.  That's how bloated software happens.  Easiest solution is to use the ListBox instead of Combo so users can multi-select.  When the UF initializes, you can build the lists dynamically by reading tables you have hidden in a separate tab (gets you away from having to write out the lists inside the listbox or combobox).  Leaving the selection blank means you want all items that match that list, or you can do the "All" option like you suggested before.

Good advice, thanks.  I appreciate your time and help.

 

[Tiger Fan]

Need a little help here constructing the right graph.  I've got a data set of all of my NFL bets and projections placed this year (currently about 330 lines and grows every week).  I'm trying to build a graph so I can see if there is a correlation b/w the difference in what I project vs. the line of the bet and the result of the bet (win or loss).  I'm stuck.....and would really appreciate any help!  here's a link to the set of the 3 key columns (Description Below).

Position - Position of player I'm betting on

Abs Value % of projection - Absolute Value of the projection vs. the bet.  (Ex. If the bet is Derrick Henry over 90 yards, and I have him projected at 99 yards, it will show 10%)

Win/Loss - Actual record of Win or Loss of the bet

https://docs.google.com/spreadsheets/d/1ehpMgoNmFFEyPRYzyonvtsgNNnaoGFHutAW9EyJq6tQ/edit?usp=sharing

thanks!

 

[Ignoratio Elenchi]

Need a little help here constructing the right graph.  I've got a data set of all of my NFL bets and projections placed this year (currently about 330 lines and grows every week).  I'm trying to build a graph so I can see if there is a correlation b/w the difference in what I project vs. the line of the bet and the result of the bet (win or loss).  I'm stuck.....and would really appreciate any help!  here's a link to the set of the 3 key columns (Description Below).

Position - Position of player I'm betting on

Abs Value % of projection - Absolute Value of the projection vs. the bet.  (Ex. If the bet is Derrick Henry over 90 yards, and I have him projected at 99 yards, it will show 10%)

Win/Loss - Actual record of Win or Loss of the bet

https://docs.google.com/spreadsheets/d/1ehpMgoNmFFEyPRYzyonvtsgNNnaoGFHutAW9EyJq6tQ/edit?usp=sharing

thanks!

It sounds like what you want is a logistic regression.  Maybe too in-depth to explain it here but you can google "logistic regression excel" or something and there are short tutorials (or probably add-ins that can do it).

As a simpler alternative, you might want to just group all your projections into some sensible buckets (e.g. 0-10%, 11-20%, 21-30%, ... , 91-100%) and then calculate the win rate for each of those buckets.  And then plot the buckets vs. the actual win rates as scatter plot to see the correlation. 

 

[Tiger Fan]

It sounds like what you want is a logistic regression.  Maybe too in-depth to explain it here but you can google "logistic regression excel" or something and there are short tutorials (or probably add-ins that can do it).

As a simpler alternative, you might want to just group all your projections into some sensible buckets (e.g. 0-10%, 11-20%, 21-30%, ... , 91-100%) and then calculate the win rate for each of those buckets.  And then plot the buckets vs. the actual win rates as scatter plot to see the correlation. 

Thanks...will do some more research on the logistic regression...otherwise, the bucket solution is a great alternative!

 

[Dwayne Hoover]

Hoping this is an easy one for the experts

I have a string of numbers that look like this ["12345", "23456", "34567"]

I want to be able to extract them all out individually without the quotes so I can sort them vertically in a column

12345

23456

34567

If there is an easy way to explain this, please let me know.  Thanks!

 

[MTskibum]

Hoping this is an easy one for the experts

I have a string of numbers that look like this ["12345", "23456", "34567"]

I want to be able to extract them all out individually without the quotes so I can sort them vertically in a column

12345

23456

34567

If there is an easy way to explain this, please let me know.  Thanks!

Select the column

Under "data" tab select "Text to columns"

Select comma deliminated

Then, do a find and replace and replace " with blank values

Then copy the values and "paste special" and do a transpose paste.

You probably then want to go back and do text to columns one more time and change it back to tab deliminated.

 

[GAlmgren]

Click on Data, then click on the remove duplicates with that column highlighted. It might be better to copy/paste them to a different tab first. I put a screenshot in the below link.

https://twitter.com/FishSkiand/status/1255218033162956804

Newer versions of Excel have added a UNIQUE function that will pull these values. Using that with the SORT and FILTER functions can make parsing data easier/more dynamic. For me at least.

 

[cubd8]

Can this be reviewed? Looking for quick help on these in excel.

COL A:           COL B:         COL C:     EXPECTED CALCUATION 1:            EXPECTED CALCULATION 2:

100                      45            40                     60                                                 0.015              

-                           40             40                      0                                                 0

-                           40             38                      2                                                0.005

-                          100               -                      -                                                -

EXPECTED CALCULATION 1:

IF Column C = '-', the expected calculation 1 should be set to '-'.

If Column A and Column C have data,, subtract the difference (A-C) in Expected Calculation 1.

If Column A = '-' but Column B and Column C have data, subtract the difference (A-C) in Expected Calculation 1.EXEPCTED 

EXPECTED CALCULATION 2:

Multiply the value from 'Expected Calculation 1' by 0.00025

If the value of 'Expected Calculation 1' is '-', then set the value of 'expected calculation 2' to '-'.

 

[Thorpe]

=IF(C1 = "-", "-", IF(A1 <> "-", A1-C1, B1-C1))

=IF(D1="-", "-", D1*0.00025)

 

[shades]

There's a constant problem in the video editing world with timecode: there are several different types and it's complicated to convert one format to another. It certainly can't be done in your head, the algorithm is insanely complicated. There are calculators online that can do it, but, they're all a bit different and sometimes do the conversion in a slightly different way into a format that's close but not exactly the one you need. 

Last week, I needed to convert some timecode and couldn't find an online calculator that matched the formats in and out that I wanted. But then I found an Excel file someone posted online that did the conversion for you: type in the timecode you want in A and the converted format appears in B. And vice-versa, type it into B in the alternate format, and it'll give you the matching code in A.

I was curious how it worked, so I looked at the formula in the cell, hoping to break it down. But it just was a reference to a hidden sheet. So I un-hid the sheet and took a look.

Was it the algorithm I expected? Did it need 8-20 helper cells and placeholders to keep it on track as it stepped through the process? Nope. Instead, column A was just numbers. But not just any numbers.... it was every possible number of 24 frames-per-second timecode. From 00:00:00.00 to 00:00:00.01 to 00:00:00.02 to 00:00:00.03, etc., down all of Column A. All 1,040,000 rows or whatever. Column B? Every corresponding number in 29.97 frames-per-second timecode, all million-plus possible answers.

The magic formula was just a LOOKUP.

 

[shades]

In layman's terms, they basically wrote a "formula" that converts metric to standard units by writing out every single increment of centimeters, from 1 to 10.5 kilometers, in column A, then wrote out every equivalent in inches in column B, then just did a LOOKUP for the matching value.

A                B

1cm = 0.393701 inches

2 cm = 0.787402 inches

3 cm = 1.1811 inches

.... 1 million, 40 thousand rows later...

1,040,000 cm = 409448.8189 inches

:headexplode:

 

[cubd8]

=IF(C1 = "-", "-", IF(A1 <> "-", A1-C1, B1-C1))

=IF(D1="-", "-", D1*0.00025)

Thank you! 

One other question related to this question. How would I update the existing  formula to account for a value of 'N' in A1 or B1? I didn't realize this until just now, but a value of 'N' is possible in any of these values and would make the calculation impossible. 

In addition to the existing formula, I need to to also output a '-' if/when A1 or B1 has a value of 'N'.

=IF(C1 = "-", "-", IF(A1 <> "-", A1-C1, B1-C1))

=IF(D1="-", "-", D1*0.00025)

 

[Thorpe]

I didn't test this in Excel, but this should work:

=IF(C1 = "-" , "-", IF(A1 = "N" or B1= "N" , "-", IF(A1 <> "-" , A1-C1, B1-C1)))

 

[cubd8]

Thank you again!

One other question!

When Column A has the same value and Column B contains "Grass", aggregate the values in Column C in the Expected Value. 

How do I write this ?

Column A:                    Column B:              Column c                 Expected Value:

111                                Grass                        100                             250

111                                Weed                          50                             250

222                                WeedGrass                25                              25

222                                "              "                    0                                25

111                                Grass                        100                             250

333                                Grass                        100                              100

444                                Weed                         50                                 0

555                                "          "                       0                                   0

 

[bryhamm]

not really an easy/convenient way to do that with formulas.  might be better doing a pivot table for that.

 

[Brony]

Thank you again!

One other question!

When Column A has the same value and Column B contains "Grass", aggregate the values in Column C in the Expected Value. 

How do I write this ?

Column A:                    Column B:              Column c                 Expected Value:

111                                Grass                        100                             250

111                                Weed                          50                             250

222                                WeedGrass                25                              25

222                                "              "                    0                                25

111                                Grass                        100                             250

333                                Grass                        100                              100

444                                Weed                         50                                 0

555                                "          "                       0                                   0

I would probably use SUMIFS for this.  In Column D, row 1: 

=SUMIFS($C$1:$C$100, $A$1:$A$100, A1) and copy down.  For Row 1, this adds up all of the occurences of 111 in Column A.  I can't tell if your example is contingent on what is in Column B.  If it is, $B$1:$B$100, B1 to the end of your SUMIFS.  

 

[cubd8]

Can somebody assist with this logic below?

(IF Column A = "Down" OR Column B > 0) AND Column C > 0 then subtract Column D - Column E.
- If Column E = N OR the subtraction cannot be performed, output a '-' in Column F.

Column A:                   Column B:                     Column c                 Column d                     Column E                     Expected Calculation:

Down                           -                                      1                                763,979                       90,121                          673,858

Down                          3                                      -                                 145,566                      145,566                        -

Up                               3                                     -                                    -                                 N                                   -

Up                               2                                     -                                   -                                  70,384                         -

Down                         -                                       1                                100                             N                                   -

Up                              1                                      -                                  -                                 29,228                          -

Down                        -                                       -                                  -                                 N                                   -

Down                       2                                      1                                40                               20                               20

Down                      1                                       1                               10                               4                                  6

 

[Chemical X]

here's one;

wife inserts an excel chart into a powerpoint presentation, then the chart rows lose their spacing......any idea how to respace an excel chart that is in powerpoint?

thx

 

[acarey50]

here's one;

wife inserts an excel chart into a powerpoint presentation, then the chart rows lose their spacing......any idea how to respace an excel chart that is in powerpoint?

thx

Paste Special as an image is the easiest in my opinion, assuming you aren't trying to link it/update it in Powerpoint.

 

[Chemical X]

Paste Special as an image is the easiest in my opinion, assuming you aren't trying to link it/update it in Powerpoint.

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

 

[acarey50]

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

Even so, if it's a chart, you should still be able to copy/paste it as an image.

If the Powerpoint is something that will be updated regularly, you can paste as a link to the original, and the powerpoint should update when the Excel file updates, but I know in practice it is not always that simple.

 

[Thorpe]

I haven't tested this, but when you paste into powerpoint there is an option to "Keep Source formatting and Link Data" which sounds like what you need.

 

[acarey50]

I haven't tested this, but when you paste into powerpoint there is an option to "Keep Source formatting and Link Data" which sounds like what you need.

True, however, if this is a file that will be emailed, shared, etc. you will get the fun popup that the source/linked file could not be found, etc., and it will not be linked to the source data. In theory it could be simple, in practice it often is not.

 

[Ned]

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

Even so, if it's a chart, you should still be able to copy/paste it as an image.

If the Powerpoint is something that will be updated regularly, you can paste as a link to the original, and the powerpoint should update when the Excel file updates, but I know in practice it is not always that simple.

agreed - how it's created shouldn't matter.

 

[heckmanm]

No question, just a pet peeve: People who put blank rows or columns in large spreadsheets that make it IMPOSSIBLE to use the "Filter" function.

 

[Brony]

No question, just a pet peeve: People who put blank rows or columns in large spreadsheets that make it IMPOSSIBLE to use the "Filter" function.

That also stymies CTRL-SHIFT-8 to select the whole table. 

It's right up there with the people who people who use Merge and Center across columns.  

 

[acarey50]

That also stymies CTRL-SHIFT-8 to select the whole table. 

It's right up there with the people who people who use Merge and Center across columns.  

I do wish they'd make Center Across Selection the default instead of merge - it is much more useful in my opinion and essentially accomplishes the same desired formatting

 

[Niles Standish]

No question, just a pet peeve: People who put blank rows or columns in large spreadsheets that make it IMPOSSIBLE to use the "Filter" function.

I know for me if I click the kind of half square looking thing to the left of the A (first column) it highlights the whole excel and lets you filter.

 

[culdeus]

Help

I have a number that must be formatted to two digits, i.e. 5 = 05. I can easily format a cell to display this.  ok.

Now I want to concatentate this number into a string and need it to also be "05" in the string.  

how?

Edit, answer was basically : ="G4-"&D16& "-" &TEXT(D12,"#00")

 

[Ned]

Help

I have a number that must be formatted to two digits, i.e. 5 = 05. I can easily format a cell to display this.  ok.

Now I want to concatentate this number into a string and need it to also be "05" in the string.  

how?

This will combine A1 and B1.

=IF(A1<10,"0"&A1&B1,A1&B1)

or you can use CONCATENATE.

=IF(A1<10,CONCATENATE("0",A1,B1),CONCATENATE(A1&B1))

 

[culdeus]

This will combine A1 and B1.

=IF(A1<10,"0"&A1&B1,A1&B1)

or you can use CONCATENATE.

=IF(A1<10,CONCATENATE("0",A1,B1),CONCATENATE(A1&B1))

I figured out another way to do it, see the edit.  

 

[tri-man 47]

As near as I can tell, the Developer tab tools (i.e., the Form Controls tools such as option buttons, scroll bars, combo boxes) are not available in Google Sheets.  Can someone confirm?