Corey Becker

My feedback

  1. 3 votes
    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)

      We’ll send you updates on this idea

      Corey Becker supported this idea  · 
      Corey Becker commented  · 

      Wow. I didn't believe you at first. I just tried it and it worked. I'm not really sure how they can resolve this though, without creating a new file type that can't be downgraded to .xls

    • 5 votes
      Sign in
      Check!
      (thinking…)
      Reset
      or sign in with
      • facebook
      • google
        Password icon
        I agree to the terms of service
        Signed in as (Sign out)

        We’ll send you updates on this idea

        Corey Becker commented  · 

        There were A LOT of people asking for separate windows. I agree they should provide the option but it certainly wasn't a dictatorial decision.

      • 3 votes
        Sign in
        Check!
        (thinking…)
        Reset
        or sign in with
        • facebook
        • google
          Password icon
          I agree to the terms of service
          Signed in as (Sign out)

          We’ll send you updates on this idea

          Corey Becker commented  · 

          I'm guessing this will be low priority for them but I can offer you a workaround. In my version of Excel (2016), it already works like this. If you actually launch a new session (e.g. click on Excel in your Start menu), it should launch a new Excel session and keep your other session minimized. They recently allowed multiple windows within the same session which is probably the issue you're running into (hard to tell what's a separate window vs session). If you just open an Excel file from some folder, it will open in your existing Excel session so your other spreadsheets will all pop up. So just open Excel from your start menu and then open the file and the other files will stay minimized.

        • 99 votes
          Sign in
          Check!
          (thinking…)
          Reset
          or sign in with
          • facebook
          • google
            Password icon
            I agree to the terms of service
            Signed in as (Sign out)

            We’ll send you updates on this idea

            Corey Becker commented  · 

            Daniel's suggestion is an issue with chart's. Charting should be updated to allow that behavior. I use the error technique personally and all my sums ignore errors. Adding a null function seems like a workaround to fix real issues. We should just fix the issues you're trying to workaround.

            Corey Becker commented  · 

            It seems that there are probably much simpler solutions to the examples listed below.

          • 2 votes
            Sign in
            Check!
            (thinking…)
            Reset
            or sign in with
            • facebook
            • google
              Password icon
              I agree to the terms of service
              Signed in as (Sign out)

              We’ll send you updates on this idea

              Corey Becker commented  · 

              The 10,000 limit should only apply to the drop-down that you see in auto-filter. It's just a display issue. All your rows are still sorted/filtered, you just can't see all the options in that drop-down, that doesn't mean it doesn't apply to them. I suggest you always have an "IsFiltered" column on your table. This field can be driven by some cell/input/slicer/etc. It could be as simple as (in cell F2) =A2="Jelly Beans". Jelly Beans can be replaced with anything obviously, including a cell reference. You can also add multiple tests with AND/OR/etc.

            • 3 votes
              Sign in
              Check!
              (thinking…)
              Reset
              or sign in with
              • facebook
              • google
                Password icon
                I agree to the terms of service
                Signed in as (Sign out)

                We’ll send you updates on this idea

                Corey Becker commented  · 

                I'm not sure how they could solve this exactly, but it would be nice. I've written some pretty nasty VBA to do something like this but it sucked.

              • 2 votes
                Sign in
                Check!
                (thinking…)
                Reset
                or sign in with
                • facebook
                • google
                  Password icon
                  I agree to the terms of service
                  Signed in as (Sign out)

                  We’ll send you updates on this idea

                  Corey Becker commented  · 

                  I feel like there's probably a way to do this with a custom list or something. I think this idea should be part of making that process easier/more dynamic.

                • 2 votes
                  Sign in
                  Check!
                  (thinking…)
                  Reset
                  or sign in with
                  • facebook
                  • google
                    Password icon
                    I agree to the terms of service
                    Signed in as (Sign out)

                    We’ll send you updates on this idea

                    Corey Becker commented  · 

                    Oh god, definitely not a new file type. That was disastrous. Definitely agree this would be nice though. I think a summary with the data model and all data queries/connections would be nice. Icon or indicator would certainly be a start.

                  • 83 votes
                    Sign in
                    Check!
                    (thinking…)
                    Reset
                    or sign in with
                    • facebook
                    • google
                      Password icon
                      I agree to the terms of service
                      Signed in as (Sign out)

                      We’ll send you updates on this idea

                      Corey Becker commented  · 

                      Jan, it's not what I "believe" to be bad practice. It just is. My favorite explanation of this can be found if you Google "Volatile Excel Functions Decision Models".

                      You are trying to make a point by saying you have 43 sheets, but that's only confirming how terrible the spreadsheet is designed. As you continue to develop your Excel/data skills, this will become much more apparent. I'm simply suggesting to anyone reading that they avoid the mistakes I made early in my career.

                      That being said, I know people find volatile functions as an easy stop-gap measure when they're not aware of their alternatives. That's perfectly fine until you start dealing with truly complex models. However, Microsoft should do a better job educating their users, not continue to support bad habits by expanding these workarounds.

                      Your first example has several other alternatives.Give me an example spreadsheet containing an example of when you think INDIRECT is necessary and I will show you a better alternative. In doing so, it will be faster, more reliable, and more dynamic.

                      Corey Becker commented  · 

                      Using volatile functions is bad practice. They are never the best solution. Sometimes they are the simplest, so for small, simple workbooks they're fine. If you have not noticed performance issues with INDIRECT then you don't deal with any large or complex spreadsheets. I've seen several people design these dynamic sheet name spreadsheets you're speaking about and I just shake my head. Data should be stored in a single table with a column to specify the attribute you're filtering on (as opposed to a separate sheet). Development should not focus on supporting bad habits.

                      Corey Becker commented  · 

                      Not sure if someone mentioned this before but another issue with these functions is that they are volatile, meaning no one should actually use them in a large file. Also, I'm concerned this would lead to more people using INDIRECT, which is never a good thing.

                    • 316 votes
                      Sign in
                      Check!
                      (thinking…)
                      Reset
                      or sign in with
                      • facebook
                      • google
                        Password icon
                        I agree to the terms of service
                        Signed in as (Sign out)

                        We’ll send you updates on this idea

                        started  ·  9 comments  ·  Excel for Windows (Desktop Application) » Editing  ·  Flag idea as inappropriate…  ·  Admin →
                        Corey Becker commented  · 

                        This is a no brainer. I think just about everyone has run into this issue at some point.

                      • 253 votes
                        Sign in
                        Check!
                        (thinking…)
                        Reset
                        or sign in with
                        • facebook
                        • google
                          Password icon
                          I agree to the terms of service
                          Signed in as (Sign out)

                          We’ll send you updates on this idea

                          Corey Becker commented  · 

                          Great idea. Being able to specify "Locked" by column would be nice. Also, unlocking the structure of the table itself so you can insert rows as you suggested.

                        • 50 votes
                          Sign in
                          Check!
                          (thinking…)
                          Reset
                          or sign in with
                          • facebook
                          • google
                            Password icon
                            I agree to the terms of service
                            Signed in as (Sign out)

                            We’ll send you updates on this idea

                            Corey Becker commented  · 

                            You can link Form/ActiveX checkbox controls to a specific cell.

                          • 2 votes
                            Sign in
                            Check!
                            (thinking…)
                            Reset
                            or sign in with
                            • facebook
                            • google
                              Password icon
                              I agree to the terms of service
                              Signed in as (Sign out)

                              We’ll send you updates on this idea

                              Corey Becker commented  · 

                              I like the idea of the sidebar but it needs a lot of work. Keyboard navigation is one thing. It's also not intuitive where anything is in my opinion.

                            • 16 votes
                              Sign in
                              Check!
                              (thinking…)
                              Reset
                              or sign in with
                              • facebook
                              • google
                                Password icon
                                I agree to the terms of service
                                Signed in as (Sign out)

                                We’ll send you updates on this idea

                                Corey Becker commented  · 

                                This always seemed like a no brainer to me. Cutting is often times easier because it doesn't update the formulas, but then you have to reformat the pasted cells to match the formatting of their new home.

                              • 31 votes
                                Sign in
                                Check!
                                (thinking…)
                                Reset
                                or sign in with
                                • facebook
                                • google
                                  Password icon
                                  I agree to the terms of service
                                  Signed in as (Sign out)

                                  We’ll send you updates on this idea

                                  Corey Becker commented  · 

                                  As any advanced Excel user will tell you, the better solution is to stop merging cells. It leads to sloppy spreadsheets. There is always a better way to design your spreadsheet. Use Center Across Selection for horizontal text. If you have vertical text, you're doing it wrong.

                                • 15 votes
                                  Sign in
                                  Check!
                                  (thinking…)
                                  Reset
                                  or sign in with
                                  • facebook
                                  • google
                                    Password icon
                                    I agree to the terms of service
                                    Signed in as (Sign out)

                                    We’ll send you updates on this idea

                                    Corey Becker commented  · 

                                    @JoeU, that really depends on your application though. You should not be doing theoretical mathematics in Excel. This suggestion is related to floating point arithmetic, not approximation. If you subtract 2-2, it should be 0, not 0.0000000001. The issue is related to conversion between binary and decimal, not your "reaches zero" heuristic.

                                    Corey Becker supported this idea  · 
                                    Corey Becker commented  · 

                                    @Andreas Using ROUND was our workaround but rounding is not ideal in all applications. This suggestion is more related to the fact that Excel's correction for the floating point bug is not consistent.

                                    Corey Becker shared this idea  · 
                                  • 478 votes
                                    Sign in
                                    Check!
                                    (thinking…)
                                    Reset
                                    or sign in with
                                    • facebook
                                    • google
                                      Password icon
                                      I agree to the terms of service
                                      Signed in as (Sign out)

                                      We’ll send you updates on this idea

                                      Corey Becker commented  · 

                                      I think it would be cool if there was an input for which match to return (0 to return an array of the matched values, -1 to return the last occurrence, 1+ to return the corresponding match). Hopefully that would be an optional input and wouldn't hurt performance for most use cases.

                                      Corey Becker commented  · 

                                      I think it would make more sense to keep in consistent with SUMIFS/COUNTIFS and allow multiple criteria.

                                      =GETMATCH(return_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

                                      Corey Becker commented  · 

                                      @Kenneth, Good list. I'm guessing it would get too complicated to incorporate AND/OR, especially when it's easy to just use multiple functions added together. I don't know how you could implement that in a simple way within a single function. Interested to see ideas though.

                                      I also have a deep-seeded hatred for vlookup. I usually try to put all data in tables and add new fields for lookups. I will add a bit field that checks whether each row meets all the conditions and then my index/match or sumif or other function can just filter on that one column.

                                      Corey Becker commented  · 

                                      There are a couple other similar ideas but I have a couple specific suggested solutions that I hope are considered when a solution is devised. Thanks!

                                      Current
                                      =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

                                      Proposal #1
                                      =LOOKUP_1(lookup_value, table_array, [lookup_idx_num], [return_idx_num], [row_or_col], [range_lookup])

                                      Where...

                                      [lookup_col_num] is the column to search for lookup_value. This field is optional with a default of 1.

                                      [return_col_num] is the column that the result is pulled from. This field is options with a default of the last column in the table_array.

                                      [row_or_col] may be a little more controversial but I don't understand the benefit of having two different functions that basically do the same thing. Have it default to VLOOKUP with and option to switch to HLOOKUP.

                                      I would argue that the new lookup function would be even simpler than the current version with only two required inputs and a large improvement to flexibility. However, you are still somewhat limited in that you need a contiguous table_array so I have a second proposal that's a little more complicated but should be familiar to those using some other popular functions in excel.

                                      Proposal #2
                                      =LOOKUP_2(return_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

                                      This should look familiar. It's the sumifs/countifs function but instead of counting/summing, it simply returns the first result matching all the criteria. You could also throw an optional [instance_num] in there like the substitute function has.

                                    • 104 votes
                                      Sign in
                                      Check!
                                      (thinking…)
                                      Reset
                                      or sign in with
                                      • facebook
                                      • google
                                        Password icon
                                        I agree to the terms of service
                                        Signed in as (Sign out)

                                        We’ll send you updates on this idea

                                        Corey Becker commented  · 

                                        Conditional formatting and macros should both be used sparingly as they are resource hogs and prone to issues (volatility, maintenance, etc.). We've had virtually no issues with 2016. We rarely use VBA and all our data is organized in tables with pivot tables and charts to summarize. We also moved away from conditional formatting towards dashboard-style charts and summary tables.

                                      • 3 votes
                                        Sign in
                                        Check!
                                        (thinking…)
                                        Reset
                                        or sign in with
                                        • facebook
                                        • google
                                          Password icon
                                          I agree to the terms of service
                                          Signed in as (Sign out)

                                          We’ll send you updates on this idea

                                          Corey Becker commented  · 

                                          You can do this with VBA I imagine. It's a pretty specific request for them to build a feature to do this. It would be interesting to have a simple user interface where you could set up alerts like this though.

                                        • 4 votes
                                          Sign in
                                          Check!
                                          (thinking…)
                                          Reset
                                          or sign in with
                                          • facebook
                                          • google
                                            Password icon
                                            I agree to the terms of service
                                            Signed in as (Sign out)

                                            We’ll send you updates on this idea

                                            Corey Becker commented  · 

                                            They redesigned the Data tab in 2016. You can just click "New Query" and then select CSV. Should be easier than the way you were doing it before. Ideally, you're only doing this once and setting it to update automatically.

                                          ← Previous 1

                                          Feedback and Knowledge Base